SQL QUERY с настраиваемыми столбцами из SUM
Я хотел бы построить SQL-запрос, чтобы получить сумму часов (разница между концом и началом) по неделям для каждого человека. В моей основной таблице у меня есть 3 столбца:
Ожидаемый мной результат:
Спасибо за вашу помощь
Я не знаю, как получить пользовательские столбцы с вычисленным результатом из моей основной таблицы.
Наличие недель в виде столбцов делает вашу проблему намного сложнее, а решение негибким - если вы добавите больше данных, которые, например, дадут вам W04, вам, вероятно, придется редактировать свой SQL - так могли бы вы вместо этого сделать это в виде строк? В любом случае, как вы понимаете WK01? Неделя 1 по сравнению с самой ранней датой в вашей таблице, по сравнению с фиксированной датой (какая дата?) или чем-то еще?
Дайте определение неделе. (ISO 8601 с понедельника по воскресенье или что-то еще.)
Я получаю номер недели с помощью функции DATEPART(week,startDT)
какой sql вы используете?
Возникли некоторые проблемы с данными выборки и результатами. Самый большой из них: endDT может быть перед startDT, и каждая строка охватывает несколько недель. Например, идентификатор 2 не будет равен 9 для недели 2.
Ответы 1
Код, выполненный на SQL Server, состоит из 3 шагов.
- Создайте запрос CTE для расчета разницы во времени и номера недели.
- Создайте запрос CTE для суммирования часов по идентификатору и номеру недели.
- Создайте сводную таблицу для преобразования недель в столбцы Примечание. Для примера использовались данные с поправкой на дату.
Решение
WITH hours_calculated as (
SELECT [ID]
,[startDT]
,[endDT]
, DATEDIFF(hh, [startDT], [endDT]) as Hours_Difference
, CAST((DAY([startDT])-1)/7 AS INT) +1 AS WeekNumber
FROM [development].[dbo].[hours_by_week]
), weeks_calculated as (
select ID, WeekNumber,SUM(Hours_Difference) as total_hours from hours_calculated group by ID, WeekNumber
)
select ID , [1] as W01,[2] as W02,[3] as W03,[4] as W04,[5] as W05
from weeks_calculated AS SourceTable
pivot
(
SUM(total_hours)
FOR WeekNumber IN ([1],[2],[3],[4],[5] )
) AS PivotTable;
Выход
ID W01 W02 W03 W04 W05
1 7 NULL NULL NULL NULL
2 NULL 9 NULL NULL NULL
3 2 4 3 NULL NULL
Схема
CREATE TABLE [dbo].[hours_by_week](
[ID] [tinyint] NOT NULL,
[startDT] [datetime2](7) NOT NULL,
[endDT] [datetime2](7) NOT NULL
) ON [PRIMARY]
GO
Ссылки
Группировка по неделям определенного месяца на сервере sql
Как получить общее количество часов между двумя датами на сервере sql?
Большое спасибо !
Хорошо, какой у тебя вопрос? Что именно не работает так, как вы ожидаете? В любом случае вам следует предоставить пример входных данных.