Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query with date field not sorting correctly

Posted on 2011-02-14
13
Medium Priority
?
432 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month12 days, 22 hours left to enroll

971 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