开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