How to get data in result set order by month?
Below the is working fine but I am not getting in month order. Can you suggest me any idea?
SELECT SUM(TaxableAmt) [NSV], (UPPER(CONVERT(VARCHAR,LEFT(DATENAME(MM,invoicedate),3)))+'/'+ CONVERT(VARCHAR,RIGHT(DATENAME(YYYY,invoicedate),2))) AS MONTHYEAR
FROM SALESDATA where (invoicedate >='4-1-2009' and invoicedate <='4-30-2010')
GROUP BY (UPPER(CONVERT(VARCHAR,LEFT(DATENAME(MM,invoicedate),3)))+'/'+ CONVERT(VARCHAR,RIGHT(DATENAME(YYYY,invoicedate),2)))
Result set is:
NSV 开发者_StackOverflow中文版MONTHYEAR
45099947.4300001 APR/09
83295380.4299992 NOV/09
95838138.2 AUG/09
74326454.2599992 DEC/09
94144688.5400001 JUL/09
60688678.260001 MAR/10
58451739.9700001 APR/10
87555926.7200027 FEB/10
128036311.36 JAN/10
50949078.1699996 JUN/09
45232741.8099997 MAY/09
72846524.389999 OCT/09
You didn't ask SQL Server for any order, it's free to return the data in any order it likes.
One way of doing this (and reducing the string manipulation to one place):
select NSV,UPPER(SUBSTRING(DATENAME(month,MonYear),1,3)) + '/' + RIGHT(DATEPART(year,MonYear),2)
from
(SELECT SUM(TaxableAmt) [NSV],DATEADD(month,DATEDIFF(month,'20010101',invoicedate),'20010101') as MonYear
FROM SALESDATA where (invoicedate >='20090401' and invoicedate <='20100430')
GROUP BY DATEADD(month,DATEDIFF(month,'20010101',invoicedate),'20010101')
) t
ORDER BY MonYear
精彩评论