Solved

TSQL: how do i go by month order

Posted on 2011-03-02
13
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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:Ephraim Wangoya
ID: 35023107

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

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 41

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:Ephraim Wangoya
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 41

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:
Ephraim Wangoya earned 500 total points
ID: 35023316

Its ok so long as you get the correct solution
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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