query to get sales person and count of sales who d

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 sales person and count of sales who d

Author

Topic

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-13 : 22:07:31

I have this data table

sale_id sales_person_id start (date)

1 12 2/5/2003
2 12 3/5/2004
3 12 12/4/2005
4 14 2/3/2005
6 16 3/4/2006
11 89 3/5/2003

sale_id is identity column, each sales_person_id can have number of
sale_id

I need a query to get sales_person_id, count(sale_id) for all sales
person who did a sales for each and every month since 01/2003

I wrote this query but got errors as it returned data for other sales
persons also who did not do any sale in some month. the problem is with
my having clauase

SELECT sales_person_id, sum(MSAle)'TOTAL LOTS'
FROM (SELECT artist_id, count(sale_id) AS MSAle, month([start]) AS
[month] from SALES
WHERE Year([start])> = 2003
GROUP BY sales_person_id, month([start])
) AS A
GROUP BY sales_person_id
HAVING COUNT(sales_person_id) = (SELECT COUNT(DISTINCT
CAST(MONTH(start) AS VARCHAR(2)) + '_' + CAST(YEAR(start) AS
VARCHAR(2)))
FROM lot WHERE Year(start) >= 2003)

The problem is with the having clause,
as it should be able to get only sales_person_id who did sale in each
month

anyhelp on this????????

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-14 : 00:27:14

"I need a query to get sales_person_id, count(sale_id) for all sales
person who did a sales for each and every month since 01/2003."

You have two set of data, one is the list of person by month (ensuring they have at least one record each month) and two, the count of sales by person in data set one.

With that said,

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(7, '2/5/2003')
insert into #Sales(sales_person_id, start) values(7, '3/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/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

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-14 : 00:31:35

I use the Feb03 - May03 range since that a lot of record if i use 01/2003 to present. At anyrate, the first part to create the sample data (that is CREATE TABLE and INSERT stmts). The second part is to populate the Calendar Month table (CREATE TABLE and the LOOP). The third part is the actual query that you'll use to extract thd data. Set the range if you need to. Remember the @RangeEndDate is the last day of the month, not the first day of the month.

Hope this helps.

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-14 : 00:34:25

[code]
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(7, '2/5/2003')
insert into #Sales(sales_person_id, start) values(7, '3/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/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
[/code]

May the Almighty God bless us all!

vishalj
Starting Member

32 Posts

Posted - 2006-06-14 : 10:02:41

Thanks skywalker, I ran your query and I got this result

sales person id total sales
7 7
12 5

well according to my need these should not show up in the result as they did not do any sale in all the months from Jan to Dec
the person should have done sale each and every month to be in the result

vishalj
Starting Member

32 Posts

Posted - 2006-06-14 : 11:27:01

hey thanks, i got it. please ignore my previous email. I did not look at the range part. Anyways I have to run it for each year seperately else it does not give me results. don't know why.
Also, I am getting very few results.

So I need to change it to every quarter. I need sales person id for everyone who did sales in every quarter instead of each month. can you modify your query according to that.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-15 : 05:18:59

[qoute]Anyways I have to run it for each year seperately else it does not give me results. don't know why.[/quote]

You need to make sure you set the CalendarMonth range (before the WHILE LOOP). Modify the StarteDate variable to the earliest date you need. I already set the EndDate to the current date (or you can set it sometime in the future, perhaps 5 years from now). And the other condition is, it must be continuous, that means, no gap from your RangeStartDate and RangeEndDate. If you want by year, you have to call it with separate date range.

Here is the modified query for the month. I also added the year in case you will need it. In general, you only need to modify the JOIN clause condition and the HAVING clause condition if you want year, quarter, month.


--Author: Perez, JM
--Date: 6/15/2006
SET NOCOUNT ON

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(7, '2/5/2003')
insert into #Sales(sales_person_id, start) values(7, '3/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '4/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(7, '5/5/2003')
insert into #Sales(sales_person_id, start) values(9, '5/5/2003')
go

--generate the month table
CREATE TABLE #RefCalendarMonth(
  YearText    VARCHAR(4),
  QuarterText VARCHAR(2),
  QuarterNbr  INT,
  YearQuarterText VARCHAR(6),
  YearMonth   VARCHAR(6),
  Start_Date  DATETIME,
  End_Date    DATETIME
)
go

DECLARE
  @StartDate DATETIME,
  @EndDate DATETIME

--modify the calendar range to the earliest date you need
SET @StartDate = CAST('2003-01-01' as DATETIME)  
--SET @EndDate = CAST('2003-12-01' as DATETIME)
SET @EndDate = GETDATE()

WHILE (@StartDate <= @EndDate)
BEGIN
   INSERT INTO #RefCalendarMonth 
   SELECT 
      CAST(YEAR(@StartDate) AS VARCHAR),
      'Q' + CAST(DATEPART(quarter,@StartDate) AS VARCHAR),
      DATEPART(quarter,@StartDate),
      CAST(YEAR(@StartDate) AS VARCHAR) + 'Q' + CAST(DATEPART(quarter,@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 the date range, 
-- using by month, use first and last day of the month
-- using by quarter, use the first and last day withing the date range 
SET @RangeStartDate = '2003-01-01'
SET @RangeEndDate = '2003-06-30'   

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  --uncomment this for continuous month
           YEAR(S.Start) = Year(R.Start_Date)                  --use this for cont. quarter AND year
       AND DATEPART(QUARTER,S.Start) = R.QuarterNbr            --use this for cont. quarter
   )
   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  --use this for continuous month
   HAVING COUNT(DISTINCT r.YearQuarterText) = DATEDIFF(QUARTER,  @RangeStartDate, @RangeEndDate) + 1  --use this for continuous quarter
   --HAVING COUNT(DISTINCT r.YearText) = DATEDIFF(YEAR,  @RangeStartDate, @RangeEndDate) + 1  -- use this for year
 )
GROUP BY sales_person_id
GO

DROP TABLE #Sales
DROP TABLE #RefCalendarMonth 
GO

May the Almighty God bless us all!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 05:25:09

Duplicate post! Click link to see answer for topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67817

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources