CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
texas_mike81
texas_mike81

Reputation: 71

Conditional Where Clause Based on Sysdate?

I'm creating a query that will be run twice a month:

  1. On the 5th of the month looking at billing days 16-last day of the month of the previous month.

    datepart(dd,h.BILLED_DATE) > 15    
DATEPART(mm, h.billed_date) = DATEPART(mm,dateadd(m,-1,getdate()))
and DATEPART(yyyy, h.billed_date) = DATEPART(yyyy,dateadd(m,-1,getdate()))

  2. On the 20th of the month looking at billing days of 1-15 of the same month.

    datepart(dd,h.BILLED_DATE) >= 1
and datepart(dd,h.BILLED_DATE) < 16
and DATEPART(mm, h.BILLED_DATE) = DATEPART(mm,GETDATE())
and DATEPART(yyyy, h.BILLED_DATE) = DATEPART(yyyy,GETDATE()))

These work independently in the where clause. When I try to combine them in a case statement in the where clause, however, I'm getting errors galore, starting with the first inequality after the "then."

Where
  Case 
    when datepart(dd,getdate()) > 15 
      then [2 above]
      else [1 above]
  End

Cure my ignorance.

Upvotes: 0

Views: 1434

Answers (2)

anon
anon

Reputation:

CASE is an expression that returns a single value. You can't use it for control of flow like you can in some other languages.

Your query also will not make any usage of indexes on your billed_date column, which you should consider adding to support this query.

How about this re-write, which uses pure datetime operations to build an open-ended range instead of performing all those expensive datepart functions:

DECLARE @today SMALLDATETIME, @start SMALLDATETIME, @end SMALLDATETIME;
SET @today = DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));

SET @start = DATEADD(DAY, 1-DAY(@today), @today);
SET @end = DATEADD(DAY, 15, @start);

IF DATEPART(DAY, @today) <= 15
BEGIN
    SET @start = DATEADD(MONTH, -1, @end);
    SET @end = DATEADD(DAY, 1-DAY(@today), @today);
END 

SELECT ... WHERE h.billed_date >= @start AND h.billed_date < @end;

Upvotes: 3

bluevector
bluevector

Reputation: 3493

This should do it

WHERE (
    datepart(dd,getdate()) > 15 
    AND DATEPART(mm, enc.hosp_admsn_time) = DATEPART(mm,dateadd(m,-1,getdate()))
    AND DATEPART(yyyy, enc.hosp_admsn_time) = DATEPART(yyyy,dateadd(m,-1,getdate()))
) OR (
  datepart(dd,h.BILLED_DATE) >= 1
  and datepart(dd,h.BILLED_DATE) < 16
  and DATEPART(mm, h.BILLED_DATE) = DATEPART(mm,GETDATE())
  and DATEPART(yyyy, h.BILLED_DATE) = DATEPART(yyyy,GETDATE()))
)

Upvotes: 0

Related Questions