Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

query help with count

Status
Not open for further replies.

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

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
 
Sorry forgot to post what the recordset should looklike

here it is

Code:
monitor         quarter      count
john moyares    1Q 2005      4
john moyares    2Q 2005      2
lori kumar      1Q 2006      3
lori kumar      1Q 2007      2
lori kumar      2Q 2007      1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top