Правильный порядок дат в JET SQL - Access 2010

ProgramBox

Правильный порядок дат в JET SQL — Access 2010

Post author:admin
Запись опубликована:1 сентября, 2022
Post category:Вопросы по программированию

#sql #ms-access-2010 #jet-sql

Вопрос:

Хорошо, очень разочарован, пробуя разные варианты, нужно какое-то предложение.

Я попытался создать своего рода ROLLUP CUBE запрос, JET-SQl и все работает должным образом, за исключением правильного упорядочения DATE полей.

 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')) amp; ' 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')) amp; ' TOTAL'
ORDER BY 1

UNION

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

[WORK TYPE] amp; ' 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] amp;' 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

Часть выходных данных

 ╔═════════════════╦═════════════════════════╦═══════════════════════╦════════════╦═════════════╦════════════════╦══════════════╗
║      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     ║
╚═════════════════╩═════════════════════════╩═══════════════════════╩════════════╩═════════════╩════════════════╩══════════════╝

Да, ORDERBY это работает не так, как ожидалось.

Кто-нибудь может подсказать, как это правильно настроить

ОБНОВЛЕНИЕ 1

Я попытался заменить CDATE бит на DATEVALUE , но это тоже не работает
Я пробовал использовать order by as DATEVALUE(P.[START TIME]) amp; ' TOTAL' , но это тоже не помогло

ОБНОВЛЕНИЕ2

У меня не было другого выбора, кроме как сделать что-то подобное, не очень доволен, но из-за нехватки времени хочу продолжить это, если у кого-нибудь есть лучший способ предложить

 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 'amp; 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] amp; ' 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] amp;' 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')) 
)

1. Вы смешиваете данные о дате с текстовыми данными. В результате конечный формат столбца будет полностью текстовым. Попробуйте удалить строки TOTAL и Z-TOTAL-Z , чтобы убедиться, что у вас остались только даты, и посмотрите, дает ли это вам правильную сортировку? В качестве альтернативы, вы можете выполнить сортировку по фактическим датам (т.е. явно ORDER BY CDATE(FORMAT(P.[START TIME],'DD/MM/YYYY')) , а не по столбцу, который вы возвращаете в SELECT .

2. @SchmitzIT Я согласен с вашей точкой зрения, смешивающей дату и текст, однако весь мой смысл в написании union заключается в том, чтобы показать total поле, но я также попробовал ваше предложение о сохранении общего количества поданных date (и я тоже пытался null ), что также не сработало. Я уже пробовал ваше второе предложение о явном упоминании ORDER BY материала, которое также не работает

3. Только что увидел обновление. В вашем ЗАКАЗЕ по-прежнему есть текст. Это означает, что вы все равно получаете сортировку ASCII. Вы пробовали использовать предложенный мной синтаксис, используя только даты?

Метки: Правильный порядок дат в JET SQL — Access 2010

Вопрос:

Комментарии:

Вам также может понравиться

Как разделить строку разными словами

отображать динамическую страницу с использованием перенаправления htaccess

пользователь servicestack auth не может авторизоваться