SQL Query using parameter for daily, weekly, monthly selection-CodePudding

I have a stored procedure used for looking up part information to feed a report. I would like to run the procedure to gather the data on a daily, weekly and monthly basis. Currently I have the below but can't figure out how to properly format the case statement in a where clause or if this is the best way to accomplish the goal.

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

CodePudding user response：

Since you are using a procedure, you could compute your date before the query itself and make it more readable.

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

CodePudding user response：

In your WHERE clause you need to ensure each condition has a left hand side and right hand side.

You currently only have the left hand side in of your CASE WHEN clause, so you will need to reformat it some way to achieve a consistent result that you can compare to the right hand side.

One possible method (and this may not be the most efficient) would be:

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

However this is essentially the same as just using several OR statements (take care to wrap the OR statements in an extra brackets to ensure they work as intended):

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())
    )