Вопрос о хранимой процедуре Crystal Reports + SQL Server с GROUP BY

Я пишу отчет в Crystal Reports XI Developer, который запускает хранимую процедуру в базе данных SQL Server 2005. Набор записей возвращает сводку из таблицы журнала, сгруппированную по дню и часу.

В настоящее время мой запрос выглядит примерно так:

SELECT
    sum(colA) as "Total 1", 
    day(convert(smalldatetime, convert(float, Timestamp) / 1440 - 1)) as "Date",
    datepart(hh, convert(smalldatetime, convert(float, Timestamp) / 1440 - 1)) as "Hour"
    `etc...`
GROUP BY 
    Day, Hour

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

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

Я знаю, что могу изменить это, поместив весь запрос в цикл WHILE (внутри хранимой процедуры) и выполняя запросы в отдельные часы, но что-то внутри меня говорит, что один запрос лучше, чем 24.

Я хотел бы знать, есть ли способ заставить Crystal перебирать часы дня, а не перебирать строки в таблице, как обычно.

В качестве альтернативы, есть ли способ отформатировать запрос так, чтобы он включал пустые строки часов, не убивая мой сервер базы данных?


sql-server stored-procedures crystal-reports crystal-reports-xi
person nageeb    schedule 07.06.2011    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вот как я решил эту проблему:

  1. Создайте локальную таблицу на вашем SQL-сервере. Назовите это «LU_Hours».
  2. В этой таблице будет 1 целочисленное поле (называемое «Часы») с 24 строками. Конечно, значения будут от 1 до 24.
  3. Право присоедините это к вашему существующему запросу.
  4. Возможно, вам придется настроить это, чтобы убедиться, что нули пустых часов обрабатываются так, как вам удобно.
person PowerUser    schedule 07.06.2011
comment
Поскольку я группирую по дням И по часам, имеет ли смысл создать таблицу со столбцами для дней месяца (1-31), а также для часов дня? Как: (1,0) (1,1) ... (1,23) (2,1) (2,2) ... (31,22) (31,23) - person nageeb; 07.06.2011
comment
Дни месяца немного сложнее, так как они различаются по месяцам, а некоторые годы являются високосными. Составьте таблицу с датами за несколько лет (для этого подходит Excel). Затем присоедините эту таблицу LU_Date к LU_Hours, чтобы получить последнюю вспомогательную таблицу. - person PowerUser; 09.06.2011

arrow_upward
2
arrow_downward

Вы можете использовать предложение WITH для создания 24 часов, а затем OUTER JOIN.

WITH hours AS (

  SELECT 1 AS hour
  UNION
  SELECT 2 AS hour
  ...
  SELECT 24 AS hour

)

SELECT *
FROM hours h
LEFT OUTER JOIN [your table] x ON h.hour=x.datepart(hh, convert(smalldatetime, convert(float, Timestamp) / 1440 - 1))

Этот SQL нужно будет добавить в команду.

При необходимости сгруппируйте в SQL или в отчете.

person craig    schedule 07.06.2011