Query with date field not sorting correctly

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.
LVL 1
G ScottAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
simonpaul64Connect With a Mentor Commented:
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
 
simonpaul64Commented:
Order by implementDate without the mask - which should order correctly by the date as stored in the db.
0
 
Pratima PharandeCommented:
Format will treat  it as  string and sort as string .
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
G ScottAuthor Commented:
But when I do that I lose the grouping of monthly totals.

 No more totals
0
 
simonpaul64Commented:
You should be able to select with the format and order without.

Show us the sql that you used with the order...
0
 
simonpaul64Commented:
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
 
G ScottAuthor Commented:
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
 
simonpaul64Commented:
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
 
G ScottAuthor Commented:
That statement produced this error:

 Got this error
0
 
simonpaul64Commented:
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
 
G ScottAuthor Commented:
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
 
awking00Connect With a Mentor Commented:
Have you tried-
GROUP BY Format([implementDate],"mm-yy")
ORDER BY Format([implementDate],"yy-mm");
0
 
G ScottAuthor Commented:
Both of these solutions worked. Thanks for your help on this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.