----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: Madhav_S9 Message 2 in Discussion Here is the solution I have included script to create tables, populate data and then the solution script. I hope this is needful. --Script to create tables if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_BKNG_MST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TB_BKNG_MST] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_BKNG_TXN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TB_BKNG_TXN] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_ROOM_MST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TB_ROOM_MST] GO CREATE TABLE [dbo].[TB_BKNG_MST] ( [bkg_id] [int] NULL , [bkg_from] [datetime] NULL , [bkg_to] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TB_BKNG_TXN] ( [bkg_txn_id] [int] NULL , [bkng_id] [int] NULL , [bkg_from] [datetime] NULL , [bkg_to] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[TB_ROOM_MST] ( [rm_id] [int] NOT NULL , [rm_name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO --Script to pouplate Data INSERT INTO TB_ROOM_MST Values (1,'ROOM1') INSERT INTO TB_ROOM_MST Values (2,'ROOM2') INSERT INTO TB_ROOM_MST Values (3,'ROOM3') INSERT INTO TB_BKNG_TXN Values (1,1,'Dec 1 2004 10:00AM','Dec 1 2004 2:00PM') INSERT INTO TB_BKNG_TXN Values (2,1,'Dec 2 2004 10:00AM','Dec 2 2004 2:00PM') INSERT INTO TB_BKNG_TXN Values (3,1,'Dec 3 2004 10:00AM','Dec 3 2004 2:00PM') INSERT INTO TB_BKNG_TXN Values (4,1,'Dec 4 2004 10:00AM','Dec 4 2004 2:00PM') INSERT INTO TB_BKNG_MST Values (1,'Dec 1 2004 10:00AM','Dec 4 2004 2:00PM') --Solution Script Declare @StartDate datetime, @EndDate datetime Set @Startdate = '11/29/2004 10:00' Set @EndDate = '12/06/2004 17:00' Set @StartDate = Left(@StartDate,11) Set @EndDate = Left(@EndDate,11) Declare @Date Table (from_date datetime, to_date datetime) While @StartDate <= @EndDate BEGIN Insert INTO @Date Values(@StartDate, DateAdd(s,-1,DateAdd(d,1, @StartDate))) Set @StartDate = DateAdd(d,1,@StartDate) END Select rm_name, rm_id, from_date, isnull(bkg_from,to_date) FROM @Date CROSS JOIN TB_ROOM_MST as RM LEFT OUTER JOIN TB_BKNG_TXN as BT ON BT.bkng_id = rm_id AND bkg_from BETWEEN from_date AND to_date AND bkg_to BETWEEN from_date AND to_date UNION Select rm_name, rm_id, isnull(bkg_to,from_date), to_date FROM @Date CROSS JOIN TB_ROOM_MST as RM INNER JOIN TB_BKNG_TXN as BT ON BT.bkng_id = rm_id AND bkg_from BETWEEN from_date AND to_date AND bkg_to BETWEEN from_date AND to_date Thanks & Regards MadhavRao ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDOTNET/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
