I have below query not getting

DECLARE @orgId int = 3,
        @insurance int = 0,
        @isMoreInfoNeeded bit = 0;

SET NOCOUNT ON;
SELECT pd.assignedid AS patientId,
       pd.patientName,
       p.name AS productname,
       i.insuranceName,
       fp.datecreated AS dateOrdered,
       ISNULL(pe.firstname   ' '   pe.lastname, 'Migrated/By Service') AS requestedby,
       fp.isMoreInfoNeeded,
       p.productId,
       fp.personId
FROM patientdetails pd
     INNER JOIN t1 fp (NOLOCK) ON fp.personid = pd.personid
                                              AND pd.organizationId = @orgId
                                              AND YEAR(fp.dateCreated) = YEAR(GETDATE())
     INNER JOIN t2 p (NOLOCK) ON p.productId = fp.productId
                                  AND (fp.doId IS NULL
                                    OR fp.doId = 0)
     LEFT JOIN t3 pe (NOLOCK) ON fp.createdby = pe.personId
     LEFT JOIN t4 pai (NOLOCK) ON pai.personId = pd.personid
     LEFT JOIN t5 i (NOLOCK) ON i.insuranceid = pai.insuranceid
     LEFT JOIN t6 nt (NOLOCK) ON nt.personid = fp.personid
                                            AND fp.productId = nt.productId
     LEFT OUTER JOIN (SELECT MAX(doId) AS doId,
                             personId
                      FROM doctororderall
                      WHERE personId > 0
                        AND productId = 1
                        AND YEAR(datecreated) = YEAR(GETDATE())
                      GROUP BY personId) d ON d.personId = pd.personId
WHERE pd.organizationid = @orgId
  AND d.doId IS NULL
  AND pd.personId NOT IN (SELECT personId
                          FROM PatientMissingInformation
                          WHERE status NOT IN ('Process Complete', 'Patient Cancelled')
                            AND personId = pd.personId)
  AND (@insurance = 0
    OR (@insurance = 1
    AND pai.isprimary = 1
    AND i.insuranceId IN (1, 2, 3, 4))
    OR (@insurance = 2
    AND pai.isprimary = 1
    AND i.insuranceId NOT IN (1, 2, 3, 4))
    OR (@insurance = 3
    AND i.insuranceName IS NULL))
  AND (fp.isMoreInfoNeeded = @isMoreInfoNeeded)
  AND ((nt.notcoveredpatientId IS NULL))
  AND pd.personId NOT IN (SELECT personId
                          FROM notcoveredpatient
                          WHERE YEAR(datecreated) = YEAR(GETDATE()));

If I will pass values above declared insurance int=0 then i am getting records counts: 156

IF I will change only insurance para value insurance int=1 : counts=18 , insurance int=2 : counts=63 and insurance=3 : counts=32

if i will pass insurance int=0 then output count should be 18 63 32=113 but I am getting result 156 that is wrong, Is my filtering logic is wrong?

Insurance int=0 is sum of insurance value 1 2 3

CodePudding user response：

If the 0-case should sum the others, then it should have the respective formula:

and (    (@insurance=0 and (pai.isprimary=1 or i.insuranceName is null))
or......
)