September 9, 2009 at 10:40 am
Hi all,
I'm a stumped newbie, and hoping for some help. Here are my requirements:
- I need a list of distinct Sites that have a subscription that encompass a specific date range. Say the range is 7/1/08 to 6/31/09, and a Site's subscription has a StartDate of 5/30/08 and an EndDate of 8/29/09, they would be counted.
- I'll then use that list to get a count of all tests taken per Site. This includes Sites that have no tests taken in that date range, but do have a subscription that encompasses our date range.
I know I'm missing something obvious, but I'd appreciate if someone can hit me over the head with it. :hehe:
Here's some sample data (Sql 2005):
--Sample data
create table #subscriptions
(SiteNum int, Product char(2), StartDate datetime, EndDate datetime)
create table #tests
(SiteNum int, Product char(2), DateTaken datetime)
insert into #subscriptions
select 122, 'P1', '2007-05-01 00:00:00.000', '2008-05-31 23:59:59.000'
union all
select 123, 'P1', '2008-05-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 123, 'P1', '2008-07-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 124, 'P1', '2008-08-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 125, 'P1', '2007-12-31 00:00:00.000', '2008-12-27 23:59:59.000'
union all
select 125, 'P1', '2008-12-31 00:00:00.000', '2009-12-30 23:59:59.000'
union all
select 126, 'P1', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 126, 'P2', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 127, 'P1', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 127, 'P2', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
insert into #tests
select 123, 'P1', '2008-09-01 00:00:00.000'
union all
select 123, 'P1', '2008-07-11 00:00:00.000'
union all
select 124, 'P1', '2008-09-02 00:00:00.000'
union all
select 124, 'P1', '2008-12-17 00:00:00.000'
union all
select 125, 'P1', '2008-08-14 00:00:00.000'
union all
select 126, 'P1', '2009-06-20 00:00:00.000'
--My attempt
select
Sites = case when t.SiteNum is null then s.SiteNum else t.SiteNum end,
isnull(count(*), 0) as TestsTaken
from
/*
This is the where I'm trying to get distinct counts of sites that have a subscription that encompasses 7/1/08 to 6/31/09
I'm missing Site 126, and it also includes 122 and shouldn't...
*/
(
select distinct SiteNum from #subscriptions
where Product = 'P1' and (StartDate >= '2008-07-01 00:00:00.000' or EndDate <= '2009-06-30 23:59:59.000')
) s
left join #tests t
on t.SiteNum = s.SiteNum
where t.DateTaken >= '2008-07-01 00:00:00.000' and t.DateTaken <= '2009-06-30 23:59:59.000'
group by case when t.SiteNum is null then s.SiteNum else t.SiteNum end
--Expected Results
select 123 as Sites, 2 as TestsTaken
union all
select 124 as Sites, 2 as TestsTaken
union all
select 125 as Sites, 1 as TestsTaken
union all
select 126 as Sites, 1 as TestsTaken
union all
select 127 as Sites, 0 as TestsTaken
--Clean up
drop table #tests
drop table #subscriptions[/code]
Thanks for any help!
- Jeff
September 9, 2009 at 11:40 am
I am not sure the criteria is correct. If you want sites that span across a given date range then your operators are incorrect. This will get you the list that starts before your start date and ends after your end date.
--Sample data
create table #subscriptions
(SiteNum int, Product char(2), StartDate datetime, EndDate datetime)
create table #tests
(SiteNum int, Product char(2), DateTaken datetime)
insert into #subscriptions
select 122, 'P1', '2007-05-01 00:00:00.000', '2008-05-31 23:59:59.000'
union all
select 123, 'P1', '2008-05-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 123, 'P1', '2008-07-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 124, 'P1', '2008-08-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 125, 'P1', '2007-12-31 00:00:00.000', '2008-12-27 23:59:59.000'
union all
select 125, 'P1', '2008-12-31 00:00:00.000', '2009-12-30 23:59:59.000'
union all
select 126, 'P1', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 126, 'P2', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 127, 'P1', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 127, 'P2', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
insert into #tests
select 123, 'P1', '2008-09-01 00:00:00.000'
union all
select 123, 'P1', '2008-07-11 00:00:00.000'
union all
select 124, 'P1', '2008-09-02 00:00:00.000'
union all
select 124, 'P1', '2008-12-17 00:00:00.000'
union all
select 125, 'P1', '2008-08-14 00:00:00.000'
union all
select 126, 'P1', '2009-06-20 00:00:00.000'
SELECT s.siteNum, COUNT(t.SiteNum) FROM #subscriptions S
LEFT JOIN #tests T ON S.SiteNum = T.SiteNum AND S.Product = T.Product
WHERE startdate = '06/30/09'
GROUP BY s.SiteNum
drop table #tests
drop table #subscriptions
September 9, 2009 at 11:49 am
This should get you going.
The trick is to flip your criteria:
You want all records that start before your end date and end after your start date...
However, I couldn't see why record 127 is expected... It's not in the date range...
--Sample data
create table #subscriptions
(SiteNum int, Product char(2), StartDate datetime, EndDate datetime)
create table #tests
(SiteNum int, Product char(2), DateTaken datetime)
insert into #subscriptions
select 122, 'P1', '2007-05-01 00:00:00.000', '2008-05-31 23:59:59.000'
union all
select 123, 'P1', '2008-05-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 123, 'P1', '2008-07-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 124, 'P1', '2008-08-01 00:00:00.000', '2009-06-30 23:59:59.000'
union all
select 125, 'P1', '2007-12-31 00:00:00.000', '2008-12-27 23:59:59.000'
union all
select 125, 'P1', '2008-12-31 00:00:00.000', '2009-12-30 23:59:59.000'
union all
select 126, 'P1', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 126, 'P2', '2008-06-01 00:00:00.000', '2009-07-01 23:59:59.000'
union all
select 127, 'P1', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
union all
select 127, 'P2', '2008-06-01 00:00:00.000', '2008-06-30 23:59:59.000'
insert into #tests
select 123, 'P1', '2008-09-01 00:00:00.000'
union all
select 123, 'P1', '2008-07-11 00:00:00.000'
union all
select 124, 'P1', '2008-09-02 00:00:00.000'
union all
select 124, 'P1', '2008-12-17 00:00:00.000'
union all
select 125, 'P1', '2008-08-14 00:00:00.000'
union all
select 126, 'P1', '2009-06-20 00:00:00.000'
--My attempt
select
Sites = case when t.SiteNum is null then s.SiteNum else t.SiteNum end,
isnull(count(*), 0) as TestsTaken
from
/*
This is the where I'm trying to get distinct counts of sites that have a subscription that encompasses 7/1/08 to 6/31/09
I'm missing Site 126, and it also includes 122 and shouldn't...
*/
(
select distinct SiteNum from #subscriptions
where Product = 'P1' and (EndDate >= '2008-07-01 00:00:00.000' AND StartDate = '2008-07-01 00:00:00.000' and t.DateTaken <= '2009-06-30 23:59:59.000'
group by case when t.SiteNum is null then s.SiteNum else t.SiteNum end
--Expected Results
select 123 as Sites, 2 as TestsTaken
union all
select 124 as Sites, 2 as TestsTaken
union all
select 125 as Sites, 1 as TestsTaken
union all
select 126 as Sites, 1 as TestsTaken
union all
select 127 as Sites, 0 as TestsTaken
--Clean up
drop table #tests
drop table #subscriptions
September 9, 2009 at 12:18 pm
I had a typo for my sample data, I intended to include 127 in the result set.... sorry for the confusion there.
Thanks for the help guys, I got it now. Derp!
:laugh:
- Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply