How to get data in result set order by month?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

The following code is working fine but I am not getting in month order. Can you suggest me any idea?

SQL

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 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

62841500.5999995 SEP/09

Regards,

N.SRIRAM

Posted 10-Jan-11 23:17pm

SRIRAM 2

Updated 11-Jan-11 0:02am

Dalek Dave

v3

Add a Solution

Comments

Hiren solanki 11-Jan-11 6:06am

you can always accept answer if it solved.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Answer 1 · 2011-01-10T23:25:00

Solution 1

Just put ORDER BY at the end, Like

SQL

Order By MONTH(invoicedate)

That will give you data by sorting month wise regardless of year but if you want to sort month among Year then you can use below Syntax

SQL

Order By YEAR(invoicedate),Month(invoicedate)

Hope it will work for you.

Posted 10-Jan-11 23:25pm

Hiren solanki

Comments

Hiren solanki 11-Jan-11 6:06am

Hi friends I got the answer.

Below is the query.Here I included year and month.

Collapse
SELECT SUM(TaxableAmt) [NSV], (UPPER(CONVERT(VARCHAR,LEFT(DATENAME(MM,invoicedate),3)))+'/'+ CONVERT(VARCHAR,RIGHT(DATENAME(YYYY,invoicedate),2))) AS MONT,YEAR(invoicedate),Month(invoicedate)
FROM SALESDATA where (invoicedate >='4-1-2008' and invoicedate <='4-30-2011')
GROUP BY (UPPER(CONVERT(VARCHAR,LEFT(DATENAME(MM,invoicedate),3)))+'/'+ CONVERT(VARCHAR,RIGHT(DATENAME(YYYY,invoicedate),2))),YEAR(invoicedate),Month(invoicedate)
Order By YEAR(invoicedate),Month(invoicedate)

Hiren solanki 11-Jan-11 6:06am

This is the same what i suggested, If it helped then don't post your answer just accept it, everybody will know it later on.