reuse of promocode with none overlapping start end

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

reuse of promocode with none overlapping start end

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-22 : 14:30:19

Greetings

give the following sample data, how can I make it so that I can reuse the same promocode as long as it does not overlap with an existing promo (that has same Promocode) start & end dates?

Gracias


declare @corelist table(
        RowNum           int identity,
		[PromoCode] [varchar](255) NOT NULL,
		[StartDate] [datetime] NOT NULL,
		[EndDate] [datetime] NOT NULL
		)
insert into @corelist
SELECT 'SQLTeam Free Chocolate for all',
       '1/1/2012',
       '3/1/2012'
       		
      
select * from @corelist

declare @list2 table(
        RowNum           int identity,
		[PromoCode] [varchar](255) NOT NULL,
		[StartDate] [datetime] NOT NULL,
		[EndDate] [datetime] NOT NULL
		)

insert into @list2
SELECT 'SQLTeam Free Chocolate for all',
       '4/1/2012',
       '5/1/2012'
       
insert into @corelist
  select  t1.[PromoCode],
          t1.[StartDate],
          t1.[EndDate]
                       
  from @list2 t1
  left join @corelist t2 on t1.[PromoCode] = t2.[PromoCode]
 where 
		   (t1.[StartDate] not between t2.[StartDate] and t2.[EndDate] )
		   and 
		   (t1.[EndDate] not between t2.[StartDate] and t2.[EndDate] )

select * from @corelist		   

declare @list3 table(
        RowNum           int identity,
		[PromoCode] [varchar](255) NOT NULL,
		[StartDate] [datetime] NOT NULL,
		[EndDate] [datetime] NOT NULL
		)

insert into @list3
SELECT 'SQLTeam Free Chocolate for all',
       '6/1/2012',
       '7/1/2012'
       
insert into @corelist
  select  t1.[PromoCode],
          t1.[StartDate],
          t1.[EndDate]           
  from @list3 t1
  left join @corelist t2 on t1.[PromoCode] = t2.[PromoCode]
 where 
		   (t1.[StartDate] not between t2.[StartDate] and t2.[EndDate] )
		   and 
		   (t1.[EndDate] not between t2.[StartDate] and t2.[EndDate] )		

select * from @corelist

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:53:31

sorry can you explain what should smaple output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources