• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

TSQL: how do i go by month order

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
ptemo
Asked:
ptemo
  • 5
  • 5
  • 3
1 Solution
 
Ephraim WangoyaCommented:

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
 
Ephraim WangoyaCommented:
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
 
ptemoAuthor Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
ptemoAuthor Commented:
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
 
Ephraim WangoyaCommented:

Check my next solution, I realized that error would occur
0
 
SharathData EngineerCommented:
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
 
ptemoAuthor Commented:
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
 
ptemoAuthor Commented:
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
 
SharathData EngineerCommented:
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
 
Ephraim WangoyaCommented:

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
 
SharathData EngineerCommented:
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
 
ptemoAuthor Commented:
hello ewangoya, i kept getting an error code.  Sharath_123 your second code worked great!!

thanks!!!!
0
 
Ephraim WangoyaCommented:

Its ok so long as you get the correct solution
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now