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

Query with date field not sorting correctly

Posted on 2011-02-14
13
415 Views
Last Modified: 2013-11-05
I have a query in which I want to get monthly totals. I take a date field and format it "mm-yy" and do a group by that field. It all works out fine. However, when I try to order that field ascending, which would put 12-10 before 01-11, it doesn't work out. Here is my SQL statement and a pic:
 
SELECT Format([implementDate],"mm-yy") AS Expr1, Sum(tblTheHistory.LaborImprovement) AS SumOfLaborImprovement INTO tblMonthlySavings
FROM tblTheHistory
WHERE (((tblTheHistory.trialNumber)>0) AND ((tblTheHistory.implemented)=-1))
GROUP BY Format([implementDate],"mm-yy");

Open in new window

Query Results
I will be honest, I got this figured out once before in another db, but for the life of me I cannot remember what I did.

Thanks for any assistance.
0
Comment
Question by:G Scott
13 Comments
 
LVL 5

Expert Comment

by:simonpaul64
ID: 34887163
Order by implementDate without the mask - which should order correctly by the date as stored in the db.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34887185
Format will treat  it as  string and sort as string .
0
 
LVL 1

Author Comment

by:G Scott
ID: 34887257
But when I do that I lose the grouping of monthly totals.

 No more totals
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 5

Expert Comment

by:simonpaul64
ID: 34887283
You should be able to select with the format and order without.

Show us the sql that you used with the order...
0
 
LVL 5

Expert Comment

by:simonpaul64
ID: 34887331
This is an example - although SQL server needed the un formatted date field because of the group....

SELECT CONVERT(VARCHAR(10), EFFDT, 12) , EFFDT, count(*)
FROM PS_JOB
WHERE EMPLID LIKE '018004'
group by CONVERT(VARCHAR(10), EFFDT, 12), EFFDT
ORDER BY EFFDT DESC
0
 
LVL 1

Author Comment

by:G Scott
ID: 34887332
SELECT tblTheHistory.implementDate, Sum(tblTheHistory.LaborImprovement) AS SumOfLaborImprovement INTO tblMonthlySavings
FROM tblTheHistory
WHERE (((tblTheHistory.trialNumber)>0) AND ((tblTheHistory.implemented)=-1))
GROUP BY tblTheHistory.implementDate;

Open in new window


I am actually taking the sum by month and throwing it into a graph on a report.
0
 
LVL 5

Expert Comment

by:simonpaul64
ID: 34887467
Try this...

SELECT Format([implementDate],"mm-yy") AS Expr1, implementDate, Sum(tblTheHistory.LaborImprovement) AS SumOfLaborImprovement INTO tblMonthlySavings
FROM tblTheHistory
WHERE (((tblTheHistory.trialNumber)>0) AND ((tblTheHistory.implemented)=-1))
GROUP BY Format([implementDate],"mm-yy")
order by implementDate desc;
0
 
LVL 1

Author Comment

by:G Scott
ID: 34887551
That statement produced this error:

 Got this error
0
 
LVL 5

Expert Comment

by:simonpaul64
ID: 34887688
My bad - try this. I'm including an extra column, which you hopefully can ignore in your application...

SELECT Format([implementDate],"mm-yy") AS Expr1, implementDate, Sum(tblTheHistory.LaborImprovement) AS SumOfLaborImprovement INTO tblMonthlySavings
FROM tblTheHistory
WHERE (((tblTheHistory.trialNumber)>0) AND ((tblTheHistory.implemented)=-1))
GROUP BY Format([implementDate],"mm-yy"), implementDate
order by implementDate desc;
0
 
LVL 1

Author Comment

by:G Scott
ID: 34888708
Hmmm...still putting 17 rows in my table instead of the 3 for each month of data. Unless I am going about it wrong.
0
 
LVL 5

Accepted Solution

by:
simonpaul64 earned 300 total points
ID: 34889310
Yeah of course - having the date makes every date unique - not what you want at all. Sorry about that. It would be good to know what database you're using. I'll assume it's ql

I think you'll need to have year and date in separate columns. I've simplified this a bit - try that...

SELECT YEAR(implementDate), MONTH(implementDate) ,  Sum(tblTheHistory.LaborImprovement) AS SumOfLaborImprovement
FROM tblTheHistory
WHERE (((tblTheHistory.trialNumber)>0) AND ((tblTheHistory.implemented)=-1))
GROUP BY YEAR(implementDate), MONTH(implementDate)
order by YEAR(implementDate) desc , MONTH(implementDate) desc

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 34890238
Have you tried-
GROUP BY Format([implementDate],"mm-yy")
ORDER BY Format([implementDate],"yy-mm");
0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 34890774
Both of these solutions worked. Thanks for your help on this.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

790 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