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

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

0
dbaSQL
Asked:
dbaSQL
1 Solution
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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