Solved

Query with date field not sorting correctly

Posted on 2011-02-14
13
411 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 31

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

11 Experts available now in Live!

Get 1:1 Help Now