Вывод Сумма некоторого столбца в недельных интервалах в течение года, даты недели соответствуют дню

Мне нужна помощь, так как я застрял. Это использование SQL и Coldfusion. По сути, у меня есть таблица, в которой указано завершение определенных билетов, а также дата, когда они были отмечены как завершенные. Например, в этой таблице есть район и область, которые используются в GROUP BY. Итак, я хочу подсчитать каждый билет, который был сделан каждую неделю года, который пользователь выбирает, например, для запуска отчета. Я перечислю немного SQL, который у меня есть на данный момент.

 SELECT                                           
    SUM(CASE WHEN DATEPART(ww, completionDate) = 1 THEN 1 ELSE 0 END) AS [Jan1-7],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 2 THEN 1 ELSE 0 END) AS [Jan8-14],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 3 THEN 1 ELSE 0 END) AS [Jan15-21],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 4 THEN 1 ELSE 0 END) AS [Jan22-31]    
    SUM(CASE WHEN DATEPART(ww, completionDate) = 5 THEN 1 ELSE 0 END) AS [Feb1-7],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 6 THEN 1 ELSE 0 END) AS [Feb8-14],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 7 THEN 1 ELSE 0 END) AS [Feb15-21],
    SUM(CASE WHEN DATEPART(ww, completionDate) = 8 THEN 1 ELSE 0 END) AS [Feb22-28],

Как вы можете видеть, я в основном пытаюсь определить, какой неделей года была дата завершения, и суммировать это в отдельной колонке. Это должно быть в этом формате, если у кого-то нет лучшего предложения. Проблема в том, например, что 18 февраля 2013 г., например, SQL говорит мне, что это будет 8-я неделя 2013 г., тогда как мой код СУММУЛИРУЕТ это в FEB15-21, что является 7-й неделей в соответствии с моим кодом. Мне нужен отчет, чтобы показать месяц в верхнем заголовке, а затем показать каждую неделю в следующем заголовке этого месяца, а затем, в основном, подсчитать каждый билет, выполненный за эту неделю. Таким образом, недели/месяцы должны быть столбцами.

Я прочитал некоторые функции CF и все такое, но просто не знаю, как их использовать, чтобы получить то, что мне нужно. Есть ли способ динамически СУММИЗИРОВАТЬ эти даты, гарантируя, что они СУММУЛИРУЮТСЯ в правильном столбце в зависимости от недели, или использовать Coldfusion для обработки этой части? Но я также не слишком уверен в части ColdFusion, поэтому, если возможно, любые предложения по использованию SQL или CF и, возможно, помощь мне в части кода будут действительно оценены.

Спасибо!!


sql sum dayofweek coldfusion
person ScrumMaster Guy    schedule 21.03.2013    source источник
comment
См. ответ на этот вопрос. Этот ответ основан на сборе дат по месяцам, но его легко адаптировать к неделям (или дням, часам и т. д.).   -  person Dale M    schedule 21.03.2013
comment
Как могло 22-31 января - это неделя, это 9 дней....   -  person ljh    schedule 21.03.2013
comment
почему ваши недели не настоящие недели?   -  person Matt Busche    schedule 21.03.2013
comment
Я узнал, что это нормально, чтобы иметь его в интервалах дат. Не обязательно учитывать неделю/день недели. Так что наличие 1-7..8-14..15-21 и 22 до последнего числа этого месяца - это нормально. Всем спасибо за помощь и ответы.   -  person ScrumMaster Guy    schedule 22.03.2013

Ответы (3)


arrow_upward
0
arrow_downward

Я рекомендую создать таблицу базы данных, где первичным ключом является дата. Другие поля в этой таблице будут включать строку недели (7–13 января), а также все остальное, праздники, финансовую информацию и т. д., которые вам нужны.

Для некоторых вещей вам понадобится сценарий обслуживания, и вам, возможно, придется вручную выполнять праздничную часть. Это то, что мы делаем с нашим хранилищем данных.

Это делает отчет, который вы в настоящее время пытаетесь сделать, очень простым.

person Dan Bracuk    schedule 21.03.2013

arrow_upward
0
arrow_downward

Я не уверен на 100%, что правильно понял, но почему бы не попробовать этот запрос. Это будет подсчитывать частоты для каждой комбинации недели/месяца, что позволит вам разделить ее по неделям или месяцам по желанию на выходе. Затем вы можете использовать логику CF, чтобы определить даты ограничения для целей вывода.

SELECT

    -- Get the week and month of each row
    MONTH(completionDate) AS monthOfYear,
    WEEK(completionDate) AS weekOfYear,

    -- Count how many rows are in this group
    COUNT(*) AS frequency

FROM yourTable AS yt

-- If you are only interested in a specific date range, chuck in something like this
WHERE yt.completionDate BETWEEN DATE('2013-01-01') AND ('2013-12-30')

-- Order by month and then week, so that week 4 in Jan comes before week 4 in Feb
GROUP BY
    monthOfYear ASC,
    weekOfYear ASC

Надеюсь, это поможет, если это не совсем то, что вы имели в виду, пожалуйста, уточните вопрос.

Если это для очень большой таблицы, и вас просто интересует статистика на конец дня, рассмотрите возможность выполнения чего-то вроде следующего после каждого дня в таблице с простой структурой со столбцами statsDate DATE NOT NULL PK и statsFrequency INTEGER UNSIGNED NOT NULL

INSERT INTO yourStatsTable(
    statsDate,
    statsFrequency
)
SELECT
    DATE(completionDate),
    COUNT(*)
FROM yourTable AS yt
WHERE yt.completionDate >= SUBDATE(CURRENT_DATE,1) 
AND yt.completionDate < CURRENT_DATE
ON DUPLICATE KEY UPDATE statsFrequency = VALUES(statsFrequency)

Это даст вам таблицу с подсчетами за предыдущие дни, которую можно запросить, не затрагивая основную таблицу (но опять же, делайте это только в том случае, если это большая/занятая таблица, и генерация их на лету нецелесообразна)

person Simon at My School Portal    schedule 22.03.2013

arrow_upward
0
arrow_downward

Я думаю, что вас смущает то, как MSSQL обрабатывает номера недель. Все форматы недели пн-вс. Неделя 1 в этом случае начинается с понедельника 31 декабря 2012 г. по воскресенье 6 января 2013 г. (а не с 1 января 2013 г. по 7 января 2013 г., как вы предполагаете), неделя 2 : с 07.01.13 по 13.01.13 и так далее... 18.02.2013 приходится на восьмую неделю года, потому что он начинается на восьмой неделе (понедельник).

Вы захотите использовать что-то вроде:

DATEADD(ww, DATEDIFF(ww,0,completionDate), 0) AS [Start Of Week],
DATEADD(s,-1,DATEADD(ww, DATEDIFF(ww,0,GETDATE())+1,0)) as [End Of Week]

чтобы получить даты начала и окончания и использовать их в своей группе по предложению и суммированию.

Однако, чтобы соответствовать вашему коду, вы можете добавить количество дней смещения к [Начало недели] и [Конец недели], чтобы получить свои диапазоны. В 2013 году это смещение составит 1 день с 01 января 2013 года (человеческое начало года) – 31 декабря 2012 года (sql-начало года). Таким образом, все даты будут соответствовать формату вашей недели [1-7 января].

Надеюсь это поможет. -Мин

person Minh Vo    schedule 22.03.2013