Date Range Sample T-SQL


http://social.msdn.microsoft.com/Forums/en-US/1f1dacda-2ae8-4c9b-90a3-bfd7182f4364/select-from-one-table-based-on-condition-from-another-table-using-date-ranges-and-criteria?forum=transactsql


 CREATE TABLE ActivityDates  ( StartDate DATE,FinishDate DATE,Criteria INT,ActivityType INT)
CREATE TABLE Calendar (  CalendarDate  DATE)
 
INSERT ActivityDates (StartDate, FinishDate, Criteria , ActivityType)
VALUES
('20140101',       '20140102',           1, 20),
('20140102',       '20140103',          1,  20),
('20140103',       '20140106',          1, 30),
('20140106',       '20140107',          1, 10), --error in your original sample
('20140107',       '20140108',          1, 10),
('20140108',       '20140113',          0, 20)

insert Calendar (CalendarDate)
values
('20140101'),
('20140102'),
('20140103'),
('20140104'),
('20140105'),
('20140106'),
('20140107'),
('20140108'),
('20140109'),
('20140110'),
('20140111'),
('20140112'),
('20140113'),
('20140114')
GO
--select * from ActivityDates
--select * from Calendar 
 

 --SELECT   CalendarDate   FROM Calendar t1
 --  WHERE  EXISTS 
 --    (SELECT * FROM ActivityDates t2 
 --      WHERE t1.CalendarDate >=t2.StartDate AND t1.CalendarDate< t2.FinishDate  and Criteria=1)

 ;with mycte as ( 
SELECT CalendarDate,
       Criteria,
       ActivityType
FROM   Calendar t1
       CROSS apply (SELECT Criteria,
                           ActivityType
                    FROM   ActivityDates t2
                    WHERE  t1.CalendarDate >= t2.StartDate
                           AND t1.CalendarDate < t2.FinishDate
                           AND Criteria = 1) d (Criteria, ActivityType) 

   )


SELECT c.CalendarDate, m.ActivityType
FROM   Calendar c LEFT JOIN mycte m  
ON m.CalendarDate = c.CalendarDate 

drop table ActivityDates, Calendar

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s