Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ORDER BY converted week range

Posted on 2011-03-16
12
286 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 34

Expert Comment

by:Paul MacDonald
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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