How do I order weekdays with row and column total in dynamic pivot

Question

I have a table in which I have some Employee details like id,employeeid,workdate,taskid,hours,entrydate,entryby

And the other table have the basic information about user like firstname,lastname,emailid,password

now I want to create a crosstab query in which I want to display username and the weekday and the working total hours of the employee. well first i was thinking to use temp table for this But I failed to do so I use something like this for each day of week

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

But for me this is not working. Anybody please tell me the query for this using pivot I want the result something like this

Answer 1

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

Answer 2

try like,

    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

Answer 3

Here is the sample table

and the sample code

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)

Set monday as your First day of the week in Sql Server

SET DATEFIRST 1;

Insert data into a new table to order the weekedays and bring total as the last column

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

Now we do the logic for calculating the total for each Name and Weekdays and bring row-Total in the last row.

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

Select the distinct columns from the #ORDERTABLE table

DECLARE @cols NVARCHAR (MAX)

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

Now pivot the query and order by RNO(in which we have applied logic to bring Total in the last row)

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

and here is the result

Here is the SQLFIDDLE http://sqlfiddle.com/#!3/655df/6 (If it shows any error on loading just click RUNSQL button, it will work)

Here is an update where we need to find Startdate and EndDate of that week for a given date(Add the below just after the insertion into #TEMP table)

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)

And use the above variables in the WHERE condition(before the CUBE) ie,

WHERE [DATE] BETWEEN @STARTDATE AND @ENDDATE