Ordering Dates Correctly in JET SQL - Access 2010

Question

Ok, quite frustrated trying out different options, need some suggestion.

I have tried to create a sort of ROLLUP, CUBE query in JET-SQl and everything is working as expected except the correct ordering of DATE field.

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) AS [MNTH],
P.[WORK TYPE],
P.[CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
format(count (p.[work type])/(select count (p.[work type]) from p),'0.00%') as [Percentage Vol],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE 
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND

CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

GROUP BY 
[WORK TYPE],
P.[CASE TYPE],
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY'))
ORDER BY 1

UNION

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) & ' TOTAL' AS [MNTH],
'' AS [WORK TYPE],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
format(count (p.[work type])/(select count (p.[work type]) from p),'0.00%') as [Percentage Vol],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)


GROUP BY 

CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) & ' TOTAL'
ORDER BY 1

UNION

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) AS [MNTH],

[WORK TYPE] & ' TOTAL' AS [WORK TYPE1],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
format(count (p.[work type])/(select count (p.[work type]) from p),'0.00%') as [Percentage Vol],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

GROUP BY 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) ,
[WORK TYPE] &' TOTAL'

ORDER BY 1

UNION SELECT 
'Z-TOTAL-Z' AS [MNTH],

'' AS [WORK TYPE1],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
format(count (p.[work type])/(select count (p.[work type]) from p),'0.00%') as [Percentage Vol],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

ORDER BY 1

A part of the output is

╔═════════════════╦═════════════════════════╦═══════════════════════╦════════════╦═════════════╦════════════════╦══════════════╗
║      MNTH       ║        WORK TYPE        ║       CASE TYPE       ║ TOTAL TIME ║ TOTAL COUNT ║ Percentage Vol ║ AVERAGE TIME ║
╠═════════════════╬═════════════════════════╬═══════════════════════╬════════════╬═════════════╬════════════════╬══════════════╣
║ 1/6/2014        ║ Query                   ║ Deficit_on_SFS        ║ 00:18:55   ║           1 ║ 0.37%          ║ 00:18:55     ║
║ 1/6/2014        ║ Query                   ║ Rental_Income         ║ 00:00:24   ║           1 ║ 0.37%          ║ 00:00:24     ║
║ 1/6/2014        ║ Query                   ║ Support_from_Family   ║ 00:00:30   ║           1 ║ 0.37%          ║ 00:00:30     ║
║ 1/6/2014        ║ Query TOTAL             ║                       ║ 00:19:49   ║           3 ║ 1.10%          ║ 00:06:36     ║
║ 1/6/2014        ║ System_Downtime         ║ Downtime              ║ 00:00:18   ║           2 ║ 0.74%          ║ 00:00:09     ║
║ 1/6/2014        ║ System_Downtime TOTAL   ║                       ║ 00:00:18   ║           2 ║ 0.74%          ║ 00:00:09     ║
║ 1/6/2014 TOTAL  ║                         ║                       ║ 00:26:48   ║          21 ║ 7.72%          ║ 00:01:17     ║
║ 12/6/2014       ║ Decline_to_Branch       ║ Duplicate             ║ 00:02:01   ║           5 ║ 1.84%          ║ 00:00:24     ║
║ 12/6/2014       ║ Decline_to_Branch       ║ Incorrect_Docs        ║ 00:01:47   ║           3 ║ 1.10%          ║ 00:00:36     ║
║ 12/6/2014       ║ Decline_to_Branch       ║ Missing_Docs          ║ 00:01:35   ║           4 ║ 1.47%          ║ 00:00:24     ║
║ 12/6/2014       ║ Decline_to_Branch       ║ Not_for_ASU           ║ 00:00:23   ║           1 ║ 0.37%          ║ 00:00:23     ║
║ 12/6/2014       ║ Decline_to_Branch TOTAL ║                       ║ 00:05:46   ║          13 ║ 4.78%          ║ 00:00:27     ║
║ 12/6/2014 TOTAL ║                         ║                       ║ 00:05:46   ║          13 ║ 4.78%          ║ 00:00:27     ║
║ 2/6/2014        ║ Adhoc_Work              ║ Non_ASU_Related_Adhoc ║ 00:00:24   ║           2 ║ 0.74%          ║ 00:00:12     ║
║ 2/6/2014        ║ Adhoc_Work TOTAL        ║                       ║ 00:00:24   ║           2 ║ 0.74%          ║ 00:00:12     ║
║ 2/6/2014        ║ Assessment              ║ HL                    ║ 00:00:40   ║           2 ║ 0.74%          ║ 00:00:20     ║
║ 2/6/2014        ║ Assessment              ║ HL_and_BTL            ║ 00:01:20   ║           2 ║ 0.74%          ║ 00:00:40     ║
║ 2/6/2014        ║ Assessment TOTAL        ║                       ║ 00:02:00   ║           4 ║ 1.47%          ║ 00:00:30     ║
║ 2/6/2014        ║ Break                   ║ Lunch                 ║ 00:03:24   ║           6 ║ 2.21%          ║ 00:00:34     ║
║ 2/6/2014        ║ Break TOTAL             ║                       ║ 00:03:24   ║           6 ║ 2.21%          ║ 00:00:34     ║
╚═════════════════╩═════════════════════════╩═══════════════════════╩════════════╩═════════════╩════════════════╩══════════════╝

