使用参数进行每日、每周、每月选择的SQL查询

Question

我有一个存储过程，用于查找零件信息以提供报告。我想每天、每周和每月运行该程序来收集数据。目前我有以下内容，但不知道如何在where子句中正确格式化case语句，或者这是否是实现目标的最佳方式。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Assemblies]
@FREQUENCY varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT SC01 AS 'Part Number', 
(CASE 
WHEN SC01 LIKE '%Assembly1%' THEN 'A1' 
WHEN SC01 LIKE '%Assembly2%' THEN 'A2' 
WHEN SC01 LIKE '%Assembly3%' THEN 'A3' 
ELSE 'Other'
END) AS 'Assembly', 
(CASE 
WHEN SC01 LIKE '%Component1%' THEN 'C1' 
WHEN SC01 LIKE '%Component2%' THEN 'C2' 
WHEN SC01 LIKE '%Component3%' THEN 'C3' 
ELSE 'Other' 
END) AS 'Component', 
Key3 AS 'Group' 
FROM dbo.Part 
WHERE (SC01 LIKE '%Assembly%' OR SC01 LIKE '%Component%') AND
CASE 
WHEN @FREQUENCY = 'DAILY' THEN 
Date01 = (CAST(GETDATE()) 
WHEN @FREQUENCY = 'WEEKLY' THEN 
Date01 >= DATEADD(DAY, 1-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
AND Date01 <= DATEADD(DAY, 8-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4))
WHEN @FREQUENCY = 'MONTHLY' THEN
Date01 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date01 <= GETDATE()
END
ORDER BY SC01
END

Answer 1

由于您使用的是过程，因此您可以在查询之前计算日期，并使其更具可读性。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Assemblies]
@FREQUENCY varchar(10)
AS
BEGIN
SET NOCOUNT ON;
Declare @BeginDate datetime = '[date]'
declare @EndDate datetime = '[date]'
-- use your calculation logic.

SELECT SC01 AS 'Part Number', 
(CASE 
WHEN SC01 LIKE '%Assembly1%' THEN 'A1' 
WHEN SC01 LIKE '%Assembly2%' THEN 'A2' 
WHEN SC01 LIKE '%Assembly3%' THEN 'A3' 
ELSE 'Other'
END) AS 'Assembly', 
(CASE 
WHEN SC01 LIKE '%Component1%' THEN 'C1' 
WHEN SC01 LIKE '%Component2%' THEN 'C2' 
WHEN SC01 LIKE '%Component3%' THEN 'C3' 
ELSE 'Other' 
END) AS 'Component', 
Key3 AS 'Group' 
FROM dbo.Part 
WHERE (SC01 LIKE '%Assembly%' OR SC01 LIKE '%Component%') 
AND Date01 BETWEEN @BeginDate AND @EndDate
END
ORDER BY SC01
END

Answer 2

在WHERE子句中，您需要确保每个条件都有左手边和右手边。

您目前只有CASE WHEN子句的左手边，因此您需要以某种方式重新格式化它，以获得可以与右手边进行比较的一致结果。

一种可能的方法(这可能不是最有效的(是：

CASE 
WHEN @FREQUENCY = 'DAILY' 
AND Date01 = (CAST(GETDATE()) 
THEN 1
WHEN @FREQUENCY = 'WEEKLY' 
AND Date01 >= DATEADD(DAY, 1-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
AND Date01 <= DATEADD(DAY, 8-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
THEN 1
WHEN @FREQUENCY = 'MONTHLY'
AND Date01 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date01 <= GETDATE() 
THEN 1
ELSE 0
END = 1

然而，这本质上与只使用几个OR语句相同(注意将OR语句包装在额外的括号中，以确保它们按预期工作(：

WHERE ...
AND (
(@FREQUENCY = 'DAILY' 
AND Date01 = (CAST(GETDATE()) 
OR (@FREQUENCY = 'WEEKLY' 
AND Date01 >= DATEADD(DAY, 1-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)) 
AND Date01 <= DATEADD(DAY, 8-DATEPART(dw, GETDATE() - 4), CONVERT(DATE,GETDATE() - 4)))
OR (@FREQUENCY = 'MONTHLY'
AND Date01 >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 
AND Date01 <= GETDATE())
)