CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
ByRequest
ByRequest

Reputation: 311

PostgreSQL Three Columns With Counts Per Unique Date Ranges

I need a query to return a count of distinct/unique employee Ids who clocked in per month in each department.

So, I need a list of distinct employee departments returned in one column, then a count of how many unique employee IDs clocked in this month in a second column, how many unique employees clocked in last month in a third column, then how many clocked in the month before in a fourth column.

The end result should look something like this:

| DeptID | ThisMonth | LastMonth | MonthBefore |
------------------------------------------------
1001, 2, 3, 2  
1002, 4, 5, 2  
1003, 5, 7, 3

There are tens of thousands of timestamps, so for performance, I need to limit the overall data I am bringing back then try to total up everything in that window. Additionally, these results will eventually need to be fed into another temporary table.

I have tried variations of the following query so far:

SELECT
DISTINCT P.DepartmentID AS "DeptID",
--COUNT(DISTINCT CI.EmployeeID),
(SELECT COUNT(DISTINCT CI.EmployeeID) WHERE (ShiftStart >= 
(DATE_TRUNC('month',NOW())) AND ShiftEnd <= (DATE_TRUNC('month',NOW()) + '1 month'::INTERVAL - '1 day'::INTERVAL))),
(SELECT COUNT(DISTINCT CI.EmployeeID) WHERE (ShiftStart >= (DATE_TRUNC('month',NOW()) - '1 month'::INTERVAL) AND ShiftEnd <= (DATE_TRUNC('month',NOW()) - '1 day'::INTERVAL))),
(SELECT COUNT(DISTINCT CI.EmployeeID) WHERE (ShiftStart >= (DATE_TRUNC('month',NOW()) - '2 month'::INTERVAL) AND ShiftEnd <= (DATE_TRUNC('month',NOW()) - '1 month'::INTERVAL - '1 day'::INTERVAL)))
FROM TimeClock CI
INNER JOIN dbo.Personnel P ON CI.EmployeeID = P.RecordID
WHERE ShiftStart >= (DATE_TRUNC('month',NOW()) - '2 month'::INTERVAL)
AND ShiftEnd <= (DATE_TRUNC('month',NOW()) + '1 month'::INTERVAL - '1 day'::INTERVAL)
GROUP BY P.DepartmentID, CI.ShiftStart, CI.ShiftEnd--, CI.EmployeeID

The issue/problem I am having is the departmentids are duplicating - most likely because of the start and end timestamps of the shifts being in the GROUP. I would prefer not to break this out into multiple queries if possible.

Upvotes: 0

Views: 50

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Sounds like conditional aggregation could help you. I honestly don't get your schema from the query, so I can only post you a template, how this looks in general. You'll have to figure out the rest.

SELECT <departmentid>,
       count(DISTINCT
             CASE
               WHEN <shift is this month> THEN
                 <userid>
             END),
       count(DISTINCT
             CASE
               WHEN <shift is last month> THEN
                 <userid>
             END),
       count(DISTINCT
             CASE
               WHEN <shift is from two months ago> THEN
                 <userid>
             END)
       FROM <from clause maybe with joins>
       WHERE <shifts are from now to two months ago>
       GROUP BY <departmentid>;

(All the stuff in <> are meant as placeholders.)

Upvotes: 1

Related Questions