Reputation: 41
Here is my issue. This is a tanning salon and we have recently added spa services. Clients use to tan just time in a day so counting distinct clients by employee was pretty easy. Now They tan/spa more then one time in a day and see different staff on any given day. I am trying to SUM the dinstinct client count by day into one number. Here is the query. I hope this make sense. Please forgive me as first time I have posted?
This query will produce the count of distinct client each day they come in and which employee tanned them. (Which is correct) I just need to sum this by employee. If I do not include the date part it selects distinct for the entire date range.
SELECT COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)
The above query produces this type of output (without the sum at bottom)
EmployeeUID day of tan # clients
383-E11132012143712J1U 1 52
383-E11132012143712J1U 2 80
383-E11132012143712J1U 3 68
383-E11132012143712J1U 5 58
383-E11132012143712J1U 6 78
383-E11132012143712J1U 7 65
383-E11132012143712J1U 9 85
383-E11132012143712J1U 10 64
383-E11132012143712J1U 11 65
383-E11132012143712J1U 13 55
383-E11132012143712J1U 14 55
383-E11132012143712J1U 16 76
383-E11132012143712J1U 17 65
383-E11132012143712J1U 18 50
383-E11132012143712J1U 20 55
383-E11132012143712J1U 21 56
383-E11132012143712J1U 23 47
383-E11132012143712J1U 24 79
383-E11132012143712J1U 25 59
383-E11132012143712J1U 27 55
383-E11132012143712J1U 28 54
383-E11132012143712J1U 30 62
total 1383
If the datepart is removed it looks at the entire 30 day period as one grouping and only returns distinct count of 656 for this employee.
I need it to return the sum of 1383.
sample data for above employee. the client id may use services more then 1 time in a davy but I only want to count one time.
Upvotes: 4
Views: 411
Reputation: 32693
If I understood you correctly, just wrap your query as a sub-query.
This will return one row with one total number.
SELECT
SUM(T.[# clients]) AS TotalClients
FROM
(
SELECT COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)
) AS T
If you want a SUM
per employee, just add another GROUP BY
. This will return one row per EmployeeUID
:
SELECT
T.EmployeeUID
,SUM(T.[# clients]) AS ClientsPerEmployee
FROM
(
SELECT COUNT(DISTINCT ClientUID) AS [# clients], DATEPART(day, DateOfTan) AS [day of tan], EmployeeUID
FROM History_TanHistory
WHERE DateOfTan > CONVERT(DATETIME, '2016-06-01 00:00:00', 102)
AND DateOfTan < CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
AND Deleted = 0 AND Borrow = 0 AND AddedBack = 0 AND CanceledTan = 0
GROUP BY EmployeeUID, DATEPART(day, DateOfTan)
) AS T
GROUP BY T.EmployeeUID
Upvotes: 1