CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2pivotpivot-table
dawncode
dawncode

Reputation: 618

pivot transform for multiple weeks

I have to convert rows into columns for all weeks for a given confirmationId but in my query it is doing it only for one week but there are several weeks exists under single ID. `

ConfirmationId       TimeSheetDate           DayName                        WorkingHours
-------------------- ----------------------- ------------------------------ ----------------------
1                    2006-07-18 00:00:00.000 Tuesday                        8
1                    2006-07-19 00:00:00.000 Wednesday                      8
1                    2006-07-20 00:00:00.000 Thursday                       8
1                    2006-07-21 00:00:00.000 Friday                         8
1                    2006-07-24 00:00:00.000 Monday                         8
1                    2006-07-25 00:00:00.000 Tuesday                        8
1                    2006-07-26 00:00:00.000 Wednesday                      8
1                    2006-07-27 00:00:00.000 Thursday                       8
1                    2006-07-28 00:00:00.000 Friday                         8
1                    2006-08-07 00:00:00.000 Monday                         8
1                    2006-08-08 00:00:00.000 Tuesday                        8
1                    2006-08-09 00:00:00.000 Wednesday                      8
1                    2006-08-10 00:00:00.000 Thursday                       8
1                    2006-08-11 00:00:00.000 Friday                         8
1                    2006-08-29 00:00:00.000 Tuesday                        8
1                    2006-08-30 00:00:00.000 Wednesday                      8
1                    2006-08-31 00:00:00.000 Thursday                       8
1                    2006-09-01 00:00:00.000 Friday                         8
1                    2006-10-09 00:00:00.000 Monday                         8
1                    2006-10-10 00:00:00.000 Tuesday                        8
1                    2006-10-11 00:00:00.000 Wednesday                      8
1                    2006-10-12 00:00:00.000 Thursday                       8
1                    2006-10-13 00:00:00.000 Friday                         8
1                    2006-10-23 00:00:00.000 Monday                         8
1                    2006-10-24 00:00:00.000 Tuesday                        8
2                    2007-06-22 00:00:00.000 Friday                         8
2                    2007-06-25 00:00:00.000 Monday                         8
2                    2007-06-26 00:00:00.000 Tuesday                        8
2                    2007-06-27 00:00:00.000 Wednesday                      8
2                    2007-07-02 00:00:00.000 Monday                         8
2                    2007-07-03 00:00:00.000 Tuesday                        8
2                    2007-07-05 00:00:00.000 Thursday                       8
2                    2007-07-06 00:00:00.000 Friday                         8
2                    2007-07-09 00:00:00.000 Monday                         8
2                    2007-07-10 00:00:00.000 Tuesday                        8
2                    2007-07-11 00:00:00.000 Wednesday                      8
2                    2007-07-12 00:00:00.000 Thursday                       8
2                    2007-07-13 00:00:00.000 Friday                         8
2                    2007-07-16 00:00:00.000 Monday                         8
2                    2007-07-17 00:00:00.000 Tuesday                        8
2                    2007-07-18 00:00:00.000 Wednesday                      8
2                    2007-07-19 00:00:00.000 Thursday                       8
2                    2007-07-20 00:00:00.000 Friday                         8
2                    2007-07-23 00:00:00.000 Monday                         8
2                    2007-07-24 00:00:00.000 Tuesday                        8
2                    2007-07-25 00:00:00.000 Wednesday                      8
2                    2007-07-26 00:00:00.000 Thursday                       8
2                    2007-07-27 00:00:00.000 Friday                         8
2                    2007-07-30 00:00:00.000 Monday                         8
2                    2007-07-31 00:00:00.000 Tuesday                        8
2                    2007-08-01 00:00:00.000 Wednesday                      8
2                    2007-08-02 00:00:00.000 Thursday                       8
2                    2007-08-03 00:00:00.000 Friday                         8
2                    2007-08-06 00:00:00.000 Monday                         8
2                    2007-08-07 00:00:00.000 Tuesday                        8
2                    2007-08-08 00:00:00.000 Wednesday                      8
2                    2007-08-09 00:00:00.000 Thursday                       8
2                    2007-08-10 00:00:00.000 Friday                         8
2                    2007-08-13 00:00:00.000 Monday                         8
2                    2007-08-14 00:00:00.000 Tuesday                        8
2                    2007-08-15 00:00:00.000 Wednesday                      8
2                    2007-08-16 00:00:00.000 Thursday                       8
2                    2007-08-17 00:00:00.000 Friday                         8
2                    2007-08-20 00:00:00.000 Monday                         8
2                    2007-08-21 00:00:00.000 Tuesday                        8
2                    2007-08-22 00:00:00.000 Wednesday                      8

I need result in this form:

ConfirmationId  weekstartdate   weekenddate Monday  Tuesday Wednesday Thursday Friday Saturday Sunday
    1           16/07/2007      23/07/2007  NULL    NULL    8         8        8      NULL     NULL
    1           24/07/2007      30/07/2007  8       8       8         8        8      NULL     NULL
    2           18/06/2007      24/06/2007  NULL    NULL    NULL      NULL     8      NULL     NULL
    2           25/06/2007      1/07/2007   8       8       8         NULL     NULL   NULL     NULL
    2           2/07/2007       8/07/2007   8       8       NULL      8        8      NULL     NULL

What i have tried so far: 

select Confirmationid,[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
from
(
Select confirmationid,WorkingHours,TimeSheetDate,[DayName] from #temp1
) d
pivot(
min(WorkingHours) for
[DayName] in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
)piv order by ConfirmationId

this give me result: which is totally wrong.

enter image description here

Upvotes: 1

Views: 69

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You should use a calendar table to get the first and last day of the week. Select all relevant fields in a cte and then pivot from there:

;WITH cte AS (SELECT    ConfirmationId
                      , DayName
                      , WorkingHours 
                      , b.FirstDateOfWeek
                      , b.LastDateOfWeek
               FROM  #temp1 a
               JOIN tlkp_Calendar b
                 ON a.TimeSheetDate = b.CalendarDate
              )
SELECT * 
FROM  cte
PIVOT(MIN(WorkingHours) 
      FOR [DayName] IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
     )pvt 
ORDER BY ConfirmationId

Upvotes: 1

Related Questions