How to get distinct count of records from table having a column with total value is 0

Question

Need to get the total count of patients for a given date where all FilledBy value is 0

My Script

CREATE TABLE [dbo].[da.CheckListActivity](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PatientId] [int] NULL,
    [ChecklistId] [int] NULL,
    [FilledBy] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[da.Patient](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PatientId] [int] NULL,
    [PatientName] [varchar](200) NULL,
    [DischargeDate] [Datetime]
) ON [PRIMARY]
GO

INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (101, 1002, 35)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (101, 1003, 21)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (101, 1004, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (102, 1002, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (102, 1003, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (102, 1004, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (103, 1002, 21)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (103, 1003, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (103, 1004, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (104, 1002, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (104, 1003, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (104, 1004, 0)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (105, 1002, 21)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (105, 1003, 27)
INSERT [dbo].[da.CheckListActivity] ([PatientId], [ChecklistId], [FilledBy]) VALUES (105, 1004, 35)

INSERT [dbo].[da.Patient] ([PatientId], [PatientName], [DischargeDate]) VALUES (101, N'Patient 1', '2021-05-03 03:22:06.650')
INSERT [dbo].[da.Patient] ([PatientId], [PatientName], [DischargeDate]) VALUES (102, N'Patient 2', '2021-05-02 06:13:18.430')
INSERT [dbo].[da.Patient] ([PatientId], [PatientName], [DischargeDate]) VALUES (103, N'Patient 3', '2021-05-02 03:22:06.650')
INSERT [dbo].[da.Patient] ([PatientId], [PatientName], [DischargeDate]) VALUES (104, N'Patient 4', '2021-05-02 06:13:18.430')
INSERT [dbo].[da.Patient] ([PatientId], [PatientName], [DischargeDate]) VALUES (105, N'Patient 5', '2021-05-03 02:36:33.970')

Attempt Query

SELECT COUNT(PatientId)PatientCnt  FROM [dbo].[da.CheckListActivity]
            WHERE [PatientId] IN (
                SELECT PatientId FROM [dbo].[da.Patient] 
                WHERE CONVERT(Date,DischargeDate) = CONVERT(Date,'2021-05-02'))
            AND FilledBy = 0 GROUP BY [PatientId]

Expected Output: PatientCnt = 2 (Which are 102, 104)

user_185647 · Answer

You can GROUP BY PatientId and check for both min(FilledBy) and max(FilledBy) equal to zero for the condition all FilledBy value is 0

select  p.PatientId
from    [da.Patient] p
        inner join [da.CheckListActivity] cla   on  p.PatientId = cla.PatientId
where   p.DischargeDate >= '2021-05-02'
and     p.DischargeDate <  '2021-05-03'
group by p.PatientId
having  min(FilledBy)   = 0
and     max(FilledBy)   = 0

To get the required count, use CTE or derived table

select count(*) as PatientCnt
from
(
    select  p.PatientId
    from    [da.Patient] p
            inner join [da.CheckListActivity] cla   on  p.PatientId = cla.PatientId
    group by p.PatientId
    having  min(FilledBy)   = 0
    and     max(FilledBy)   = 0
) p

user_6357694 · Answer

SELECT COUNT(DISTINCT PatientId) PatientCnt
FROM [da.CheckListActivity]
WHERE [PatientId] IN (
        SELECT PatientId
        FROM [dbo].[da.Patient]
        WHERE CONVERT(DATE, DischargeDate) = CONVERT(DATE, '2021-05-02')
        )
    AND PatientId NOT IN (
        SELECT PatientId
        FROM [da.CheckListActivity]
        WHERE FilledBy <> 0
        )

Output: 2

If you remove the COUNT, you will get 102 and 104, your desired output.

How to get distinct count of records from table having a column with total value is 0

2 Answers