query to get quaterly sales for each sales person

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query to get quaterly sales for each sales person

Author

Topic

vishalj
Starting Member

32 Posts

Posted - 2006-06-14 : 21:26:13

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 04:57:22

I think this one looks nicer. Do you see how easy it is to change between different date intervals?
Month = mm
Quarter = qq
Week = ww

-- populate test data
declare	@sales table
	(
		sales_person_id int,
		start datetime
	)

insert @sales (sales_person_id, start)
select 12, '2/5/2003' union all
select 12, '3/5/2003' union all
select 12, '4/5/2003' union all
select 12, '4/5/2003' union all--double april
select 12, '5/5/2003' union all
select 1, '2/5/2003' union all
select 2, '2/5/2004' union all
select 4, '2/5/2003' union all
select 4, '3/5/2003' union all--missing april
select 4, '5/5/2003' union all
select 6, '2/5/2003' union all
select 6, '3/5/2003' union all
select 6, '4/5/2003' union all
select 7, '12/5/2003' union all
select 7, '9/5/2003' union all
select 7, '6/5/2003' union all
select 7, '1/5/2003'

declare	@rangestartdate datetime,
	@rangeenddate DATETIME

SET @RangeStartDate = '2003-02-01'
SET @RangeEndDate = '2003-05-31'

-- do the work for month
SELECT		Sales_Person_ID SalesPersonID,
		COUNT(*) Sales
FROM		@Sales
WHERE		Start BETWEEN @RangeStartDate AND @RangeEndDate
GROUP BY	Sales_Person_ID
HAVING		COUNT(DISTINCT 100 * YEAR(Start) + DATEPART(mm, Start)) > DATEDIFF(mm, @RangeStartDate, @RangeEndDate)

SalesPersonID	Sales
-------------	------
12		5


SET @RangeStartDate = '2003-01-01'
SET @RangeEndDate = '2003-12-31'

-- do the work for quarter
SELECT		Sales_Person_ID SalesPersonID,
		COUNT(*) Sales
FROM		@Sales
WHERE		Start BETWEEN @RangeStartDate AND @RangeEndDate
GROUP BY	Sales_Person_ID
HAVING		COUNT(DISTINCT 100 * YEAR(Start) + DATEPART(qq, Start)) > DATEDIFF(qq, @RangeStartDate, @RangeEndDate)

SalesPersonID	Sales
-------------	------
7		4


SET @RangeStartDate = '2003-02-04'
SET @RangeEndDate = '2003-02-06'

-- do the work for week
SELECT		Sales_Person_ID SalesPersonID,
		COUNT(*) Sales
FROM		@Sales
WHERE		Start BETWEEN @RangeStartDate AND @RangeEndDate
GROUP BY	Sales_Person_ID
HAVING		COUNT(DISTINCT 100 * YEAR(Start) + DATEPART(ww, Start)) > DATEDIFF(ww, @RangeStartDate, @RangeEndDate)

SalesPersonID	Sales
-------------	------
1		1
4		1
6		1
12		1

Peter Larsson
Helsingborg, Sweden

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-15 : 05:22:42

duplicate post (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67737)
vishalj, we hope you are learning something in SQL :).

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-15 : 05:37:20

quote:
100 * DATEPART(yyyy, Start) + DATEPART(mm, Start)

Nice, need to remember that one. Thanks :).

May the Almighty God bless us all!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 05:39:06

quote:
Originally posted by cmdr_skywalker

quote:
100 * DATEPART(yyyy, Start) + DATEPART(mm, Start)

Nice, need to remember that one. Thanks :).

You're welcome!

The reason I use 100 * is that it works for all date intervals. When using quarters, 10 * would be enough, but...

Peter Larsson
Helsingborg, Sweden

vishalj
Starting Member

32 Posts

Posted - 2006-06-15 : 10:25:00

for some reason this does not gives me the correct result.

I think in the distinct having there is some problem
as i get the total count of that person regardless of any where clauses,

Is there any way to do this by making a refcalender table and then doing the having clause. Like we are doing for the months case.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 11:41:32

quote:
Originally posted by vishalj

for some reason this does not gives me the correct result.

I think in the distinct having there is some problem
as i get the total count of that person regardless of any where clauses,

Is there any way to do this by making a refcalender table and then doing the having clause. Like we are doing for the months case.

Do you have som sample data like I provided in my example? Post sample data here and the expected outcome of that data.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 11:50:40

quote:
Originally posted by vishalj

I think in the distinct having there is some problem
as i get the total count of that person regardless of any where clauses

Most likely the rangestart and rangeend dates in not properly set.
Since there is a WHERE clause, the resultset is filtered down to nothing else but records where start date is between these dates.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources