Solved

ORDER BY converted week range

Posted on 2011-03-16
12
283 Views
Last Modified: 2012-05-11
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
Comment
Question by:dbaSQL
12 Comments
 
LVL 33

Expert Comment

by:paulmacd
ID: 35151950
How about changing the ORDER BY to list the dates as YYYY-MM-DD?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152011
My first attempt was MM-DD-YYYY, but the output was the same.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35152105
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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35152141
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152172
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152202
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 17

Author Comment

by:dbaSQL
ID: 35152357
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
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35152481
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152482
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152508
Perfect, ewangoya.  Thank you very much.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35152528
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 35152675
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

22 Experts available now in Live!

Get 1:1 Help Now