asked on

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

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.

simonpaul64

Order by implementDate without the mask - which should order correctly by the date as stored in the db.

Pratima

Format will treat it as string and sort as string .

G Scott

ASKER

But when I do that I lose the grouping of monthly totals.

simonpaul64

You should be able to select with the format and order without.

Show us the sql that you used with the order...

simonpaul64

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

G Scott

ASKER

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.

simonpaul64

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;

G Scott

ASKER

That statement produced this error:

simonpaul64

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;

G Scott

ASKER

Hmmm...still putting 17 rows in my table instead of the 3 for each month of data. Unless I am going about it wrong.

ASKER CERTIFIED SOLUTION

simonpaul64

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

awking00

membership