下午所有
奇怪的是这个。我编写了一个相当简单的查询,并且我认为它工作正常。但是,当我一次又一次地多次运行查询时,每次都会得到不同的行数。它是一个实时数据库,但除了我之外,今天没有人使用它。我在参数中设置的日期范围是过去的日期,因此新的\已删除的记录不应影响它。所以我有点困惑为什么有时我会得到更多的行,有时会更少的行,而我每次都应该返回相同的行。复制下面的代码
DECLARE
@From date,
@To date
SET @From = '01/07/2015'
SET @To = '31/12/2015'
--AS
SELECT DISTINCT
BR.Branch,
DIV.Division,
BCM.ClientRef@ AS 'Client Reference',
BPY.PolicyRef@ AS 'Policy Reference',
AE.Name AS 'Account Executive',
DIV2.#Name AS 'Account Handler',
BTX.Dt_raised AS 'Effective Date',
BTX.Ledger_dt AS 'Ledger Date',
INS.VTDescription AS 'Insurer',
BPY.Ptype AS 'Policy Type',
BTX.Orig_Debt AS 'GWP',
COALESCE(BTX.Comm_amt, 0) AS 'Commission',
CASE
WHEN BTX.Ccode = '7' THEN 0
ELSE COALESCE(CHG.Orig_Debt, 0)
END AS 'Fee',
COALESCE(BTX.Comm_amt, 0) + CASE WHEN BTX.Ccode = '7' THEN 0 ELSE COALESCE(CHG.Orig_Debt, 0) END AS 'Income',
CASE DATENAME(MONTH, BTX.Dt_Raised)
WHEN 'July' THEN YEAR(BTX.Dt_Raised)
WHEN 'August' THEN YEAR(BTX.Dt_Raised)
WHEN 'September' THEN YEAR(BTX.Dt_Raised)
WHEN 'October' THEN YEAR(BTX.Dt_Raised)
WHEN 'November' THEN YEAR(BTX.Dt_Raised)
WHEN 'December' THEN YEAR(BTX.Dt_Raised)
WHEN 'January' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
WHEN 'February' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
WHEN 'March' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
WHEN 'April' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
WHEN 'May' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
WHEN 'June' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
END AS 'FinYear',
CASE DATENAME(MONTH, BTX.Dt_Raised)
WHEN 'January' THEN 'Ja'
WHEN 'February' THEN 'Fe'
WHEN 'March' THEN 'Ma'
WHEN 'April' THEN 'Ap'
WHEN 'May' THEN 'My'
WHEN 'June' THEN 'Ju'
WHEN 'July' THEN 'Jy'
WHEN 'August' THEN 'Au'
WHEN 'September' THEN 'Se'
WHEN 'October' THEN 'Oc'
WHEN 'November' THEN 'No'
WHEN 'December' THEN 'De'
END AS 'Month Letter',
DATENAME(MONTH, BTX.Dt_Raised) AS 'MonthName',
CASE DATENAME(MONTH, BTX.Dt_Raised)
WHEN 'January' THEN 7
WHEN 'February' THEN 8
WHEN 'March' THEN 9
WHEN 'April' THEN 10
WHEN 'May' THEN 11
WHEN 'June' THEN 12
WHEN 'July' THEN 1
WHEN 'August' THEN 2
WHEN 'September' THEN 3
WHEN 'October' THEN 4
WHEN 'November' THEN 5
WHEN 'December' THEN 6
END AS 'Order',
CASE ROW_NUMBER() OVER (PARTITION BY BCM.ClientRef@ ORDER BY BCM.ClientRef@)
WHEN 1 THEN 1 ELSE 0
END AS 'Client Count',
CASE ROW_NUMBER() OVER (PARTITION BY BPY.PolicyRef@ ORDER BY BPY.PolicyRef@)
WHEN 1 THEN 1 ELSE 0
END AS 'Policy Count'
FROM icp_yyclient AS BCM
INNER JOIN icp_brpolicy AS BPY ON BCM.ClientRef@ = BPY.ClientRef@ AND BCM.Branch@ = BPY.Branch@
INNER JOIN icp_brcledger AS BTX ON BPY.PolicyRef@ = BTX.PolicyRef@ AND BPY.Branch@ = BTX.Branch@
LEFT JOIN icp_brcledger AS CHG ON BTX.Chg_ptr = CHG.Suffix AND BTX.PolicyRef@ = CHG.PolicyRef@ AND BTX.Branch@ = CHG.Branch@
LEFT JOIN AccountExecutives AS AE ON BCM.Job1 = AE.Code
LEFT JOIN icp_bredetail AS DIV2 ON BPY.#Exec = DIV2.Code AND BPY.Branch@ = DIV2.Branch@
LEFT JOIN Division_VT AS DIV ON DIV2.Ext_department = DIV.Division
LEFT JOIN Branch_VT AS BR ON DIV.Branch = BR.VTId
LEFT JOIN icp_INSC_VT AS INS ON BPY.Insco_INSC_VTId = INS.VTId
WHERE
BPY.Branch@ = 0
AND BTX.Dt_Raised BETWEEN @From AND @To
AND (BTX.Trantype IN ('New Business') AND BTX.Trantype <> 'Charge')
AND (BPY.Term_code IS NULL AND BPY.Term_date IS NULL)
ORDER BY BR.Branch, DIV.Division, BCM.ClientRef@