Help with this join needed

Transact-SQL
1

I have this 2 tables that describe the timetable during the first 3 Weeks:

Weeks:

 WeekNr    Mon   Tue   Wed   Thu   Fri
  1         1     1     1     1     2
  2         1     1     1     1     2
  3         3     3     1     1     2  

Days:

Day    Name
 1      8 am - 17:00 pm
 2      8 am - 14:30 pm
 3      day off

I tried to join this tables in a way that it returns for all days from Monday to Friday all matches with the days - table:

Select Name
From Weeks
     left outer join Days on Days.Day in (Mon,Tue,Wed,Thu,Fri)

This join returns for the first Week-Record only 2 records (1,2). For each unique value in (Mon,Tue,Wed,Thu,Fri) only one single match is established.
How can I get all 5 matches per record?

2

You have to JOIN to the Days table for each Day in the Weeks table - so 5 joins will be needed if you want all the data in a single row for each week. If you want the data by each day - then you have to UNPIVOT the week (I prefer using cross apply to unpivot - but you can also use the unpivot operator):

Declare @weeksTable Table (WeekNr int, Mon int, Tue int, Wed int, Thu int, Fri int);
Declare @daysTable Table ([Day] int, [Name] varchar(30));

 Insert Into @weeksTable (WeekNr, Mon, Tue, Wed, Thu, Fri)
 Values (1, 1, 1, 1, 1, 2)
      , (2, 1, 1, 1, 1, 2)
      , (3, 3, 3, 1, 1, 2);

 Insert Into @daysTable ([Day], name)
 Values (1, '8 am - 17:00 pm')
      , (2, '8 am - 14:30 pm')
      , (3, 'day off');

 Select *
   From @weeksTable             wt
   Left Join @daysTable         d1 On d1.[Day] = wt.Mon
   Left Join @daysTable         d2 On d2.[Day] = wt.Tue
   Left Join @daysTable         d3 On d3.[Day] = wt.Wed
   Left Join @daysTable         d4 On d4.[Day] = wt.Thu
   Left Join @daysTable         d5 On d5.[Day] = wt.Fri;

 Select wt.WeekNr
      , wd.Weekday
      , dt.Name
   From @weeksTable             wt
  Cross Apply (Values ('Mon', wt.Mon)
                    , ('Tue', wt.Tue)
                    , ('Wed', wt.Wed)
                    , ('Thu', wt.Thu)
                    , ('Fri', wt.Fri)
               )                wd(Weekday, DayInWeek)
   Left Join @daysTable         dt On dt.[Day] = wd.DayInWeek;
1 Like
3

Wauuu... you did all my homework! Thank you! Great solution allthough I really hoped I could do without the 5 joins and was intrigued by the simplicity of my join, thinking it could do that magic. I still think somehow it should.
Great explanation - I actually never had heard of unpivoting a table, which makes absolute sense here. This is from the database that is fed by the Time Management System that our company bought and uses and that I query. After years I still don't get why they had decided to shape that table with a column for each weekday, which makes all operations so complicated and slow.

Martin

1 Like
4

Glad I could help - not sure about time management systems though.

5

After some operations on the now unpivoted columns, I need to write the data back to the Weeks table. This is what I do:

Declare @weeksTable Table (WeekNr int, Mon int, Tue int, Wed int, Thu int, Fri int);
Declare @UpdateData Table ([WeekNr] int, [Weekday] varchar(30), [day] int);

 Insert Into @weeksTable (WeekNr, Mon, Tue, Wed, Thu, Fri)
 Values (1, 1, 1, 1, 1, 2)
  , (2, 1, 1, 1, 1, 2)
  , (3, 3, 3, 1, 1, 2);

 Insert Into @UpdateData ([WeekNr], Weekday, day)
 Values (1, 'Mon', 4)
  , (1, 'Tue', 4)
  , (1, 'Wed', 4)
  , (1, 'Thu', 4)
  , (1, 'Fri', 4)
  , (2, 'Mon', 4)
  , (2, 'Tue', 4)
  , (2, 'Wed', 4)
  , (2, 'Thu', 4)
  , (2, 'Fri', 4)
  , (3, 'Mon', 4)
  , (3, 'Tue', 4)
  , (3, 'Wed', 4)
  , (3, 'Thu', 4)
  , (3, 'Fri', 4);

Update @weeksTable set Mon = CASE WHEN Weekday = 'Mon' THEN x.day else Mon END,
				   Tue = CASE WHEN Weekday = 'Tue' THEN x.day else Tue END,
				   Wed = CASE WHEN Weekday = 'Wed' THEN x.day else Wed END,
				   Thu = CASE WHEN Weekday = 'Thu' THEN x.day else Thu END,
				   Fri = CASE WHEN Weekday = 'Fri' THEN x.day else Fri END
