ORDER BY converted week range

It orders the data, but my 2011 weeks are coming in before my 2010.

Week                                    Total Loan Amount
02/27/2011 - 03/05/2011      140800.00
03/06/2011 - 03/12/2011      159450.00
03/13/2011 - 03/19/2011      95725.00
10/17/2010 - 10/23/2010      32150.00
10/24/2010 - 10/30/2010      48700.00
.............
......
I can order converted dates when just selecting the date, but I haven't got one for a week range, like this.

Anybody?
SELECT
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101) [Week],
	SUM(l.loan_amount) [Total Loan Amount]
FROM 
	table
GROUP BY
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)
ORDER BY
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)

Open in new window

LVL 18
dbaSQLAsked:
Who is Participating?
 
Ephraim WangoyaCommented:
Try this way
SELECT
    CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101) [Week],
	SUM(l.loan_amount) [Total Loan Amount]
FROM 
	table
GROUP BY
    CONVERT(VARCHAR(6), date_approved ,112),
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)
ORDER BY   
    CONVERT(VARCHAR(6), date_approved ,112),
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)

Open in new window

0
 
Paul MacDonaldDirector, Information SystemsCommented:
How about changing the ORDER BY to list the dates as YYYY-MM-DD?
0
 
dbaSQLAuthor Commented:
My first attempt was MM-DD-YYYY, but the output was the same.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Ephraim WangoyaCommented:
try
SELECT
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101) [Week],
	SUM(l.loan_amount) [Total Loan Amount]
FROM 
	table
GROUP BY
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)
ORDER BY
	CONVERT(VARCHAR(6), date_approved ,112)

Open in new window

0
 
wdosanjosCommented:
Try this:

SELECT CONVERT(VARCHAR(20), wstart, 101) + ' - ' + CONVERT(VARCHAR(20), wend, 101) Week,
	   SUM(loan_amount) 'Total Loan Amount'
  FROM (
SELECT DATEPART(year, l.date_approved) yapproved,
	   CAST(DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved) as smalldatetime) wstart, 
	   CAST(DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)) as smalldatetime) wend,
	   l.loan_amount
  FROM YourTable l) s
GROUP BY yapproved, wstart, wend
ORDER BY yapproved, wstart, wend

Open in new window

0
 
dbaSQLAuthor Commented:
No good.

Msg 8127, Level 16, State 1, Line 12
Column "dbo.tablename.date_approved" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
dbaSQLAuthor Commented:
When I said 'no good', I was referring to ewangoya's suggested post.  

wdosanjos, I've tried yours -- the 2010 data is being listed first, but in multiples... like this:

10/17/2010 - 10/23/2010      750.00
10/17/2010 - 10/23/2010      500.00
10/17/2010 - 10/23/2010      500.00
10/17/2010 - 10/23/2010      1000.00
.............
.....
0
 
dbaSQLAuthor Commented:
The inner query also returns the multiples:

yapproved            wstart                                  wend                          loan_amount
2010      2010-10-17 18:07:00      2010-10-23 18:07:00      500.00
2010      2010-10-17 18:06:00      2010-10-23 18:06:00      750.00
2010      2010-10-17 17:44:00      2010-10-23 17:44:00      750.00
2010      2010-10-17 17:41:00      2010-10-23 17:41:00      750.00
........................
.........
0
 
dbaSQLAuthor Commented:
My first post was the only one that really provides the output I'm looking for:

Week                                    Total Loan Amount
02/27/2011 - 03/05/2011      140800.00
03/06/2011 - 03/12/2011      159450.00
03/13/2011 - 03/19/2011      95725.00
10/17/2010 - 10/23/2010      32150.00
10/24/2010 - 10/30/2010      48700.00
.............
......


But the week sets are not ordered properly, by year, month and date.  They are only ordered by month and date.

Anybody?
0
 
dbaSQLAuthor Commented:
Perfect, ewangoya.  Thank you very much.
0
 
Ephraim WangoyaCommented:
Or even better
SELECT    
    CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101) [Week],
	SUM(l.employeeid) [Total Loan Amount]
FROM 
	table
GROUP BY
    
    CONVERT(VARCHAR(8),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),112),
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved)),101)
ORDER BY       
    CONVERT(VARCHAR(8),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),112),
	CONVERT(VARCHAR(20),DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date_approved),101) + ' - ' + CONVERT(VARCHAR(20),DATEADD(day,6,DATEADD(day,-(DATEPART(dw,l.date_approved)-1),l.date

Open in new window

0
 
dbaSQLAuthor Commented:
ok, one last thing.  i order my converted dates like this:

GROUP BY CONVERT(VARCHAR(10),l.date_approved,120)
ORDER BY CONVERT(VARCHAR(10),l.date_approved,120)

makes them all come out properly in order.  also makes them YYYY-MM-DD.
my output on the weekly, however, is MM/DD/YYYY

i think i'd rather the MM/DD/YYYY, but if I change my date construct to CONVERT(VARCHAR(10),l.date_approved,101), they are no longer ordered properly.

your thoughts?   i'd like them all to be formatted the same, MM/DD/YYYY.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.