CODEWITHSUNDEEP
c#asp.netsql-server-2008
Rajkumar Gor
Rajkumar Gor

Reputation: 57

Order By Date not working in my project

I am working on a C# / ASP.NET project and I am using SQL Server as my back end.

I have written a query to get the details of 'Total Sale' per month and year, i.e in mm-yyyy format and I want to sort it ascending order.

But the query does not sort its results in ascending order.

My query is

/* TOTAL SALE*/
SELECT 
    CAST((Datepart(Month, [p].Transdate)) AS varchar(50)) +'-'+  
    CAST((Datepart(Year, [p].Transdate)) AS varchar(50)) AS [Month/Year],
    SUM([p].Litres) [Total Sale] 
FROM 
    CustomerPoints AS [p] 
INNER JOIN
    Customers AS [c] ON [c].[CustomerID] = [p].[CustomerID] 
WHERE
    [p].Transdate BETWEEN '2013-01-20' AND '2015-03-05' 
    AND [c].DistributorID = '1'
GROUP BY 
    CAST((Datepart(Month, [p].Transdate)) AS varchar(50)) + '-'+ 
    CAST((Datepart(Year, [p].Transdate)) AS varchar(50))
ORDER BY 
    CAST((Datepart(Month, [p].Transdate)) AS varchar(50)) + '-'+ 
    CAST((Datepart(Year, [p].Transdate)) AS varchar(50)) ASC

The output of above query is

Month/Year   Total Sale
--------------------------
1-2013          600
1-2014          2300
10-2014         0
2-2015          1560
3-2014          80
3-2015          700

Kindly help.

Thank you in advance.

Upvotes: 0

Views: 154

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460340

Because you cast the dates to strings you get lexicographical order instead of by date. So don't cast it:

SELECT Cast((Datepart(month, [p].transdate)) AS VARCHAR(50)) 
       + '-' 
       + Cast((Datepart(year, [p].transdate))AS VARCHAR(50)) AS [Month/Year], 
       Sum([p].litres)                                       [Total Sale] 
FROM   customerpoints AS [p] 
       INNER JOIN customers AS [c] 
               ON [c].[customerid] = [p].[customerid] 
WHERE  [p].transdate BETWEEN '2013-01-20' AND '2015-03-05' 
       AND [c].distributorid = '1' 
GROUP  BY Cast((Datepart(month, [p].transdate)) AS VARCHAR(50)) 
          + '-' 
          + Cast((Datepart(year, [p].transdate))AS VARCHAR(50)) 
ORDER  BY MIN(transdate) ASC

But since you are using Group By you have to tell SQL-Server from what row of each group you want to take the transdate. I have simply used MIN(transdate) which is the beginning of the month.

Upvotes: 3

timcbaoth
timcbaoth

Reputation: 709

Why do you "format" the date that you want to order by?

Cast((Datepart(Month,[p].Transdate)) as varchar(50)) + '-'+
Cast((Datepart(Year,[p].Transdate))as varchar(50)) ASC

This will order regarding that part as a varchar(50). Just order by Transdate.

Upvotes: 1

Related Questions