Solved

Query with date field not sorting correctly

Posted on 2011-02-14
13
416 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
[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
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
Technology Partners: 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!

 
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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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