descargar - sql server versiones

Cláusula WHERE condicional para recuperar registros

Question

Estoy usando SQL Server 2008 R2.

Estoy teniendo un bloque SQL como a continuación:

DECLARE @Day INT = 5
DECLARE @Month INT = NULL
DECLARE @year INT = NULL
DECLARE @dtnow DATETIME

SET @dtnow = GETDATE()

IF @Month IS NULL
    SELECT @Month = MONTH(DATEADD(MONTH,-1, @dtnow ))

IF @Year IS NULL
    SELECT @year = YEAR(DATEADD(MONTH,-1, @dtnow ))


SELECT * 
FROM TblSalesRecords 
WHERE 
    CASE WHEN @Day IS NULL THEN -- Condition One
        BEGIN 
            ISNULL(@Month, MONTH(TblSalesRecords.CreatedDate)) = MONTH(TblSalesRecords.CreatedDate) 
            AND (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate)
        END 
    ELSE                        -- Condition two
        BEGIN 
            TblSalesRecords.CreatedDate BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(MONTH,-1, @dtnow ))) + ''-'' + CONVERT(VARCHAR,month(DATEADD(MONTH,-1,@dtnow))) + ''-'' + CONVERT(VARCHAR, DAY(DATEADD(MONTH,-1,@dtnow))) +'' 00:00:00.000'') 
            AND CONVERT (DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(DAY,-1,@dtnow ))) + ''-'' + CONVERT(VARCHAR,MONTH(DATEADD(DAY,-1,@dtnow))) + ''-'' + CONVERT(VARCHAR, DAY(DATEADD(DAY,-1,@dtnow))) +'' 23:59:59.998'')
        END
    END 
    AND TblSalesRecords.IsDeleted=0

Lo que quiero lograr es,

Si @Day se establece en NULL entonces debería ejecutar el CASE..WHEN declaración de - Condición uno más de - Condición dos .

Pero CASE..WHEN error de sintaxis cerca de "=" en la CASE..WHEN .

¿Alguien me puede decir cómo lograr esto?

Answer 1

Puedes lograrlo de esta manera:

SELECT * 
FROM TblSalesRecords 
WHERE (@Day IS NULL 
            AND ISNULL(@Month, MONTH(TblSalesRecords.CreatedDate)) = MONTH(TblSalesRecords.CreatedDate) 
            AND (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate)
       )
    OR (@Day IS NOT NULL                      -- Condition two
            AND TblSalesRecords.CreatedDate BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(MONTH,-1, @dtnow ))) + ''-'' + CONVERT(VARCHAR,month(DATEADD(MONTH,-1,@dtnow))) + ''-'' + CONVERT(VARCHAR, DAY(DATEADD(MONTH,-1,@dtnow))) +'' 00:00:00.000'') 
            AND CONVERT (DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(DAY,-1,@dtnow ))) + ''-'' + CONVERT(VARCHAR,MONTH(DATEADD(DAY,-1,@dtnow))) + ''-'' + CONVERT(VARCHAR, DAY(DATEADD(DAY,-1,@dtnow))) +'' 23:59:59.998'')
        )
    AND TblSalesRecords.IsDeleted=0

Hay otro error de sintaxis en (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate) pero no estoy seguro de qué debería estar allí, así que lo dejé tal como está.