Strange relationship query...

[gjsaturday]

I have two main tables...

TB1 (Days of week, Mon, Tue, Wed, Thu, Fri, Sat... datatype tinyint)
name m t w th f s
a 1 0 1 0 1 0
b 0 1 0 1 0 0
c 2 0 2 0 2 0
d 0 0 2 0 0 2
e 1 0 0 0 0 1


TB2
name date amt
a 02/16/2005 10
b 02/16/2005 9
c 02/16/2005 11
d 02/16/2005 12
e 02/16/2005 8

I join the tables by name, but I also need to join the tables based on any value in the days of week from TB1 <> 0 to the day of week of the date in TB2.

I have the following, which works...

select * from
tb2 where name+DATENAME(weekday,date) in
(select name+'Monday' from tb1 where m <> 0 union
select name+'Tuesday' from tb1 where t <> 0 union
select name+'Wednesday' from tb1 where w <> 0 union
select name+'Thursday' from tb1 where th <> 0 union
select name+'Friday' from tb1 where f <> 0 union
select name+'Saturday' from tb1 where s <> 0)

and I get...
a 02/16/2005 10
c 02/16/2005 11
d 02/16/2005 12

But there's got to be something better. I need to set my date range based on tb1 but don't know how to work backwards from what I have already.

Thanks in advance.

 

[donutman]

Is it to late to redesign your database?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gjsaturday]

Karl - those are my sentiments exactly! But unfortunately my hands are tied and I can't. And I am just a BSA playing programmer to boot.