TSQL & SSRS -返回两个不同数据集的同一个查询

Question

解释起来有点尴尬，所以我会尽我最大的努力。

我用SSMS 17编写了一个查询，查询运行良好，并正确返回1990-01-01至2018-03-04年间的数据。这是正确的，2018-03-04是这个查询的最新情况。

当将这个确切的查询放到SSRS (Visual )中时，我最初放置了一个数据参数，该参数级联成两个选项来选择case类型和位置。我将这些数据集作为单独的数据集来链接这些参数来级联它们。一切都很顺利。直到我对这份报告进行了最后一次核对之后，我才意识到我无法在2017年-10-27年获得任何数据。在任何数据上都没有过滤器.

最后，我已经删除了我的所有参数，所以它只是主要的数据集，并输入了一个代码来代替过去的2年。这仍将在2017年至10-27年结束。有没有人知道在SSMS中运行的同一个查询是如何正确地返回数据的，但是在SSRS中复制和粘贴，却突然不能超过2017-10-27？

当时只有1,615行，所以不像有大量的数据，我遇到了一些限制。

真的很困惑于这件事。我没有发布我的代码，因为它在没有问题的SSMS中工作，所以我不相信问题就在那里。

这是当前使用的代码。对于日期而言，较长的情况是计算UTC数据库到BST的时间。

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 SET NOCOUNT ON;
 SET ARITHABORT ON;

 WITH cvr AS
 (
 SELECT DISTINCT 
                        hcx.mps_Person,
                        cpx.mps_Name [Name],
 CASE       WHEN hcx.mps_SchemeCode IS NULL THEN LTRIM(RTRIM(hcx.mps_MembershipNumber)) 
            WHEN hcx.mps_SchemeCode = '' THEN LTRIM(RTRIM(hcx.mps_MembershipNumber)) 
            WHEN hcx.mps_SchemeCode IS NOT NULL THEN LTRIM(RTRIM(hcx.mps_SchemeCode))+'/'+LTRIM(RTRIM(hcx.mps_MembershipNumber)) ELSE NULL END AS [Membership Number],
                        hcx.mps_MemberCoverStatus [Cover Status],
                         cmd.[Country of Incident] [Case Country],
 CASE       WHEN hcx.mps_CoverSource = 0 THEN 'Cover Account' 
            WHEN hcx.mps_CoverSource = 1 THEN 'SAM Cover' 
            WHEN hcx.mps_CoverSource = 2 THEN 'MDU Transfer Cover' ELSE NULL END AS [Cover Source],
 CASE       WHEN hcx.mps_startdate > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,hcx.mps_startdate) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND hcx.mps_startdate < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,hcx.mps_startdate) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,hcx.mps_startdate)) ELSE hcx.mps_startdate END AS [CM Cover Start Date],
 CASE       WHEN hcx.mps_enddate > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,hcx.mps_enddate) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND hcx.mps_enddate < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,hcx.mps_enddate) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,hcx.mps_enddate)) ELSE hcx.mps_enddate END AS [CM Cover End Date],
                        cmd.[Case Number] AS [Case Number],
                        cmd.IncidentId,
                        cmd.[Medical/Dental] AS [Medical/Dental],
                        cmd.[Primary Case Type] AS [Incident Primary Case Type],
                        cmd.[Case Types] AS [Incident Case Types],
                        cpx.mps_dn_CaseTypesInvolved AS [Member Involved Case Types],
 CASE       WHEN cpx.mps_involvedfrom > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_involvedfrom) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND cpx.mps_involvedfrom < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_involvedfrom) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,cpx.mps_involvedfrom)) ELSE cpx.mps_involvedfrom END AS [Involved From],
 CASE       WHEN cpx.mps_involvedto > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_involvedto) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND cpx.mps_involvedto < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_involvedto) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,cpx.mps_involvedto)) ELSE cpx.mps_involvedto END AS [Involved To],
 CASE       WHEN cpx.mps_claimsmadenotificationdate > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_claimsmadenotificationdate) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND cpx.mps_claimsmadenotificationdate < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cpx.mps_claimsmadenotificationdate) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,cpx.mps_claimsmadenotificationdate)) ELSE cpx.mps_claimsmadenotificationdate END AS [Claims Made Notification Date],
 CASE       WHEN cmd.[MPS Claim Date] > (DATEADD(HH,1,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cmd.[MPS Claim Date]) AS CHAR) + '/03/01'),30))/7*7,'19000107')))) AND cmd.[MPS Claim Date] < (DATEADD(HH,2,(DATEADD(DAY,DATEDIFF(DAY,'19000107',DATEADD(MONTH,DATEDIFF(MONTH,0,CAST(DATEPART(YEAR,cmd.[MPS Claim Date]) AS CHAR) + '/10/01'),30))/7*7,'19000107')))) THEN (DATEADD(HH,1,cmd.[MPS Claim Date])) ELSE cmd.[MPS Claim Date] END AS [MPS Claim Date]
                        ,cmd.[Total Claim Payments (Sterling Equivalent in £)] AS [TotalClaimPayments]
                        ,cmd.[Total Non-Claim Payments (Sterling Equivalent in £)] AS [TotalNonClaimPayments]
                        ,FLOOR(cmd.[MPS Apportionment %]*100) AS [Liability]

 FROM       OneMPS_MSCRM.dbo.mps_historiccoveraccountExtensionBase AS hcx
     INNER JOIN OneMPS_MSCRM.dbo.mps_historiccoveraccountBase AS hca ON hca.mps_historiccoveraccountId = hcx.mps_historiccoveraccountId AND hca.statuscode = 1 /*Active*/
     INNER JOIN OneMPS_MSCRM.dbo.mps_casepartyExtensionBase AS cpx  ON hcx.mps_Person = cpx.mps_Person
     INNER JOIN OneMPS_MSCRM.dbo.mps_casepartyBase AS cpb ON cpx.mps_casepartyId = cpb.mps_casepartyId AND cpb.statuscode = 1 /*Active*/ AND cpx.mps_PrimaryRole = 0
     INNER JOIN dbo.CasesMasterData AS cmd ON cmd.incidentid = cpx.mps_Case

 WHERE  hcx.mps_CoverBasis = 'Claims Made'
     AND hcx.mps_IsSuperseded = 0 /*Not Superseded*/
     AND cpx.mps_involvedto >= hcx.mps_StartDate /*Intersects with Period of Involvement*/
     AND cpx.mps_involvedfrom <= hcx.mps_EndDate /*Intersects with Period of Involvement*/
     AND cpx.mps_InvolvedFrom >= '1990-01-01 00:00:00.000'
 )
 ,cts AS
 (
 SELECT 
             cvr.mps_Person,
             cvr.[Cover Source],
             cvr.Name,
             cvr.[Membership Number],
             cvr.[Cover Status],
             cvr.[CM Cover Start Date],
             cvr.[CM Cover End Date],
             cvr.[Case Number],
             cvr.IncidentId,
             cvr.[Involved From],
             cvr.[Involved To],
             cvr.[Claims Made Notification Date],
             cvr.[MPS Claim Date],
             cvr.[Medical/Dental],
             cvr.[Case Country],
             cvr.[Incident Primary Case Type],
             cvr.[Incident Case Types],
             cvr.[Member Involved Case Types],
 CASE       WHEN LEAD(cvr.[CM Cover Start Date],1,0) OVER (PARTITION BY cvr.mps_person, cvr.[Membership Number], cvr.[Case Number] ORDER BY cvr.[CM Cover Start Date]) = cvr.[CM Cover Start Date]
        AND LEAD(cvr.[CM Cover End Date],1,0) OVER (PARTITION BY cvr.mps_person, cvr.[Membership Number], cvr.[Case Number] ORDER BY cvr.[CM Cover Start Date]) = cvr.[CM Cover End Date] THEN NULL
            ELSE DATEDIFF(d,cvr.[CM Cover Start Date],cvr.[CM Cover End Date]) END [CM Cover Days],
 (SELECT MIN(cvr_sd.[CM Cover Start Date]) FROM cvr cvr_sd WHERE cvr_sd.mps_Person = cvr.mps_Person AND cvr_sd.[Membership Number] = cvr.[Membership Number] AND cvr_sd.[Case Number] = cvr.[Case Number]) [CM Cover Start Date (Min)],
 (SELECT MAX(cvr_ed.[CM Cover End Date]) FROM cvr cvr_ed WHERE cvr_ed.mps_Person = cvr.mps_Person AND cvr_ed.[Membership Number] = cvr.[Membership Number] AND cvr_ed.[Case Number] = cvr.[Case Number]) [CM Cover End Date (Max)],
 (SELECT COUNT(cvr_rw.mps_Person) FROM cvr cvr_rw WHERE cvr_rw.mps_Person = cvr.mps_Person AND cvr_rw.[Membership Number] = cvr.[Membership Number] AND cvr_rw.[Case Number] = cvr.[Case Number]) [Total Rows],
             cvr.TotalClaimPayments, 
             cvr.TotalNonClaimPayments, 
             cvr.Liability
 FROM   cvr
 )
 ,chk AS 
 (
 SELECT 
            cts.mps_Person,
            cts.[Case Number],
            cts.IncidentId,
            cts.[Membership Number],
     SUM(cts.[CM Cover Days]) AS [CM Cover Days],
    DATEDIFF(d,cts.[CM Cover Start Date (Min)],cts.[CM Cover End Date (Max)])-MAX(cts.[Total Rows]) AS [CM Cover Days (If Unbroken)]
 FROM   cts
 GROUP BY
             cts.mps_Person,
             cts.[Case Number],
             cts.IncidentId,
             cts.[Membership Number],
             cts.[CM Cover Start Date (Min)],
             cts.[CM Cover End Date (Max)],
             cts.[Case Country]
 )
 SELECT
            cts.Name,
            cts.[Membership Number],
            cts.[Cover Status],
            cts.[Cover Source],
            cts.[CM Cover Start Date],
            cts.[CM Cover End Date],
 CASE   WHEN chk.[CM Cover Days] >= DATEDIFF(d,cts.[CM Cover Start Date (Min)],cts.[CM Cover End Date (Max)])-cts.[Total Rows] THEN cts.[CM Cover Start Date (Min)] ELSE NULL END [CM Continuous Cover Start Date],
 CASE   WHEN chk.[CM Cover Days] >= DATEDIFF(d,cts.[CM Cover Start Date (Min)],cts.[CM Cover End Date (Max)])-cts.[Total Rows] THEN cts.[CM Cover End Date (Max)] ELSE NULL END [CM Continuous Cover End Date],
            a2a.[A2A Decision],
            a2a.[A2A Decision Reason],
            cts.[Case Number],
            cts.[Involved From] AS [Involved From Date],
            cts.[Involved To] AS [Involved To Date],
            cts.[Claims Made Notification Date] AS [Claims Made Notification Date],
            cts.[MPS Claim Date],
            cts.[Medical/Dental],
            cts.[Case Country],
            cts.[Incident Primary Case Type],
            cts.[Incident Case Types],
            cts.[Member Involved Case Types],
            mbr.[# Members Involved],
    SUM(ISNULL(cts.TotalClaimPayments,0)+ISNULL(cts.TotalNonClaimPayments,0)) AS [TotalSpend],
            ISNULL(cts.TotalClaimPayments,0) AS [Claim Payments - Total (£)],
    CAST(cts.Liability*(cts.TotalClaimPayments/100) AS NUMERIC(14,2)) AS [Claim Payments - Apportioned to Member (£)],
            ISNULL(cts.TotalNonClaimPayments,0) AS [Non-Claim Payments - Total (£)],
            cts.Liability AS [MPSClaimLiability]
 FROM   cts
        INNER JOIN chk ON chk.[Case Number] = cts.[Case Number] AND chk.[Membership Number] = cts.[Membership Number] AND chk.mps_Person = cts.mps_Person

OUTER APPLY
 (
 SELECT TOP 1
 CASE    WHEN   adx.mps_Decision = 0 THEN 'No' 
         WHEN   adx.mps_Decision = 1 THEN 'Yes' 
         WHEN   adx.mps_Decision = 2 THEN 'Pending' 
         WHEN   adx.mps_Decision = 3 THEN 'No - Member Declined' 
         WHEN   adx.mps_Decision = 4 THEN 'Yes - Ex Gratia' 
         WHEN   adx.mps_Decision = 5 THEN 'No - Member Uncontactable/Not Responding'
         ELSE NULL END AS [A2A Decision],
            adx.mps_DecisionReason AS [A2A Decision Reason]

 FROM   OneMPS_MSCRM.dbo.mps_authoritytoassistdecisionBase AS adb
 INNER JOIN OneMPS_MSCRM.dbo.mps_authoritytoassistdecisionExtensionBase adx ON adb.mps_authoritytoassistdecisionId = adx.mps_authoritytoassistdecisionId AND adb.statuscode IN (2) /*2=Valid*/
 INNER JOIN OneMPS_MSCRM.dbo.mps_casepartyExtensionBase cpx ON cpx.mps_casepartyId = adx.mps_CaseParty
 INNER JOIN OneMPS_MSCRM.dbo.mps_casepartyBase cpb ON cpx.mps_casepartyId = cpb.mps_casepartyId AND cpb.statuscode = 1 /*Active*/ AND cpx.mps_PrimaryRole = 0

 WHERE          cpx.mps_Case = cts.IncidentId

 ORDER BY  adx.mps_DecisionOn DESC 
 ) AS a2a
 OUTER APPLY
 (
 SELECT TOP 1 COUNT(cpx.mps_casepartyId) AS [# Members Involved]
 FROM   OneMPS_MSCRM.dbo.mps_casepartyExtensionBase cpx 
 INNER JOIN OneMPS_MSCRM.dbo.mps_casepartyBase cpb ON cpx.mps_casepartyId = cpb.mps_casepartyId AND cpb.statuscode = 1 /*Active*/ AND cpx.mps_PrimaryRole = 0

 WHERE cpx.mps_Case = cts.IncidentId
 ) AS mbr

 GROUP BY        cts.Name,
                 cts.[Membership Number],
                 cts.[Cover Status],
                 cts.[Cover Source],
                 cts.[CM Cover Start Date],
                 cts.[CM Cover End Date],
 CASE WHEN chk.[CM Cover Days] >= DATEDIFF(d,cts.[CM Cover Start Date (Min)],cts.[CM Cover End Date (Max)])-cts.[Total Rows] THEN cts.[CM Cover Start Date (Min)] ELSE NULL END,
 CASE WHEN chk.[CM Cover Days] >= DATEDIFF(d,cts.[CM Cover Start Date (Min)],cts.[CM Cover End Date (Max)])-cts.[Total Rows] THEN cts.[CM Cover End Date (Max)] ELSE NULL ENd,
                 a2a.[A2A Decision],
                 a2a.[A2A Decision Reason],
                 cts.[Case Number],
                 cts.[Involved From],
                 cts.[Involved To] ,
                 cts.[Claims Made Notification Date],
                 cts.[MPS Claim Date],
                 cts.[Medical/Dental],
                 cts.[Case Country],
                 cts.[Incident Primary Case Type],
                 cts.[Incident Case Types],
                 cts.[Member Involved Case Types],
                 mbr.[# Members Involved],
                cts.TotalClaimPayments ,
                CAST(cts.Liability*(cts.TotalClaimPayments/100) AS NUMERIC(14,2)),
                cts.TotalNonClaimPayments,
                cts.Liability
                ORDER BY cts.[Involved From] ASC 

样本数据缩短。本质上是一种数据提取。

Name|Membership Number|CoverStatus|Cover Date             |Involved From 
Bob |984684638        |Active     |2017-03-01 00:00:00.000|2017-10-27 00:00:00.000
Test|135486968        |Active     |2017-07-01 00:00:00.000|2018-03-04 00:00:00.000

第一行将显示为SSRS罚款，第二行将完全不可见。这将工作的主要日期是最后一次参与。我唯一能想到的就是BST的转换可能会以某种方式影响它，因为它是我对日期所做的唯一一件事。

Answer 1

嗨，所有的道歉，我似乎已经很好地考虑了这一点。感谢那些对此作出回应的人。

它原来是共享数据源，我们有一个活动和测试环境。data说它是指向实时的，但是当我检查连接字符串(为什么我之前没有想到它)时，它实际上是在查找数据有限的测试服务器。

用这个看不见树木的木头:)所以吸取了教训。记住要检查连接字符串，而不仅仅是名称。

Answer 2

将此查询加载到SP中并从SP中提供源代码，我希望这将解决这个问题，因为代码是巨大的。

Answer 3

您已设置事务隔离级别读未提交； 可能是同一个查询，因为这个隔离级别会话返回两个不同的数据集。就像在事务下更新任何表(插入、更新或删除)以及未完成未提交或回滚的事务一样，未提交值将在“读取未提交”隔离事务会话的选择查询中显示(脏读)。 因此，设置事务隔离级别快照；