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á.