?
Solved

ORDER BY converted week range

Posted on 2011-03-16
12
Medium Priority
?
289 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
[X]
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
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:Ephraim Wangoya
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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:
Ephraim Wangoya earned 2000 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:Ephraim Wangoya
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

777 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