Yes, the ORDERBY is not working as expected.

Can anyone suggest how to set it right

UPDATE1

I have tried replacing the CDATE bit with DATEVALUE, but this is also not working
I have tried using order by as DATEVALUE(P.[START TIME]) & ' TOTAL', that also did not help

UPDATE2

I didn't had any choice but to do something like this, not very happy thou but due to time constraint want to go ahead with this, if anyone has a better way do suggest

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) AS [MNTH],
P.[WORK TYPE],
P.[CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
FORMAT(COUNT (P.[WORK TYPE])/(SELECT COUNT (P.[WORK TYPE]) FROM P),'0.00%') AS [PERCENTAGE VOL],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE 
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND

CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

GROUP BY 
[WORK TYPE],
P.[CASE TYPE],
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY'))

ORDER BY 1,2


UNION

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY'))  AS [MNTH],
'Z-TOTAL-Z FOR '& CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) AS [WORK TYPE],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
FORMAT(COUNT (P.[WORK TYPE])/(SELECT COUNT (P.[WORK TYPE]) FROM P),'0.00%') AS [PERCENTAGE VOL],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

GROUP BY
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) 

UNION

SELECT 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) AS [MNTH],

[WORK TYPE] & ' TOTAL' AS [WORK TYPE1],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
FORMAT(COUNT (P.[WORK TYPE])/(SELECT COUNT (P.[WORK TYPE]) FROM P),'0.00%') AS [PERCENTAGE VOL],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

GROUP BY 
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) ,
[WORK TYPE] &' TOTAL'

UNION 
SELECT 
NULL AS [MNTH],

'ZZ-GRAND TOTAL-ZZ' AS [WORK TYPE1],
'' AS  [CASE TYPE],
FORMAT(SUM(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [TOTAL TIME], 
COUNT([ID]) AS [TOTAL COUNT],
FORMAT(COUNT (P.[WORK TYPE])/(SELECT COUNT (P.[WORK TYPE]) FROM P),'0.00%') AS [PERCENTAGE VOL],
FORMAT(AVG(DATEDIFF("S",[START TIME],[END TIME]))/86400,"HH:NN:SS") AS [AVERAGE TIME]
FROM P
WHERE
SWITCH
(
FORMS!DASHBOARD!CUSER='ALL'
,
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
,

FORMS!DASHBOARD!CUSER<>'ALL'
,
P.[USER]=FORMS!DASHBOARD!CUSER AND
CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) BETWEEN CDATE(FORMAT(FORMS!DASHBOARD!CSTART,'DD/MM/YYYY')) 
AND 
CDATE(FORMAT(FORMS!DASHBOARD!CEND,'DD/MM/YYYY')) 
)

You're mixing date data with text data. As a result, the final format of the column will be all text. Try removing the `TOTAL` and `Z-TOTAL-Z` lines to ensure you only have dates left, and see if that does gives you proper sorting? Alternatively, you can sort by the actual dates (I.e. explicitly `ORDER BY CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY'))`, rather than by the column you return in the `SELECT`. — SchmitzIT, Jun 20 '14 at 07:20
@SchmitzIT I agree with your point mixing date and text, however my whole point in writing union is to show the `total` field, thou I tried your suggestion as well on keeping the total filed as `date` (and i tried `null` as well) that also did not work. I had already tried your second suggestion on explicitly mentioning the `ORDER BY` stuff, that is also not working — Youbaraj Sharma, Jun 20 '14 at 07:39
Just saw the update. Your ORDER BY still has the text in there. That means you're still ending up with ASCII sorting. Have you tried using the syntax I suggested, using the dates only? — SchmitzIT, Jun 20 '14 at 08:21

Ordering Dates Correctly in JET SQL - Access 2010

0 Answers0