join on 3 tables for asp output

join on 3 tables for asp output

am 29.10.2004 17:05:05 von MikeD

I have three tables. The table structure and sample data is below(sorry for
the length). I want a select that will retrieve all Mondays since I am
scheduling instruments for a whole week. Every Instrument should have a row
for every Monday that way I can show that it hasn't been scheduled.

Thanks for any help you can provide

Mike



CREATE TABLE [dbo].[Tab_Inst_Schedules] (
[Instrument_ID] [int] NOT NULL ,
[WeekOf] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 10/25/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (1, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 10/25/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (2, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 10/25/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (3, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 10/25/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (4, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 10/25/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (5, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/01/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/08/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/15/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/22/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 11/29/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/06/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/13/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/20/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 12/27/2004)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/03/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/10/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/17/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/24/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 01/31/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/07/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/14/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/21/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 02/28/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/07/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/14/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/21/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 03/28/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/04/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/11/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/18/2005)
Insert into Tab_Inst_Schedules (Instrument_ID, WeekOf) Values (16, 04/25/2005)

CREATE TABLE [dbo].[Tab_Instrument_Details] (
[InstrumentID] [smallint] IDENTITY (1, 1) NOT NULL ,
[InstrumentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
GO

Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (1, 'SX01', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (2, 'SX02', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (3, 'SX03', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (4, 'SX04', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (5, 'SX05', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (6, 'SX06', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (7, 'SX07', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (8, 'SX08', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (9, 'SX09', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (10, 'SX10', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (11, 'SX11', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (12, 'SX12', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (13, 'SX13', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (14, 'SX14', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (15, 'SX15', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (16, 'MX01', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (17, 'MX02', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (18, 'MX03', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (19, 'MX04', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (20, 'MX05', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (21, 'MX05', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (22, 'MX06', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (23, 'MX07', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (24, 'MX08', TRUE)
Insert into Tab_Instrument_Details (InstrumentID, InstrumentName, Active)
Values (25, 'MX09', TRUE)

CREATE TABLE [dbo].[Tab_Lookup_Calendar] (
[dt] [smalldatetime] NOT NULL ,
[isWeekday] [bit] NULL ,
[Y] [smallint] NULL ,
[Q] [tinyint] NULL ,
[M] [tinyint] NULL ,
[D] [tinyint] NULL ,
[DW] [tinyint] NULL
) ON [PRIMARY]
GO

Sample data:
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/02/2004', FALSE, 2004, 4, 10, 2, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/03/2004', FALSE, 2004, 4, 10, 3, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/04/2004', TRUE, 2004, 4, 10, 4, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/05/2004', TRUE, 2004, 4, 10, 5, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/06/2004', TRUE, 2004, 4, 10, 6, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/07/2004', TRUE, 2004, 4, 10, 7, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/08/2004', TRUE, 2004, 4, 10, 8, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/09/2004', FALSE, 2004, 4, 10, 9, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/10/2004', FALSE, 2004, 4, 10, 10, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/11/2004', TRUE, 2004, 4, 10, 11, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/12/2004', TRUE, 2004, 4, 10, 12, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/13/2004', TRUE, 2004, 4, 10, 13, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/14/2004', TRUE, 2004, 4, 10, 14, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/15/2004', TRUE, 2004, 4, 10, 15, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/16/2004', FALSE, 2004, 4, 10, 16, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/17/2004', FALSE, 2004, 4, 10, 17, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/18/2004', TRUE, 2004, 4, 10, 18, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/19/2004', TRUE, 2004, 4, 10, 19, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/20/2004', TRUE, 2004, 4, 10, 20, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/21/2004', TRUE, 2004, 4, 10, 21, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/22/2004', TRUE, 2004, 4, 10, 22, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/23/2004', FALSE, 2004, 4, 10, 23, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/24/2004', FALSE, 2004, 4, 10, 24, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/25/2004', TRUE, 2004, 4, 10, 25, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/26/2004', TRUE, 2004, 4, 10, 26, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/27/2004', TRUE, 2004, 4, 10, 27, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/28/2004', TRUE, 2004, 4, 10, 28, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/29/2004', TRUE, 2004, 4, 10, 29, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/30/2004', FALSE, 2004, 4, 10, 30, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('10/31/2004', FALSE, 2004, 4, 10, 31, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/01/2004', TRUE, 2004, 4, 11, 1, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/02/2004', TRUE, 2004, 4, 11, 2, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/03/2004', TRUE, 2004, 4, 11, 3, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/04/2004', TRUE, 2004, 4, 11, 4, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/05/2004', TRUE, 2004, 4, 11, 5, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/06/2004', FALSE, 2004, 4, 11, 6, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/07/2004', FALSE, 2004, 4, 11, 7, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/08/2004', TRUE, 2004, 4, 11, 8, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/09/2004', TRUE, 2004, 4, 11, 9, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/10/2004', TRUE, 2004, 4, 11, 10, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/11/2004', TRUE, 2004, 4, 11, 11, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/12/2004', TRUE, 2004, 4, 11, 12, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/13/2004', FALSE, 2004, 4, 11, 13, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/14/2004', FALSE, 2004, 4, 11, 14, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/15/2004', TRUE, 2004, 4, 11, 15, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/16/2004', TRUE, 2004, 4, 11, 16, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/17/2004', TRUE, 2004, 4, 11, 17, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/18/2004', TRUE, 2004, 4, 11, 18, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/19/2004', TRUE, 2004, 4, 11, 19, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/20/2004', FALSE, 2004, 4, 11, 20, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/21/2004', FALSE, 2004, 4, 11, 21, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/22/2004', TRUE, 2004, 4, 11, 22, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/23/2004', TRUE, 2004, 4, 11, 23, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/24/2004', TRUE, 2004, 4, 11, 24, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/25/2004', TRUE, 2004, 4, 11, 25, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/26/2004', TRUE, 2004, 4, 11, 26, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/27/2004', FALSE, 2004, 4, 11, 27, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/28/2004', FALSE, 2004, 4, 11, 28, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/29/2004', TRUE, 2004, 4, 11, 29, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('11/30/2004', TRUE, 2004, 4, 11, 30, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/01/2004', TRUE, 2004, 4, 12, 1, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/02/2004', TRUE, 2004, 4, 12, 2, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/03/2004', TRUE, 2004, 4, 12, 3, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/04/2004', FALSE, 2004, 4, 12, 4, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/05/2004', FALSE, 2004, 4, 12, 5, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/06/2004', TRUE, 2004, 4, 12, 6, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/07/2004', TRUE, 2004, 4, 12, 7, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/08/2004', TRUE, 2004, 4, 12, 8, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/09/2004', TRUE, 2004, 4, 12, 9, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/10/2004', TRUE, 2004, 4, 12, 10, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/11/2004', FALSE, 2004, 4, 12, 11, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/12/2004', FALSE, 2004, 4, 12, 12, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/13/2004', TRUE, 2004, 4, 12, 13, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/14/2004', TRUE, 2004, 4, 12, 14, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/15/2004', TRUE, 2004, 4, 12, 15, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/16/2004', TRUE, 2004, 4, 12, 16, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/17/2004', TRUE, 2004, 4, 12, 17, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/18/2004', FALSE, 2004, 4, 12, 18, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/19/2004', FALSE, 2004, 4, 12, 19, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/20/2004', TRUE, 2004, 4, 12, 20, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/21/2004', TRUE, 2004, 4, 12, 21, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/22/2004', TRUE, 2004, 4, 12, 22, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/23/2004', TRUE, 2004, 4, 12, 23, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/24/2004', TRUE, 2004, 4, 12, 24, 6)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/25/2004', FALSE, 2004, 4, 12, 25, 7)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/26/2004', FALSE, 2004, 4, 12, 26, 1)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/27/2004', TRUE, 2004, 4, 12, 27, 2)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/28/2004', TRUE, 2004, 4, 12, 28, 3)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/29/2004', TRUE, 2004, 4, 12, 29, 4)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/30/2004', TRUE, 2004, 4, 12, 30, 5)
Insert into Tab_Lookup_Calendar (dt, isWeekday, Y, M, D, DW) Values
('12/31/2004', TRUE, 2004, 4, 12, 31, 6)

Re: join on 3 tables for asp output

am 29.10.2004 17:14:02 von reb01501

Mike D wrote:
> I have three tables. The table structure and sample data is
> below(sorry for the length). I want a select that will retrieve all
> Mondays since I am scheduling instruments for a whole week. Every
> Instrument should have a row for every Monday that way I can show
> that it hasn't been scheduled.
>
> Thanks for any help you can provide
>
> Mike
>
>
>
You've done a good job showing us what the data looks like. Now you need to
show us what the desired results look like. On the face of it, you will need
some outer joins (I'm assuming this is SQL Server, correct?)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: join on 3 tables for asp output

am 29.10.2004 17:50:04 von MikeD

Yes, this is SQL 2000. This is what I have been trying:
SELECT dbo.Tab_Lookup_Calendar.dt,
dbo.Tab_Instrument_Details.InstrumentName, dbo.Tab_Inst_Schedules.WeekOf
FROM dbo.Tab_Instrument_Details INNER JOIN
dbo.Tab_Inst_Schedules ON
dbo.Tab_Instrument_Details.InstrumentID =
dbo.Tab_Inst_Schedules.Instrument_ID RIGHT OUTER JOIN
dbo.Tab_Lookup_Calendar ON
dbo.Tab_Inst_Schedules.WeekOf = dbo.Tab_Lookup_Calendar.dt
WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
(dbo.Tab_Lookup_Calendar.DW = 2) AND
(dbo.Tab_Lookup_Calendar.dt <
DATEADD(dbo.Tab_Lookup_Calendar.M, 6, GETDATE()))


I want the data to look like this:

dt instname Weekof
10/25/2004 SX01 10/25/2004
11/1/2004 SX01 11/1/2004
11/8/2004 SX01 11/8/2004
11/15/2004 SX01 11/15/2004
10/25/2004 SX02 Null
11/1/2004 SX02 11/1/2004
11/8/2004 SX02 Null
11/15/2004 SX02 11/15/2004

I want an entry for every instrument on every Monday. If an instrument is
not scheduled I want to see a Null or handle it with an IsNull so that I can
show that the instrument is not scheduled for that week.

Here is a page with the table I want to populate.
http://home.comcast.net/~drewerym/3000_Schedule.htm

Thanks
Mike








"Bob Barrows [MVP]" wrote:

> Mike D wrote:
> > I have three tables. The table structure and sample data is
> > below(sorry for the length). I want a select that will retrieve all
> > Mondays since I am scheduling instruments for a whole week. Every
> > Instrument should have a row for every Monday that way I can show
> > that it hasn't been scheduled.
> >
> > Thanks for any help you can provide
> >
> > Mike
> >
> >
> >
> You've done a good job showing us what the data looks like. Now you need to
> show us what the desired results look like. On the face of it, you will need
> some outer joins (I'm assuming this is SQL Server, correct?)
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: join on 3 tables for asp output

am 29.10.2004 19:55:04 von MikeD

What am I missing? Shouldn't this return all rows from Tab_Lookup_Calendar
that are a Monday? Should be more than 11 rows? Could there be somthing
wrong with my SQL server?

SELECT dbo.Tab_Lookup_Calendar.dt, dbo.Tab_Inst_Schedules.Instrument_ID
FROM dbo.Tab_Lookup_Calendar LEFT OUTER JOIN
dbo.Tab_Inst_Schedules ON dbo.Tab_Lookup_Calendar.dt =
dbo.Tab_Inst_Schedules.WeekOf
WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
(dbo.Tab_Lookup_Calendar.DW = 2) AND
(dbo.Tab_Lookup_Calendar.dt <
DATEADD(dbo.Tab_Lookup_Calendar.M, 12, GETDATE())) AND
(dbo.Tab_Inst_Schedules.Instrument_ID = 1)
ORDER BY dbo.Tab_Lookup_Calendar.dt








"Bob Barrows [MVP]" wrote:

> Mike D wrote:
> > I have three tables. The table structure and sample data is
> > below(sorry for the length). I want a select that will retrieve all
> > Mondays since I am scheduling instruments for a whole week. Every
> > Instrument should have a row for every Monday that way I can show
> > that it hasn't been scheduled.
> >
> > Thanks for any help you can provide
> >
> > Mike
> >
> >
> >
> You've done a good job showing us what the data looks like. Now you need to
> show us what the desired results look like. On the face of it, you will need
> some outer joins (I'm assuming this is SQL Server, correct?)
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: join on 3 tables for asp output

am 29.10.2004 20:14:44 von reb01501

Mike D wrote:
> Yes, this is SQL 2000. This is what I have been trying:
> SELECT dbo.Tab_Lookup_Calendar.dt,
> dbo.Tab_Instrument_Details.InstrumentName,
> dbo.Tab_Inst_Schedules.WeekOf FROM dbo.Tab_Instrument_Details
> INNER JOIN dbo.Tab_Inst_Schedules ON
> dbo.Tab_Instrument_Details.InstrumentID =
> dbo.Tab_Inst_Schedules.Instrument_ID RIGHT OUTER JOIN
> dbo.Tab_Lookup_Calendar ON
> dbo.Tab_Inst_Schedules.WeekOf = dbo.Tab_Lookup_Calendar.dt
> WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
> (dbo.Tab_Lookup_Calendar.DW = 2) AND
> (dbo.Tab_Lookup_Calendar.dt <
> DATEADD(dbo.Tab_Lookup_Calendar.M, 6, GETDATE()))
>
>
> I want the data to look like this:
>
> dt instname Weekof
> 10/25/2004 SX01 10/25/2004
> 11/1/2004 SX01 11/1/2004
> 11/8/2004 SX01 11/8/2004
> 11/15/2004 SX01 11/15/2004
> 10/25/2004 SX02 Null
> 11/1/2004 SX02 11/1/2004
> 11/8/2004 SX02 Null
> 11/15/2004 SX02 11/15/2004
>
> I want an entry for every instrument on every Monday. If an
> instrument is not scheduled I want to see a Null or handle it with an
> IsNull so that I can show that the instrument is not scheduled for
> that week.
>
> Here is a page with the table I want to populate.
> http://home.comcast.net/~drewerym/3000_Schedule.htm
>
> Thanks
> Mike
>
>
>
>
OK, this query (I've used table aliases to make it more readable), gives you
a cross join between details and calendar:

SELECT c.dt, i.InstrumentID, i.InstrumentName
FROM dbo.Tab_Lookup_Calendar c
CROSS JOIN dbo.Tab_Instrument_Details i
WHERE (c.isWeekday = 1) AND
(c.DW = 2) AND
(c.dt < DATEADD(m, 6, GETDATE()))
ORDER BY i.InstrumentName,c.dt

Now it's simply a matter of joining the results of this query to schedule,
using an outer join to give you the results you want:

SELECT dt, InstrumentName, WeekOf
FROM Tab_Inst_Schedules s RIGHT JOIN
(SELECT c.dt, i.InstrumentID,i.InstrumentName
FROM dbo.Tab_Lookup_Calendar c
CROSS JOIN dbo.Tab_Instrument_Details i
WHERE (c.isWeekday = 1) AND
(c.DW = 2) AND
(c.dt < DATEADD(m, 6, GETDATE()))) q
ON q.dt=s.WeekOf AND q.InstrumentID = s.Instrument_ID
ORDER BY q.InstrumentName,q.dt

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: join on 3 tables for asp output

am 29.10.2004 20:27:01 von MikeD

I have been fighting this query for days and I couldn't get it. Thanks so
much for you help. I'd say I owe you one but if I recall it is more than one!

Thanks
Mike

"Bob Barrows [MVP]" wrote:

> Mike D wrote:
> > Yes, this is SQL 2000. This is what I have been trying:
> > SELECT dbo.Tab_Lookup_Calendar.dt,
> > dbo.Tab_Instrument_Details.InstrumentName,
> > dbo.Tab_Inst_Schedules.WeekOf FROM dbo.Tab_Instrument_Details
> > INNER JOIN dbo.Tab_Inst_Schedules ON
> > dbo.Tab_Instrument_Details.InstrumentID =
> > dbo.Tab_Inst_Schedules.Instrument_ID RIGHT OUTER JOIN
> > dbo.Tab_Lookup_Calendar ON
> > dbo.Tab_Inst_Schedules.WeekOf = dbo.Tab_Lookup_Calendar.dt
> > WHERE (dbo.Tab_Lookup_Calendar.isWeekday = 1) AND
> > (dbo.Tab_Lookup_Calendar.DW = 2) AND
> > (dbo.Tab_Lookup_Calendar.dt <
> > DATEADD(dbo.Tab_Lookup_Calendar.M, 6, GETDATE()))
> >
> >
> > I want the data to look like this:
> >
> > dt instname Weekof
> > 10/25/2004 SX01 10/25/2004
> > 11/1/2004 SX01 11/1/2004
> > 11/8/2004 SX01 11/8/2004
> > 11/15/2004 SX01 11/15/2004
> > 10/25/2004 SX02 Null
> > 11/1/2004 SX02 11/1/2004
> > 11/8/2004 SX02 Null
> > 11/15/2004 SX02 11/15/2004
> >
> > I want an entry for every instrument on every Monday. If an
> > instrument is not scheduled I want to see a Null or handle it with an
> > IsNull so that I can show that the instrument is not scheduled for
> > that week.
> >
> > Here is a page with the table I want to populate.
> > http://home.comcast.net/~drewerym/3000_Schedule.htm
> >
> > Thanks
> > Mike
> >
> >
> >
> >
> OK, this query (I've used table aliases to make it more readable), gives you
> a cross join between details and calendar:
>
> SELECT c.dt, i.InstrumentID, i.InstrumentName
> FROM dbo.Tab_Lookup_Calendar c
> CROSS JOIN dbo.Tab_Instrument_Details i
> WHERE (c.isWeekday = 1) AND
> (c.DW = 2) AND
> (c.dt < DATEADD(m, 6, GETDATE()))
> ORDER BY i.InstrumentName,c.dt
>
> Now it's simply a matter of joining the results of this query to schedule,
> using an outer join to give you the results you want:
>
> SELECT dt, InstrumentName, WeekOf
> FROM Tab_Inst_Schedules s RIGHT JOIN
> (SELECT c.dt, i.InstrumentID,i.InstrumentName
> FROM dbo.Tab_Lookup_Calendar c
> CROSS JOIN dbo.Tab_Instrument_Details i
> WHERE (c.isWeekday = 1) AND
> (c.DW = 2) AND
> (c.dt < DATEADD(m, 6, GETDATE()))) q
> ON q.dt=s.WeekOf AND q.InstrumentID = s.Instrument_ID
> ORDER BY q.InstrumentName,q.dt
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>