Count distinct help - date related

[mossbs]

Hi guys,

have this procedure set up that produces a count, by customer of requests made.

What i need to do is now factor in another table and make sure that the product_id is only being counted once per day, so if the same dealer makes a requst for info on the same product more than once in one day, then it only counts it as 1.

i.e
same product_id + same day = 1
same product_id + different day = 2 or more
same product_id + different dealer = 2 or more

etc...

what i have so far is as follows (this just counts the Total requests and the total 'getProductDetails', it is the 'getProductDetails' count that needs to change)

SELECT
	O.oltpId AS Id_Dealer,

	COUNT(*) As TotalRequests,
	sum(CASE WHEN L.message = 'getProductDetails' THEN 1 ELSE 0 END) AS TotalGetProduct
	

FROM DLog DL

INNER JOIN Organisation O
ON DL.dealer_id = O.oid

WHERE	
L.logTime >= @start
AND	L.logTime < @end
AND	O.orgType = 0

GROUP BY
	O.oltpId

The @start and @end can obviously change - so its not as simple looking for one instance of the Product_Id between those dates (as these dates are set to be weekly, monthly etc), so it needs to per day between those dates.

The table i need to join on is Product -

(Product.oltpId = DLog.product_group_id)

..then in the Product table there is a column 'Product_Id' this is what i need to make sure is only ONCE per day etc.


I think the above makes sense...

Any help greatly appreciated - any questions just ask.

Cheers guys.

 

[gmmastros]

I assume it's the L.LogTime column that represents the day. I also assume this column also stores a time component. As such, I would recommend you add (to your select clause) something like...

[tt]
Count(Distinct DateDiff(Day, '19000101', L.LogTime)) As TheCountYouWant
[/tt]

This may not be exactly what your looking for based on your actual query and the data in the tables involved.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Add the date column to your select and group by it. If the Count(ProductID) is more than 1 when you grouped by date, it means this is bad product (appeared more than once per day).

PluralSight Learning Library

 

[sparkbyte]

Not sure if this will help. I was working on something simular and started down this road.

With cte1 (Id_Dealer, TotalRequests, CountGroupID, TotalGetProduct)
	As (
SELECT
    O.oltpId AS Id_Dealer,
    COUNT(*) As TotalRequests,
	Count(*) Over (Partition By DL.product_group_id) As [CountGroupID],
    sum(CASE WHEN L.message = 'getProductDetails' THEN 1 ELSE 0 END) AS TotalGetProduct
FROM DLog As DL
	INNER JOIN Organisation As O ON DL.dealer_id = O.oid
WHERE    
	(L.logTime BETWEEN DATEADD(dd, DATEDIFF(dd, 0, @start), 0) 
	AND DATEADD(ss, - 1, DATEADD(dd, DATEDIFF(dd, 0, @end), 1))) 
	AND    O.orgType = 0
GROUP BY O.oltpId)

Select 
	Id_Dealer, 
	TotalRequests, 
	CountGroupID, 
	TotalGetProduct
From cte1
Where CountGroupID = 1

I went a step further with mine and used Rollup to show totals.

thread183-1638406

This is a thread I have started, but it has my complete query that you can referrence.

Hope this helps.

Thanks

John Fuhrman