我有一个查询，我正在尝试对日期值进行硬编码。目前，这些数据来自SSRS日历。我认为我的数据与预期没有什么不同。为了调试所有的东西，我需要对日期部分进行硬编码。
我该如何处理这些事情？我是SQL和SSRS的新手。
WITH Calendar (Start_Date, End_Date) AS(
Select TOP 6
DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) -6 + Row_Number() Over (Order By CI_ID), 0)),0)[Start_Date],
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) -6 + Row_Number() Over (Order By CI_ID), 0))+1,0)) [End_Date]
From v_UpdateInfo
Group by CI_ID
)
Select Start_Date, End_Date,
(Select count(distinct ucs.ResourceID)
From v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui on ui.CI_ID = ucs.CI_ID and ucs.Status = 2 AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryInstanceName IN (@Category) on ucs.CI_ID = cica.CI_ID
Where ucs.ResourceID IN (Select rs.ResourceID from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and FCM.CollectionID = @CollectionID JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID where rs.Operating_System_Name_and0 like '%Workstation 6.1%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0 )
AND ui.DateRevised BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 7],
(Select count(rs.ResourceID) from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and FCM.CollectionID = @CollectionID JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID where rs.Operating_System_Name_and0 like '%Workstation 6.3%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0) [Windows 8.1 Total],
(Select count(distinct ucs.ResourceID)
From v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui on ui.CI_ID = ucs.CI_ID and ucs.Status = 2 AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID AND cica.CategoryInstanceName IN (@Category) on ucs.CI_ID = cica.CI_ID
Where ucs.ResourceID IN (Select rs.ResourceID from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and FCM.CollectionID = @CollectionID JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID where rs.Operating_System_Name_and0 like '%Workstation 10%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0 )
AND ui.DateRevised BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 10],
(Select count(rs.ResourceID) from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and FCM.CollectionID = @CollectionID JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID where rs.Operating_System_Name_and0 like '%Workstation 10%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0) [Windows 10 Total]
from Calendar cal
到目前为止，我试过这个。
Declare start_date = '2019-06-01';
declare end_date = '2019-11-30';
--WITH Calendar (Start_Date, End_Date) AS(
Select TOP 6
DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) -6 + Row_Number() Over (Order By CI_ID), 0)),0)[Start_Date],
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) -6 + Row_Number() Over (Order By CI_ID), 0))+1,0)) [End_Date]
From v_UpdateInfo
Group by CI_ID
)
Select Start_Date, End_Date,
(Select count(distinct ucs.ResourceID)
From v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui on ui.CI_ID = ucs.CI_ID and ucs.Status = 2 AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID --AND
--cica.CategoryInstanceName -- IN (@Category)
on ucs.CI_ID = cica.CI_ID
Where ucs.ResourceID IN (Select rs.ResourceID from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID
and FCM.CollectionID = 'ABC0028' JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID
where rs.Operating_System_Name_and0 like '%Workstation 6.1%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0 )
AND ui.DateRevised BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 7],
(Select count(rs.ResourceID) from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID
and FCM.CollectionID = 'ABC0028' JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID
where rs.Operating_System_Name_and0 like '%Workstation 6.3%'
And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0) [Windows 8.1 Total],
(Select count(distinct ucs.ResourceID)
From v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui on ui.CI_ID = ucs.CI_ID and ucs.Status = 2 AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica JOIN v_CategoryInfo ci on cica.CategoryInstanceID = ci.CategoryInstanceID
--AND cica.CategoryInstanceName IN (@Category)
on ucs.CI_ID = cica.CI_ID
Where ucs.ResourceID IN (Select rs.ResourceID
from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and FCM.CollectionID = 'ABC0028'
JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID
where rs.Operating_System_Name_and0 like '%Workstation 10%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0 )
AND ui.DateRevised BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 10],
(Select count(rs.ResourceID) from v_r_system rs JOIN V_FullCollectionMembership AS FCM on rs.ResourceID = FCM.ResourceID and
FCM.CollectionID = 'ABC0028' JOIN v_UpdateScanStatus uss on uss.ResourceID = rs.ResourceID where rs.Operating_System_Name_and0
like '%Workstation 10%' And DATEDIFF("d",uss.LastScanTime,getdate()) <= @DaysScanned AND rs.Obsolete0 = 0) [Windows 10 Total]
--from Calendar cal
您有两个日期参数来控制查询。它们分别是CCD_ 1和CCD_。您需要在查询中更新对这些引用的所有引用。在您发布的原始查询中，有2个引用。
为了尽量减少对查询的修改，我在这个解决方案中所做的就是修改CTE以使用硬编码的日期。
此外，需要注意的是，除了此查询中的日期之外，还有其他变量需要声明和处理。它们是@DaysScand和@CollectionID。既然你在你的OP中没有提到它们，我想它们对你来说不是问题。
WITH Calendar (Start_Date, End_Date)
AS (SELECT CAST('2019-06-01' AS DATETIME2) AS [Start_Date],
CAST('2019-11-30' AS DATETIME2) AS [End_Date])
SELECT Start_Date,
End_Date,
(
SELECT COUNT(DISTINCT ucs.ResourceID)
FROM v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui
ON ui.CI_ID = ucs.CI_ID
AND ucs.Status = 2
AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica
JOIN v_CategoryInfo ci
ON cica.CategoryInstanceID = ci.CategoryInstanceID
AND cica.CategoryInstanceName IN ( @Category )
ON ucs.CI_ID = cica.CI_ID
WHERE ucs.ResourceID IN
(
SELECT rs.ResourceID
FROM v_r_system rs
JOIN V_FullCollectionMembership AS FCM
ON rs.ResourceID = FCM.ResourceID
AND FCM.CollectionID = @CollectionID
JOIN v_UpdateScanStatus uss
ON uss.ResourceID = rs.ResourceID
WHERE rs.Operating_System_Name_and0 LIKE '%Workstation 6.1%'
AND DATEDIFF("d", uss.LastScanTime, GETDATE()) <= @DaysScanned
AND rs.Obsolete0 = 0
)
AND ui.DateRevised
BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 7],
(
SELECT COUNT(rs.ResourceID)
FROM v_r_system rs
JOIN V_FullCollectionMembership AS FCM
ON rs.ResourceID = FCM.ResourceID
AND FCM.CollectionID = @CollectionID
JOIN v_UpdateScanStatus uss
ON uss.ResourceID = rs.ResourceID
WHERE rs.Operating_System_Name_and0 LIKE '%Workstation 6.3%'
AND DATEDIFF("d", uss.LastScanTime, GETDATE()) <= @DaysScanned
AND rs.Obsolete0 = 0
) [Windows 8.1 Total],
(
SELECT COUNT(DISTINCT ucs.ResourceID)
FROM v_Update_ComplianceStatus ucs
JOIN v_UpdateInfo ui
ON ui.CI_ID = ucs.CI_ID
AND ucs.Status = 2
AND ui.IsSuperseded = 0
JOIN v_CICategoryInfo_All cica
JOIN v_CategoryInfo ci
ON cica.CategoryInstanceID = ci.CategoryInstanceID
AND cica.CategoryInstanceName IN ( @Category )
ON ucs.CI_ID = cica.CI_ID
WHERE ucs.ResourceID IN
(
SELECT rs.ResourceID
FROM v_r_system rs
JOIN V_FullCollectionMembership AS FCM
ON rs.ResourceID = FCM.ResourceID
AND FCM.CollectionID = @CollectionID
JOIN v_UpdateScanStatus uss
ON uss.ResourceID = rs.ResourceID
WHERE rs.Operating_System_Name_and0 LIKE '%Workstation 10%'
AND DATEDIFF("d", uss.LastScanTime, GETDATE()) <= @DaysScanned
AND rs.Obsolete0 = 0
)
AND ui.DateRevised
BETWEEN cal.Start_Date AND cal.End_Date
) [Windows 10],
(
SELECT COUNT(rs.ResourceID)
FROM v_r_system rs
JOIN V_FullCollectionMembership AS FCM
ON rs.ResourceID = FCM.ResourceID
AND FCM.CollectionID = @CollectionID
JOIN v_UpdateScanStatus uss
ON uss.ResourceID = rs.ResourceID
WHERE rs.Operating_System_Name_and0 LIKE '%Workstation 10%'
AND DATEDIFF("d", uss.LastScanTime, GETDATE()) <= @DaysScanned
AND rs.Obsolete0 = 0
) [Windows 10 Total]
FROM Calendar cal;