query to find sales person who did sale every quarter

I have this query which gets only those sales people who made a sale
each month from Feb 03 - May 03. I need to change it to get sales
person who did sales for each quater. so instead of comparing for each
month quaterly comparison would be done. Here the range of data is from
Feb 03 - May 03 but can be changed to yearly.
So need to modify the query to return only sales_person_id who did sale
(have a sale_id) for every quarter in the year.
So only 7 should be returned.
CREATE TABLE #Sales(
sale_id int identity(1,1) not null,
sales_person_id int,
start datetime
)
go
--lets assume that your range is from Feb03 - May03
insert into #Sales(sales_person_id, start) values(12, '2/5/2003')
insert into #Sales(sales_person_id, start) values(12, '3/5/2003')
insert into #Sales(sales_person_id, start) values(12, '4/5/2003')
insert into #Sales(sales_person_id, start) values(12, '4/5/2003')
insert into #Sales(sales_person_id, start) values(12, '5/5/2003')
insert into #Sales(sales_person_id, start) values(1, '2/5/2003')
insert into #Sales(sales_person_id, start) values(2, '2/5/2004')
insert into #Sales(sales_person_id, start) values(4, '2/5/2003')
insert into #Sales(sales_person_id, start) values(4, '3/5/2003')
--missing april
insert into #Sales(sales_person_id, start) values(4, '5/5/2003')
insert into #Sales(sales_person_id, start) values(6, '2/5/2003')
insert into #Sales(sales_person_id, start) values(6, '3/5/2003')
insert into #Sales(sales_person_id, start) values(6, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '12/5/2003')
insert into #Sales(sales_person_id, start) values(7, '9/5/2003')
insert into #Sales(sales_person_id, start) values(7, '6/5/2003')
insert into #Sales(sales_person_id, start) values(7, '1/5/2003')
go
--generate the month table
CREATE TABLE #RefCalendarMonth(
YearText VARCHAR(4),
YearMonth VARCHAR(6),
Start_Date DATETIME,
End_Date DATETIME
)
go
DECLARE
@.StartDate DATETIME,
@.EndDate DATETIME
SET @.StartDate = CAST('2003-01-01' as DATETIME)
SET @.EndDate = CAST('2003-12-01' as DATETIME)
WHILE (@.StartDate <= @.EndDate)
BEGIN
INSERT INTO #RefCalendarMonth
SELECT
CAST(YEAR(@.StartDate) AS VARCHAR),
LEFT(CONVERT(VARCHAR, @.StartDate, 112),6),
@.StartDate,
DATEADD(Month,1,@.StartDate) - 1
SET @.StartDate = DATEADD(Month, 1, @.StartDate)
END
GO
DECLARE
@.RangeStartDate DATETIME,
@.RangeEndDate DATETIME
SET @.RangeStartDate = '2003-02-01'
SET @.RangeEndDate = '2003-05-31'
SELECT sales_person_id, COUNT(sale_id)
FROM #Sales
WHERE
sales_person_id IN (
SELECT sales_person_id
FROM
#Sales S JOIN #RefCalendarMonth R ON (
LEFT(CONVERT(VARCHAR, S.Start, 112),6) = R.YearMonth
)
WHERE
R.End_Date BETWEEN @.RangeStartDate AND @.RangeEndDate
GROUP BY sales_person_id, r.YearText
HAVING COUNT(DISTINCT r.yearmonth) = DATEDIFF(MONTH,
@.RangeStartDate, @.RangeEndDate) + 1
)
GROUP BY sales_person_id
GO
DROP TABLE #Sales
DROP TABLE #RefCalendarMonth
GOHi,
I think that the following will solve your problem. It will return only Sale
s_Person_ID's that had at least one sale each quarter.
SELECT Sales_Person_ID
FROM ( SELECT
Sales_Person_ID
, datepart( quarter, start) AS 'Quarter'
FROM #Sales
WHERE datepart( year, start ) = 2003
GROUP BY
datepart( quarter, start)
, Sales_Person_ID
) AS s
GROUP BY Sales_Person_ID
HAVING sum( s.Quarter ) = 10
A bit of explanition. The SELECT that creates the derived table 's' creates
a grouping of Sales_Person_ID by Quarter, and then the further grouping with
the HAVING looks for Sales_Person_ID's for the entire year. (Quarters 1 + 2
+3 + 4 = 10)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"VJ" <vishal.sql@.gmail.com> wrote in message news:1150335698.925324.229400@.c74g2000cwc.goog
legroups.com...
>I have this query which gets only those sales people who made a sale
> each month from Feb 03 - May 03. I need to change it to get sales
> person who did sales for each quater. so instead of comparing for each
> month quaterly comparison would be done. Here the range of data is from
> Feb 03 - May 03 but can be changed to yearly.
> So need to modify the query to return only sales_person_id who did sale
> (have a sale_id) for every quarter in the year.
> So only 7 should be returned.
>
>
> CREATE TABLE #Sales(
> sale_id int identity(1,1) not null,
> sales_person_id int,
> start datetime
> )
> go
>
> --lets assume that your range is from Feb03 - May03
> insert into #Sales(sales_person_id, start) values(12, '2/5/2003')
> insert into #Sales(sales_person_id, start) values(12, '3/5/2003')
> insert into #Sales(sales_person_id, start) values(12, '4/5/2003')
> insert into #Sales(sales_person_id, start) values(12, '4/5/2003')
> insert into #Sales(sales_person_id, start) values(12, '5/5/2003')
> insert into #Sales(sales_person_id, start) values(1, '2/5/2003')
> insert into #Sales(sales_person_id, start) values(2, '2/5/2004')
> insert into #Sales(sales_person_id, start) values(4, '2/5/2003')
> insert into #Sales(sales_person_id, start) values(4, '3/5/2003')
> --missing april
> insert into #Sales(sales_person_id, start) values(4, '5/5/2003')
> insert into #Sales(sales_person_id, start) values(6, '2/5/2003')
> insert into #Sales(sales_person_id, start) values(6, '3/5/2003')
> insert into #Sales(sales_person_id, start) values(6, '4/5/2003')
> insert into #Sales(sales_person_id, start) values(7, '12/5/2003')
> insert into #Sales(sales_person_id, start) values(7, '9/5/2003')
> insert into #Sales(sales_person_id, start) values(7, '6/5/2003')
> insert into #Sales(sales_person_id, start) values(7, '1/5/2003')
> go
>
> --generate the month table
> CREATE TABLE #RefCalendarMonth(
> YearText VARCHAR(4),
> YearMonth VARCHAR(6),
> Start_Date DATETIME,
> End_Date DATETIME
> )
> go
>
> DECLARE
> @.StartDate DATETIME,
> @.EndDate DATETIME
> SET @.StartDate = CAST('2003-01-01' as DATETIME)
> SET @.EndDate = CAST('2003-12-01' as DATETIME)
>
> WHILE (@.StartDate <= @.EndDate)
> BEGIN
> INSERT INTO #RefCalendarMonth
> SELECT
> CAST(YEAR(@.StartDate) AS VARCHAR),
> LEFT(CONVERT(VARCHAR, @.StartDate, 112),6),
> @.StartDate,
> DATEADD(Month,1,@.StartDate) - 1
>
> SET @.StartDate = DATEADD(Month, 1, @.StartDate)
> END
> GO
>
> DECLARE
> @.RangeStartDate DATETIME,
> @.RangeEndDate DATETIME
>
> SET @.RangeStartDate = '2003-02-01'
> SET @.RangeEndDate = '2003-05-31'
>
> SELECT sales_person_id, COUNT(sale_id)
> FROM #Sales
> WHERE
> sales_person_id IN (
> SELECT sales_person_id
> FROM
> #Sales S JOIN #RefCalendarMonth R ON (
> LEFT(CONVERT(VARCHAR, S.Start, 112),6) = R.YearMonth
> )
> WHERE
> R.End_Date BETWEEN @.RangeStartDate AND @.RangeEndDate
> GROUP BY sales_person_id, r.YearText
> HAVING COUNT(DISTINCT r.yearmonth) = DATEDIFF(MONTH,
> @.RangeStartDate, @.RangeEndDate) + 1
> )
> GROUP BY sales_person_id
> GO
>
> DROP TABLE #Sales
> DROP TABLE #RefCalendarMonth
> GO
>