Question

我正在撰写2005年Skk服务器管理演播室的存储程序,以归还一份两个不同时期的州和政策清单,每个月至今。我提出了两点意见,以收集所需数据和储存程序,用于报告服务报告。

以下是我的储存程序:

SELECT DISTINCT
    S.[State],
    COUNT(HP_MTD.PolicyID) AS PolicyCount_MTD,
    COUNT(HP_YTD.PolicyID) AS PolicyCount_YTD
FROM tblStates S
    LEFT OUTER JOIN vwHospitalPolicies HP_MTD ON S.[State] = HP.[State]
        AND HP.CreatedDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
    LEFT OUTER JOIN vwHospitalPolicies HP_YTD ON S.[State] = HP.[State]
        AND HP.CreatedDate BETWEEN DATEADD(YEAR, -1, GETDATE()) AND GETDATE()
GROUP BY S.[State]
ORDER BY S.[State] ASC

我正在讨论的问题是,当加上第二个小标题时,甚至连指第二加入的COUNT(COUNT)。我需要左派加入,因为并非所有国家都会为特定时期制定政策,但它们仍然应当出现在报告中。

任何建议都会受到高度赞赏。

Answer 1

你们需要:

COUNT(DISTINCT HP_MTD.PolicyID) AS PolicyCount_MTD,
COUNT(DISTINCT HP_YTD.PolicyID) AS PolicyCount_YTD

而不是:

COUNT(HP_MTD.PolicyID) AS PolicyCount_MTD,
COUNT(HP_YTD.PolicyID) AS PolicyCount_YTD

你最初的询问包括第二批配电数。添加<代码>DISTINCT <条码>COUNT中的条款对政策信息数据库的独特发生作了限制。

Answer 2

我倾向于开展此类工作,他们基本上倾向于某种观点。

你的实际问题是,有些政策是两次计算,一次是按月计算,一次是按年计算,一次是按年计算,一次是按改为。因此,如果您有这样的表格:

year-to-date
state   policy
==================
1       1
1       2
1       3
2       4

month-to-date
state   policy
==================
1       1
1       2

<代码>的表 JOINs(在COUNT上经评估)认为:

temp
state  monthPolicy  yearPolicy
================================
1      1            1
1      1            2
1      1            3
1      2            1
1      2            2
1      2            3
2      -            4

显然,你不想做什么。解决办法常常是用计算机通信技术或其他表格提及最后合并时的总结记录。与此类似:

WITH PoliciesMonthToDate (state, count) as (
                          SELECT state, COUNT(*)
                          FROM vwHospitalPolicies
                          WHERE createdDate >= DATEADD(MONTH, -1, GETDATE())
                          AND createdDate < DATEADD(DAY, 1, GETDATE())
                          GROUP BY state),
     PoliciesYearToDate (state, count) as (
                         SELECT state, COUNT(*)
                         FROM vwHospitalPolicies
                         WHERE createdDate >= DATEADD(YEAR, -1, GETDATE())
                         AND createdDate < DATEADD(DAY, 1, GETDATE())
                         GROUP BY state)
SELECT a.state, COALESCE(b.count, 0) as policy_count_mtd, 
                COALESCE(c.count, 0) as policy_count_ytd
FROM tblStates a
LEFT JOIN PoliciesMonthToDate b
ON b.state = a.state
LEFT JOIN PoliciesYearToDate c
ON c.state = a.state
ORDER BY a.state

(minor nitpick - 不要在表格和观点中使用预先固定装置。) 这样做是明智的,如果是出于某种原因,你要相互转换,就需要修改法典。否则,或名字会误导

友情链接