Solved

TSQL: how do i go by month order

Posted on 2011-03-02
13
335 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
Are triggers slow? 7 23
SQL server client app 3 30
T-SQL: Need a database plan to mine a pretty big Log table 4 21
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …

749 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