from (Select WeekNr as WeekNumber, Weekday, day
  From @UpdateData)x
Where WeekNr = x.WeekNumber

select * from @weeksTable

Now, as you probably expected, only the Monday - Column of each record has been updated. Is there a way to write the data back in that described way without pivoting it again?

6

You have to pivot (cross-tab) the data to be able to update - or you have to join for each day, that is add a join for each day to be updated and then use the joined tables value in the update.

 Update wt
    Set wt.Mon = wd1.day
      , wt.Tue = wd2.day
      , wt.Wed = wd3.day
      , wt.Thu = wd4.day
      , wt.Fri = wd5.day
   From @weeksTable   wt
  Inner Join @UpdateData wd1 On wd1.WeekNr = wt.WeekNr And wd1.Weekday = 'Mon'
  Inner Join @UpdateData wd2 On wd2.WeekNr = wt.WeekNr And wd2.Weekday = 'Tue'
  Inner Join @UpdateData wd3 On wd3.WeekNr = wt.WeekNr And wd3.Weekday = 'Wed'
  Inner Join @UpdateData wd4 On wd4.WeekNr = wt.WeekNr And wd4.Weekday = 'Thu'
  Inner Join @UpdateData wd5 On wd5.WeekNr = wt.WeekNr And wd5.Weekday = 'Fri';

I would just cross-tab the data...

Declare @weeksTable Table (WeekNr int, Mon int, Tue int, Wed int, Thu int, Fri int);
Declare @UpdateData Table ([WeekNr] int, [Weekday] varchar(30), [day] int);

 Insert Into @weeksTable (WeekNr, Mon, Tue, Wed, Thu, Fri)
 Values (1, 1, 1, 1, 1, 2)
      , (2, 1, 1, 1, 1, 2)
      , (3, 3, 3, 1, 1, 2);

 Insert Into @UpdateData ([WeekNr], Weekday, day)
 Values (1, 'Mon', 4)
      , (1, 'Tue', 4)
      , (1, 'Wed', 4)
      , (1, 'Thu', 4)
      , (1, 'Fri', 4)
      , (2, 'Mon', 4)
      , (2, 'Tue', 4)
      , (2, 'Wed', 4)
      , (2, 'Thu', 4)
      , (2, 'Fri', 4)
      , (3, 'Mon', 4)
      , (3, 'Tue', 4)
      , (3, 'Wed', 4)
      , (3, 'Thu', 4)
      , (3, 'Fri', 4);

 Select *
   From @weeksTable;

   With updateData
     As (
 Select ud.WeekNr
      , Mon = max(Case When ud.Weekday = 'Mon' Then ud.[day] End)
      , Tue = max(Case When ud.Weekday = 'Tue' Then ud.[day] End)
      , Wed = max(Case When ud.Weekday = 'Wed' Then ud.[day] End)
      , Thu = max(Case When ud.Weekday = 'Thu' Then ud.[day] End)
      , Fri = max(Case When ud.Weekday = 'Fri' Then ud.[day] End)
   From @weeksTable                 wt
  Inner Join @UpdateData            ud On ud.WeekNr = wt.WeekNr
  Group By
        ud.WeekNr
        )
 Update wt
    Set wt.Mon = ud.Mon
      , wt.Tue = ud.Tue
      , wt.Wed = ud.Wed
      , wt.Thu = ud.Thu
      , wt.Fri = ud.Fri
   From @weeksTable                 wt
  Inner Join updateData             ud On ud.WeekNr = wt.WeekNr;

 Select *
   From @weeksTable;
1 Like
7

Thank you (again) Jeff. I will try it later allthough I now have to think of how to transfer the WHERE clause (I haven't talked about so far) to the pivoted expression. To select only the days of a year I do not have to consider Monday and Tuesday of the first week if the 1st of January is for example on a Wednesday. See the point? So this is what I do:

where ((WeekNr >= DATEPART(WEEK, CAST('1.1.2020' as datetime)) and wd.Weekday >= DATEPART(WEEKDAY, CAST('1.1.2020' as datetime))) or WeekNr > DATEPART(WEEK, CAST('1.1.2020' as datetime))) and
      ((WeekNr <= DATEPART(WEEK, CAST('31.12.2020' as datetime)) and wd.Weekday <= DATEPART(WEEKDAY, CAST('31.12.2020' as datetime))) or WeekNr < DATEPART(WEEK, CAST('31.12.2020' as datetime)))
8

I will solve this by bringing the table with the to be updated value in the same format as the Weekstable. That makes it easier to update; the where clause is applied inside the update condition:
set Mon = Case When ... THEN New_Value ELSE Mon END.

No further help required