Solved

TSQL: how do i go by month order

Posted on 2011-03-02
13
331 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
Comment Utility

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

Author Comment

by:ptemo
Comment Utility
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
Comment Utility

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

Expert Comment

by:Sharath
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ptemo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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

Its ok so long as you get the correct solution
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now