Dennis Garrigan
Dennis Garrigan

Reputation: 41

Sum a Distinct Count with datepart(day)

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

Answers (1)

Vladimir Baranov
Vladimir Baranov

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

Related Questions