SIMPLE OR COMPLEX QUERY
-
I have this data time
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 AGROUP 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)
if i replace my having with
count(distinct c1) = (datediff(month, '20030101' , getdate()) + 1)
I am not sure if it will work. 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????????
-
No need for a having clause as far as I can see. Try this
SELECT
sales_person_id, COUNT(sale_id) AS TotalSales, DATEADD(dd,-DAY(start)+1,start) AS SalesMonth FROM SALES
WHERE Start>'2003/01/01'
GROUP BY sales_person_id, DATEADD(dd,-DAY(start)+1,start)
If it's not right, please give example input and example output and I'll take another look.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply