.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

Order by sum using case?

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :ASP.Net
 
SELECT fldFSL, Login, BranchName, 

SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited)= 1 THEN 1 ELSE 0 END) AS Jan,
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 2 THEN 1 ELSE 0 END) AS Feb, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 3 THEN 1 ELSE 0 END) AS Mar, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 4 THEN 1 ELSE 0 END) AS Apr, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 5 THEN 1 ELSE 0 END) AS May, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 6 THEN 1 ELSE 0 END) AS Jun, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 7 THEN 1 ELSE 0 END) AS Jul,
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 8 THEN 1 ELSE 0 END) AS Aug,
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 9 THEN 1 ELSE 0 END) AS Sep, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 10 THEN 1 ELSE 0 END) AS Oct,
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 11 THEN 1 ELSE 0 END) AS Nov, 
SUM(CASE WHEN YEAR(DateVisited) = YEAR(getdate()) AND MONTH(DateVisited) = 12 THEN 1 ELSE 0 END) AS Dec

FROM         (SELECT top 100 percent dbo.tblBranch.fldFSL, db


View Complete Post


More Related Resource Links

WCF DataService : basic Order - OderDetail - Product use case - unable to persist

  
Hello, I have the very classical Order - OderDetail - Product use case. I've made my database, my entity framework model and exposed it as a DataServices Read/Write. Order, OrderDetail and Product table have a Id column which is a PK based on an IDENTITY column (auto incr) Form the client, I try the following : MyEntities session1 = new MyEntities(svcUri); Order o = new Order(); session1.AddToOrder(o); o.OrderDate = DateTime.Now; Product p1 = new Product(); session1.AddToProduct(p1); p1.ProductDescription = "Product1"; Product p2 = new Product(); session1.AddToProduct(p2); p2.ProductDescription = "Product2"; OrderDetails d1 = new OrderDetails() {Product = p1, Amount = 1}; session1.AddToOrderDetails(d1); o.OrderDetails.Add(d1); var response1 = session1.SaveChanges(SaveChangesOptions.Batch); I receive the following error : Unable to determine the principal end of the 'MyEntities.FK_OrderDetails_Product' relationship. Multiple added entities may have the same primary key. I try to initialize every Id (PK)  of every object I create on the client using temporary values (-1,-2,-3,...) and then I got an other error message : The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_OrdeDetails_Order\". The conflict occurred in database \"MyEntities\", table \"dbo.Order\", column 'OrderId'.&a

ORDER BY and CASE statement not working with VARCHAR types

  

Hello all,

I cannot understand why this query won't work for some fields (VARCHAR ones):

 

	DECLARE @SortExpression as varchar(max) = null	
	SET @SortExpression = 'jobName'
	
	create table #TempJobHistory(
		[created] [datetime] NOT NULL,
		[clientName] [varchar](50) NOT NULL,	
		[startTime] [datetime] NOT NULL,
		[endTime] [datetime] NULL,			
		[jobName] [varchar](50) NULL,
		[exitCode] [int] NULL,
		[status] [varchar](20) NOT NULL,
		[rowNumber] [bigint] NOT NULL		
	)	

	insert into #TempJobHistory
	Select

Visual Case Tool - UML Tutorial

  
The Class Diagram

The class diagram is core to object-oriented design. It describes the types of objects in the system and the static relationships between them.

Use Case Diagram

  
The use case diagram shows the functionality of the system from an outside-in viewpoint.

Actors (stick men) are anything outside the system that interacts with the system.

Sorting ORDER BY - WORD

  

Is it possible with SQL sorted in descending order according to the word?
In Table I shrajeno Mastercard, Visa, Diners etc ...

All Visa are the first in the order, another Mastercard, etc. ...

Something does not work: ORDER BY PaymentType = 'Visa';

I would not want to work in one field PaymentId (111,222,33, etc. ..) and then use the ORDER BY.

Is this possible in such a way?


Linq qry with order by basic questions

  

I have never tried a group by with linq, now I do and I'm stuck.

I try to convert this sql statement to its linq counterpart :

SELECT COUNT(tb_Mailer_Smses.Id) AS [cnt], tb_Mailer_mailing.Id As [Id]
,SUM( CASE WHEN tb_Mailer_smses.DLRStatus = 200 THEN 1 ELSE 0 END) As [Delivered]
,SUM(CASE WHEN DLRStatus BETWEEN 300 AND 499 THEN 1 WHEN DLRStatus BETWEEN 800 AND 899 THEN 1 END) AS [Errors]
FROM tb_Mailer_mailing INNER JOIN
tb_Mailer_Smses ON tb_Mailer_mailing.Id = tb_Mailer_Smses.MailingId
WHERE tb_Mailer_mailing.ApplicationId = 1
GROUP BY tb_Mailer_mailing.Id


Is there some one who can me show me the convertion process ?


SWITCH...CASE STATEMENT

  

Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
{
 
    if (Page.IsPostBack)
    {
    public String toSring()
    {
    int intcomputerChoice=1;
        
         if(computerChoice==1)
         {
         return(Rock);
       }
       if(computerChoice==2)
         {
         return(Paper);
       }
       if(computerChoice==3)
         {
         return(Scissors);
       }
       }
       if (Page.IsPostBack)
      {

Editor's Note: In Case You Hadn't Noticed .

  

Find out what's new for MSDN Magazine, including a print redesign and the introduction of virtual labs on our web site so you can experiment with our code.

Howard Dierking

MSDN Magazine June 2008


ASP.NET: Collect Customer Order Information on an Internet Site Using XML and Web Forms

  

XML has quickly become the new data structure standard for everything from database access to cross-platform computing. XML is typically considered to be a vehicle for data exchange, dynamic data presentation, and data storage. However, the potential of XML far surpasses those limited applications. This article examines one new use: the gathering of data across a number of forms in an ASP.NET Beta 1 framework application. The sample program is a Web app used for ordering pizza. It uses ASP and C# to gather order information and then stores it in XML. To build the application, several concepts are explained, including data collection, order persistence using cookies, grouping input forms, and formatting the data for display.

Jeff Jorczak

MSDN Magazine September 2001


.NET Migration Case Study: Using ASP.NET to Build the beta.visualstudio.net Web Site

  

When the Web site used to collect customer feedback about the Visual Studio .NET beta needed an update, the Visual Studio team saw a good opportunity to implement, deploy, and showcase a real-world site using .NET technologies. This article covers the migration of the Web site, beta.visualstudio.net, from components written in Visual Basic 6.0 and ASP hosted on Microsoft Internet Information Services (IIS) 5.0 to ASP.NET and the .NET Framework. The choices of technologies to be incorporated, along with issues such as validation, security, and authentication, are all discussed. Both existing components and the new components written in JScript and ASP.NET that were used to migrate the site are also explained.

Jay Schmelzer

MSDN Magazine September 2001


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend