Amount of a separate invoice with a date (day)

Question

Here is my problem. This is a solarium, and we recently added spa services. Customers use tanning only once a day, so counting individual customers by employees is pretty straightforward. Now they tan / spa more than once a day and see different employees on any day. I try to use SUM so that the client counts the day for one number. Here is the request. Hope this makes sense. Please forgive me the first time I posted a message?

This request will count an individual client every day when they arrive, and which employee tans them. (That's right) I just need to summarize this with the employee. If I do not include the date part, it selects a separate 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 O (no amount below)
EmployeeUID day 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 deleted, it considers the entire 30-day period as one group and returns only the number 656 for this employee.

I need him to return the amount of 1383.

sample data for the above employee. a client identifier can use the service more than 1 time in a squeeze, but I want to count only once.

Answer 1

If you understand correctly, just wrap your request as a subquery.

This will return one row with one total.

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

SUM , GROUP BY. 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