washaw
Programmer
- Feb 13, 2008
- 48
I have a table, the table contains a data of a Site and a monitor which monitors a site,
A monitor picks certain sites quarterly to do monitoring, for instance
monitor k picks site1, site2, site3 to do monitoring in the first quarter of the year 2008
then for the second quarter he picks some new sites say site4 and site5 and can also pick some site which he monitored in the previous quarters asy site1.
My problem is I want to get how many new sites did the monitor do monitoring for that quarter.
for example monitor monitored site s1, s2, s3 in 1Q2008
and site s3, s4 in 2Q2008
the count shoul look like
1Q2008 3
2Q2008 1
because he monitored 3 new sites in 1Q2008, but in 2Q2008 he picked one new and one already reviewed site s3
we only count the new ones
hope I didn't confuse you
here is the table structure
THanks
A monitor picks certain sites quarterly to do monitoring, for instance
monitor k picks site1, site2, site3 to do monitoring in the first quarter of the year 2008
then for the second quarter he picks some new sites say site4 and site5 and can also pick some site which he monitored in the previous quarters asy site1.
My problem is I want to get how many new sites did the monitor do monitoring for that quarter.
for example monitor monitored site s1, s2, s3 in 1Q2008
and site s3, s4 in 2Q2008
the count shoul look like
1Q2008 3
2Q2008 1
because he monitored 3 new sites in 1Q2008, but in 2Q2008 he picked one new and one already reviewed site s3
we only count the new ones
hope I didn't confuse you
here is the table structure
Code:
create table #siteMonitor (monitor varchar(100), quarter varchar(100), site varchar(100))
insert into #siteMonitor (monitor, quarter, site)
select '1Q 2005', 'john moyes', 's0001'
union all select '1Q 2005', 'john moyes', 's0002'
union all select '1Q 2005', 'john moyes', 's0003'
union all select '1Q 2005', 'john moyes', 's0004'
union all select '2Q 2005', 'john moyes', 's005'
union all select '2Q 2005', 'john moyes', 's003'
union all select '2Q 2005', 'john moyes', 's006'
union all select '1Q 2006', 'lori kumar', 's0007'
union all select '1Q 2006', 'lori kumar', 's0008'
union all select '1Q 2006', 'lori kumar', 's0009'
union all select '1Q 2007', 'lori kumar', 's007'
union all select '1Q 2007', 'lori kumar', 's0010'
union all select '1Q 2007', 'lori kumar', 's0011'
union all select '2Q 2007', 'lori kumar', 's007'
union all select '2Q 2007', 'lori kumar', 's0012'
THanks