Solved

TSQL: how do i go by month order

Posted on 2011-03-02
13
334 Views
Last Modified: 2012-05-11
Hello  all,

I have a code that sorts by month wages earn by insurance.  But i'm not sure how to sort by month.  

here is my code, thanks in advance

SELECT        DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar) AS Expr1, SUM(sched_amount_paid_allowed)
                         AS sched_amount_paid_allowed, SUM(coins_amount_paid) AS coins_amount_paid, SUM(sched_copay_amnt_rcvd) AS sched_copay_amnt_rcvd
FROM            Schedule
GROUP BY DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar)
0
Comment
Question by:ptemo
  • 5
  • 5
  • 3
13 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35023035

try
SELECT        DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar) AS Expr1, SUM(sched_amount_paid_allowed) 
                         AS sched_amount_paid_allowed, SUM(coins_amount_paid) AS coins_amount_paid, SUM(sched_copay_amnt_rcvd) AS sched_copay_amnt_rcvd
FROM            Schedule
GROUP BY DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar)
ORDER BY MONTH(sched_paid_date)

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35023084
Grouping will have a problem
Change this to
SELECT Expr1, sched_amount_paid_allowed, coins_amount_paid, sched_copay_amnt_rcvd
FROM
   (
	SELECT MONTH(sched_paid_date) MonthPaid, DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar) AS Expr1, 
	SUM(sched_amount_paid_allowed) AS sched_amount_paid_allowed, 
	SUM(coins_amount_paid) AS coins_amount_paid, 
	SUM(sched_copay_amnt_rcvd) AS sched_copay_amnt_rcvd
	FROM            Schedule
	GROUP BY DATENAME(month, sched_paid_date) + ', ' + CAST(YEAR(sched_paid_date) AS varchar)
	) A
ORDER BY A.MonthPaid

Open in new window

0
 

Author Comment

by:ptemo
ID: 35023086
thanks for answering, but I got this error message

"Column "Schedule.sched.paid.date" is invalid in the
Order by clause because it is not contained in either an aggregate function or the group by clause

any thoughts?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ptemo
ID: 35023102
for the second code i got this error message

Column "Schedule.sched.paid.date" is invalid in the select list because it is not contained in either an aggregate function or the group by clause

any thoughts?
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35023107

Check my next solution, I realized that error would occur
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35023124
try this.
SELECT Datename(MONTH,sched_paid_date) + ', ' + Cast(Year(sched_paid_date) AS VARCHAR) AS expr1,
         Sum(sched_amount_paid_allowed)                                                  AS sched_amount_paid_allowed,
         Sum(coins_amount_paid)                                                          AS coins_amount_paid,
         Sum(sched_copay_amnt_rcvd)                                                      AS sched_copay_amnt_rcvd
    FROM schedule 
GROUP BY Datename(MONTH,sched_paid_date), 
         Cast(Year(sched_paid_date) AS VARCHAR) 
ORDER BY Datename(MONTH,sched_paid_date)

Open in new window

0
 

Author Comment

by:ptemo
ID: 35023132
hello

I actually tried the second code, but it didnt work.  i got this error code

Column "Schedule.sched.paid.date" is invalid in the select list because it is not contained in either an aggregate function or the group by clause

0
 

Author Comment

by:ptemo
ID: 35023147
sorry i didnt see your latest code.  i tried it, i got no error message but the months come in the wrong order.  please see below

NULL
August, 2010
December, 2010
February, 2011
January, 2011
January, 1900
July, 2010
March, 2011
May, 2010
November, 2010
October, 2010
September, 2010
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35023169
I guess you are looking for this.
SELECT Datename(MONTH,sched_paid_date) + ', ' + Cast(Year(sched_paid_date) AS VARCHAR) AS expr1,
         Sum(sched_amount_paid_allowed)                                                  AS sched_amount_paid_allowed,
         Sum(coins_amount_paid)                                                          AS coins_amount_paid,
         Sum(sched_copay_amnt_rcvd)                                                      AS sched_copay_amnt_rcvd
    FROM schedule 
GROUP BY datepart(MM,sched_paid_date), 
         Datename(MONTH,sched_paid_date), 
         Cast(Year(sched_paid_date) AS VARCHAR) 
ORDER BY datepart(MM,sched_paid_date)

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35023183

I think its order by year then month


SELECT Datename(MONTH,sched_paid_date) + ', ' + Cast(Year(sched_paid_date) AS VARCHAR) AS expr1,
         Sum(sched_amount_paid_allowed)                                                  AS sched_amount_paid_allowed,
         Sum(coins_amount_paid)                                                          AS coins_amount_paid,
         Sum(sched_copay_amnt_rcvd)                                                      AS sched_copay_amnt_rcvd
    FROM schedule 
GROUP BY Datename(MONTH,sched_paid_date), 
         Cast(Year(sched_paid_date) AS VARCHAR) 
ORDER BY Datepart(YY, sched_paid_date), Datename(MONTH,sched_paid_date)

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 35023186
Yes, you can include year also in the order by clause.
SELECT Datename(MONTH,sched_paid_date) + ', ' + Cast(Year(sched_paid_date) AS VARCHAR) AS expr1,
         Sum(sched_amount_paid_allowed)                                                  AS sched_amount_paid_allowed,
         Sum(coins_amount_paid)                                                          AS coins_amount_paid,
         Sum(sched_copay_amnt_rcvd)                                                      AS sched_copay_amnt_rcvd
    FROM schedule 
GROUP BY datepart(MM,sched_paid_date), 
         Datename(MONTH,sched_paid_date), 
         Cast(Year(sched_paid_date) AS VARCHAR) 
ORDER BY Cast(Year(sched_paid_date) AS VARCHAR) ,
         datepart(MM,sched_paid_date)

Open in new window

0
 

Author Comment

by:ptemo
ID: 35023286
hello ewangoya, i kept getting an error code.  Sharath_123 your second code worked great!!

thanks!!!!
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35023316

Its ok so long as you get the correct solution
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question