How can I relate a "schedule" from one table to the dates in another?

[gjsaturday]

TB1
id m t w th f
1 0 1 0 1 0
2 1 0 1 0 1
3 0 2 0 0 2
4 2 0 2 0 2

Shipments made on days where <> 0, so id 1 gets shipments on Tuesdays and Thursdays, ids 2 & 4 on Mondays, Wednesdays, & Fridays, and id 3 on Tuesdays & Fridays

tb2
id date in out
1 03/21/2005 0 6
2 03/21/2005 10 5
3 03/21/2005 0 10
4 03/21/2005 30 2
1 03/22/2005 15 6
2 03/22/2005 0 3
3 03/22/2005 20 5
4 03/22/2005 0 8
1 03/23/2005 0 2
2 03/23/2005 12 2
3 03/23/2005 0 3
4 03/23/2005 50 4
1 03/24/2005 13 2
2 03/24/2005 0 2
3 03/24/2005 0 3
4 03/24/2005 0 4
1 03/25/2005 0 2
2 03/25/2005 12 2
3 03/25/2005 10 3
4 03/25/2005 50 4

So with these tables, I want to group the rows of tb2 by the "schedule" of tb1. In other words, for id 1, I want the sum of Tuesday's and Wednesday's values; and the sum of Thursday's, Friday's, and Monday's values, for id 2, I want the sum of Monday's and Tuesday's values; the sum of Wednesday's and Thursday's values; and then just Friday's Values, and so on and so forth.

 

[JohnDTampaBay]

First let's normalize TB1. We can use a view:

CREATE VIEW vNormalTB1 AS
 SELECT id, 1 AS Weekday,
       'Monday' AS DayOfWeek, m AS GetsShipment
 UNION ALL
 SELECT id, 2 AS Weekday, 
       'Tuesday' AS DayOfWeek, t AS GetsShipment
 UNION ALL
 SELECT id, 3 AS Weekday,
       'Wednesday' AS DayOfWeek, w AS GetsShipment
 UNION ALL
 SELECT id, 4 AS Weekday,
       'Thursday' AS DayOfWeek, th AS GetsShipment
 UNION ALL
 SELECT id, 5 AS Weekday,
       'Friday' AS DayOfWeek, f AS GetsShipment

Now let's join TB2 to the view to get a little closer to what you want:

SET DATEFIRST 1 --Ensures 1st day is Monday

SELECT v.id, v.Weekday, v.DayOfWeek, 
       v.GetsShipment, t.[date], t.in, t.out
FROM vNormalTB1 AS v
  INNER JOIN TB2 AS t
  ON v.id = t.id
  AND v.Weekday = Datepart(weekday, t.[date])

I'm still unclear as to how you want the IN and OUT values rolled up. Do you want a SUM for each column starting with each TB1 DayOfWeek <> 0 up to but not including the next TB1 DayOfWeek <> 0? So id 3 gets rolled up for Tuesday's, Wednesday's and Thursday's values, and Friday's and Monday's values?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[gjsaturday]

Thanks so much John!

Yes, that's how I want the ins and outs rolled up

so my results should look something like...

id     date      in   out
1     03/22/2005 15 8
1     03/24/2005 13 4
2     03/21/2005 10 8
2     03/23/2005 12 4
2     03/25/2005 12 2
3     03/22/2005 20 11
3     03/25/2005 10 3
4     03/21/2005 30 10
4     03/23/2005 50 8
4     03/25/2005 50 4


I'm trying to get utilization of the amount out based on when and what came in.

Thanks!