Как заказать дни недели с общим количеством строк и столбцов в динамическом пивоте

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

А другая таблица содержит основную информацию о пользователе, такую ​​как имя, фамилия, адрес электронной почты, пароль

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

select distinct employeeid ,sum(hours) as TotalHours ,'MONDAY' as Day into #Monday from Project_TimeSheet where  year(entrydate)='2014' and month(entrydate)='12' and datename(dw,entrydate)='MONDAY' group by employeeid

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

Источник

3 ответа

Решение
SELECT employeeid,
SUM(CASE WHEN datename(dw,entrydate)='MONDAY' THEN hours END) as Monday,
SUM(CASE WHEN datename(dw,entrydate)='TUESDAY' THEN hours END) as Tuesday,
.....,
sum(hours) as TotalHours
FROM Project_TimeSheet
WHERE  year(entrydate)='2014' and month(entrydate)='12'
GROUP BY employeeid
WITH ROLLUP
Источник

Вот пример таблицы

и пример кода

CREATE TABLE #TEMP (Name varchar(10), [DATE] datetime,
            TotalHours int)
INSERT #TEMP VALUES 
('A','01/JAN/2014',10), 
('B','02/JAN/2014',20), 
('A','03/JAN/2014',20), 
('B','04/JAN/2014',30), 
('A','05/JAN/2014',40), 
('B','06/JAN/2014',50), 
('A','07/JAN/2014',60),
('A','08/JAN/2014',65),
('Z','07/JAN/2014',72),
('B','15/FEB/2014',70),
('B','16/FEB/2014',50), 
('A','17/FEB/2014',60),
('B','18/FEB/2014',70)

Установите понедельник в качестве первого дня недели на сервере Sql.

SET DATEFIRST 1;

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

SELECT DISTINCT DATENAME(WEEKDAY,[DATE])WK
,DATEPART(DW,[DATE]) WDNO
INTO #ORDERTABLE
FROM #TEMP
UNION ALL
SELECT 'TOTAL HOURS',8
ORDER BY 2,1

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

SELECT 
CASE Name WHEN 'TOTAL BY DAY' THEN 0
          ELSE DENSE_RANK() OVER(ORDER BY NAME DESC)
          END RNO,* 
INTO #NEWTABLE
FROM
(
    SELECT CASE WHEN Name IS NULL THEN 'TOTAL BY DAY' ELSE Name END Name,
    CASE WHEN DATENAME(WEEKDAY,[DATE]) IS NULL THEN 'TOTAL HOURS' ELSE DATENAME(WEEKDAY,[DATE]) END WK,
    SUM(TotalHours)TotalHours   
    FROM #TEMP
    WHERE YEAR([DATE])=2014 AND DATENAME(MONTH,[DATE])='JANUARY'
    GROUP BY Name,DATENAME(WEEKDAY,[DATE])
    WITH CUBE
)TAB
ORDER BY RNO DESC

Выберите отдельные столбцы из #ORDERTABLE Таблица

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + WK + ']', 
               '[' + WK + ']')
               FROM    (SELECT DISTINCT WK,WDNO FROM #ORDERTABLE) PV  
               ORDER BY WDNO

Теперь поверните запрос и упорядочите RNO(в котором мы применили логику, чтобы вывести Total в последнюю строку)

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT NAME,' + @cols + ' FROM 
             (
                 SELECT * FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 SUM(TotalHours)
                 FOR WK IN (' + @cols + ')
            ) p       
            ORDER BY RNO DESC
            ' 

EXEC SP_EXECUTESQL @query

и вот результат

Вот SQLFIDDLE http://sqlfiddle.com/(если он показывает какую-либо ошибку при загрузке, просто нажмите кнопку RUNSQL, она будет работать)

Вот обновление, где нам нужно найти Startdate и EndDate этой недели для определенной даты (добавьте ниже сразу после вставки в таблицу #TEMP)

DECLARE @STARTDATE DATE;
DECLARE @ENDDATE DATE;

SELECT 
@STARTDATE = CASE WHEN DATENAME(WEEKDAY,[DATE])='MONDAY' THEN [DATE]
      WHEN DATENAME(WEEKDAY,[DATE])='SUNDAY' THEN DATEADD(DAY,-6,[DATE]) 
      ELSE DATEADD(DAY,-datepart(dw,[DATE])+1,[DATE]) END 
,@ENDDATE = CASE WHEN DATENAME(WEEKDAY,[DATE])='MONDAY' THEN DATEADD(DAY,6,[DATE])
      WHEN DATENAME(WEEKDAY,[DATE])='SUNDAY' THEN [DATE]
      ELSE DATEADD(DAY,-datepart(dw,[DATE])+7,[DATE]) END 
FROM #TEMP
WHERE [DATE]=CAST('2014-01-06' AS DATE)

И используйте вышеупомянутые переменные в WHERE состояние (до КУБА) т.е.

WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE
Источник

Попробуй как,

    Declare @Year varchar(4)=2014
    Declare @Month varchar(2)=12
    Declare @Input datetime=@Year+'/'+@Month+'/'+'01'
    select @input


    select  employeeid 
    ,(Select sum(hours) as TotalHours from Project_TimeSheet M where year(entrydate)='2014' 
    and month(entrydate)='12' and datename(dw,entrydate)='MONDAY'  and m.employeeid=pts.employeeid  )'MONDAY' 
    from Project_TimeSheet PTS 
    ,(Select sum(hours) as TotalHours from Project_TimeSheet M where year(entrydate)='2014' 
    and month(entrydate)='12' and datename(dw,entrydate)='TUESDAY'  and m.employeeid=pts.employeeid  )'TUESDAY' 
    .....,
    from Project_TimeSheet PTS  
    where  year(entrydate)='2014' and month(entrydate)='12' and datename(dw,entrydate)='MONDAY' 
    group by employeeid

OR 

select 
SUM(MONDAY)MONDAY,SUM(TUESDAY)TUESDAY
,........
sum(hours)TotalHours
from 
(select  employeeid 
,case WHEN datename(dw,entrydate)='MONDAY' THEN hours else 0 end 'MONDAY'
,case WHEN datename(dw,entrydate)='TUESDAY' THEN hours else 0 end 'TUESDAY'
,.....
,hours
from Project_TimeSheet PTS  
where  year(entrydate)='2014' and month(entrydate)='12' and datename(dw,entrydate)='MONDAY' 
)tbl
group by employeeid
Источник
Другие вопросы по тегам