SQL Query Help - Joins and Pivots

Question

Hello,

I have a couple of tables: One contains rows that represent approved time sheet entries. Each row has a unique combination of employee code and date, plus some other data, including the number of hours worked on that day.

I also have a date dimension table that contains a column for the week number, the date of the start of the week, the date of the end of the week and the year etc.

I need to pivot the time sheets such that all the time entries in the time sheet table that match the same employee code from the time sheet table and end of week date from the date table appear as columns, with one column for each day of that week.

For example here is the time sheet table:

The second and third rows, employee code 9998 worked on the 8rd and 9th of December. These dates are in the same week.

I can see all the last day of the week from the date dimension table for any given date like this:

declare @mydate as date = '2020-12-08'  
				    
select top(1)  
TheDate  
,TheISOWeek  
,TheFirstOfWeek  
,TheLastOfWeek  
,@mydate as [input date]  
  
from DateDimension where thefirstofweek = (select thefirstofweek from DateDimension where thedate = @mydate) order by TheDate desc  

..or if I select all the records, not just top(1) I can see the whole week.

For each row I need to join any data from the time sheet then pivot that so each day of the week is a column containing the corresponding Hours worked from the timesheet.

My result I need is a table that is grouped by Employee code and TheLastOfWeek with columns for each day of that week containing the Hours worked, or NULL.

Using the example of the 2nd and 3rd rows above, I'd have a single row with Employee Code 9998, TheLastOfWeek is '2020-12-13', and columns Day1 is NULL, Day2 is 9.08, Day3 is 4.35, Day 4 is NULL etc...

I'm struggling with the logic of this and how to write a query to that.

Any help greatly appreciated.

Answer 1

Hi @Will Page ,

Thank you so much for posting here in Microsoft Q&A.

Please refer below and check whether it is working to you：

Step1： Timesheet Table createtion.

 create table Timesheet  
(  
id int identity(1,1),  
[Employee Code] int,  
[Date] date,  
[Hours Worked] decimal(8,2)  
)  
  
insert into Timesheet ([Employee Code],[Date],[Hours Worked]) values  
(9998,'2020-12-15',8),  
(9998,'2020-12-08',9.08),  
(20,'2020-12-16',8),  
(9998,'2020-12-09',4.35),  
(30,'2020-12-16',9),  
(10,'2020-12-16',9),  
(9998,'2020-12-03',1.24),  
(9998,'2020-12-20',8)  

Step2： DateDimension Table createtion.

CREATE TABLE DateDimension   
  (TheDate DATE,  
  TheISOweek INT,  
  TheFirstOfWeek DATE,  
  TheLastOfWeek DATE  
  )  
  
SET DATEFIRST  1, -- 1 = Monday, 7 = Sunday  
    DATEFORMAT mdy,   
    LANGUAGE   US_ENGLISH;  
  
DECLARE @StartDate  date = '20200101';  
  
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));  
  
;WITH seq(n) AS   
(  
  SELECT 0 UNION ALL SELECT n + 1 FROM seq  
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)  
),  
d(d) AS   
(  
  SELECT DATEADD(DAY, n, @StartDate) FROM seq  
),  
src AS  
(  
  SELECT  
    TheDate         = CONVERT(date, d),  
    TheISOWeek      = DATEPART(ISO_WEEK,  d),  
    TheDayOfWeek    = DATEPART(WEEKDAY,   d)  
  FROM d  
),  
dim AS  
(  
  SELECT  
    TheDate,   
    TheISOweek,  
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),  
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate))  
  FROM src  
)  
  
  INSERT INTO DBO.DateDimension  
  SELECT * FROM dim  
    ORDER BY TheDate  
  OPTION (MAXRECURSION 0);  

Step3： Final query:

;with cte as(  
select a.[Employee Code],b.TheISOweek,b.TheLastOfWeek  
  from Timesheet a  
inner join DateDimension b on a.Date=b.TheDate)  
,cte1 as (  
select distinct a.TheDate,a.TheLastOfWeek,b.[Employee Code]  
 from DateDimension a   
 inner join cte b    
 on  a.TheISOweek=b.TheISOweek and a.TheLastOfWeek=b.TheLastOfWeek   
 )  
,cte2 as (  
 select *  
 ,ROW_NUMBER() OVER(PARTITION BY [Employee Code],TheLastOfWeek ORDER BY thedate) RN  
 from cte1)  
,CTE3 AS (  
select distinct a.[Employee Code],a.TheLastOfWeek,b.[Hours Worked]  
,'DAY'+trim(CAST(RN AS char)) DAYNUM  
 from cte2 a  
left join Timesheet b   
on a.TheDate=b.Date and a.[Employee Code]=b.[Employee Code]  
)  
select * from   
(select * from CTE3 ) a  
pivot  
(max([Hours Worked])   
for DAYNUM in ([DAY1],[DAY2],[DAY3],[DAY4],[DAY5],[DAY6],[DAY7])) p  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Thanks. If I wanted to inclcude the id of the original row from Timesheet in the pivot, so I can reference back to the original table for each day, do I add this to CTE3 and include in the pivot?

Answer 3

HI @Will Page ,

If I wanted to inclcude the id of the original row from Timesheet in the pivot, so I can reference back to the original table for each day, do I add this to CTE3 and include in the pivot?

Please refer below:

;with cte as(  
 select a.[Employee Code],b.TheISOweek,b.TheLastOfWeek  
   from Timesheet a  
 inner join DateDimension b on a.Date=b.TheDate)  
 ,cte1 as (  
 select distinct a.TheDate,a.TheLastOfWeek,b.[Employee Code]  
  from DateDimension a   
  inner join cte b    
  on  a.TheISOweek=b.TheISOweek and a.TheLastOfWeek=b.TheLastOfWeek   
  )  
 ,cte2 as (  
  select *  
  ,ROW_NUMBER() OVER(PARTITION BY [Employee Code],TheLastOfWeek ORDER BY thedate) RN  
  from cte1)  
 ,CTE3 AS (  
 select distinct b.id,a.[Employee Code],a.TheLastOfWeek,b.[Hours Worked]  
 ,'DAY'+trim(CAST(RN AS char)) DAYNUM  
  from cte2 a  
 left join Timesheet b   
 on a.TheDate=b.Date and a.[Employee Code]=b.[Employee Code]  
 where b.id is not null  
 )  
 select * from   
 (select * from CTE3 ) a  
 pivot  
 (max([Hours Worked])   
 for DAYNUM in ([DAY1],[DAY2],[DAY3],[DAY4],[DAY5],[DAY6],[DAY7])) p  

Output:

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 4

Thanks. I spent a bit of time to figure this out since I posted.

I needed the original timesheet id for each day, so I added that to the table and did two pivots to get Day1 Value, Day 1 id, Day 2 Value, Day 2 id etc.

This is so when the user exports the timesheet, I can reference back to the original row in the timesheet table for each day of the week and mark it as exported.

I managed to get it working by following this blog: https://learn.microsoft.com/en-nz/archive/blogs/kenobonn/pivot-on-two-or-more-fields-in-sql-server