我有以下查詢沒有得到
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()));
如果我將傳遞上面宣告的值，insurance int=0那么我將獲得記錄數：156
如果我將只更改保險副價值insurance int=1 : counts=18 ，insurance int=2 : counts=63并且insurance=3 : counts=32
如果我將通過保險 int=0 那么輸出計數應該是 18 63 32=113 但我得到的結果 156 是錯誤的，我的過濾邏輯是錯誤的嗎？
保險int=0是保險價值1 2 3的總和
uj5u.com熱心網友回復：
如果 0-case 應該和其他的相加，那么它應該有各自的公式：
and ( (@insurance=0 and (pai.isprimary=1 or i.insuranceName is null))
or......
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/344766.html
標籤：sql sql-server