CODEWITHSUNDEEP
db2
Steven
Steven

Reputation: 33

DB2: Bi-monthly query for a DB2 report

I am currently writing a Crystal Report that has a DB2 query as its backend. I have finished the query but am stuck on the date portion of it. I am going to be running it twice a month - once on the 16th, and once on the 1st of the next month. Here's how it should work:

This comes down a basic bi-monthly report. I've found plenty of hints to do this in T-SQL, but no efficient ways on how to accomplish this in DB2. I'm having a hard time wrapping my head around the logic to get this to consistently work, taking into account differences in month lengths and such.

Upvotes: 0

Views: 88

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12314

There are 2 expressions for start and end date of an interval depending on the report date passed, which you may use in your where clause.
The logic is as follows:
1) If the report date is the 1-st day of a month, then:
DATE_START is 16-th of the previous month
DATE_END is the last day of the previous month
2) Otherwise:
DATE_START is 1-st of the current month
DATE_END is 15-th of the current month

SELECT
  REPORT_DATE
, CASE DAY(REPORT_DATE) WHEN 1 THEN REPORT_DATE - 1 MONTH + 15 ELSE REPORT_DATE - DAY(REPORT_DATE) + 1  END AS DATE_START
, CASE DAY(REPORT_DATE) WHEN 1 THEN REPORT_DATE - 1            ELSE REPORT_DATE - DAY(REPORT_DATE) + 15 END AS DATE_END
FROM 
(
VALUES 
  DATE('2020-02-01')
, DATE('2020-02-05')
, DATE('2020-02-16')
) T (REPORT_DATE);

The result is:

|REPORT_DATE|DATE_START|DATE_END  |
|-----------|----------|----------|
|2020-02-01 |2020-01-16|2020-01-31|
|2020-02-05 |2020-02-01|2020-02-15|
|2020-02-16 |2020-02-01|2020-02-15|

Upvotes: 1

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

In Db2 (for Unix, Linux and Windows) it could be a WHERE Condition like

WHERE
 (CASE WHEN date_part('days', CURRENT date) > 15 THEN yourdatecolum >= this_month(CURRENT date) AND yourdatecolum < this_month(CURRENT date) + 15 days
  ELSE  yourdatecolum > this_month(CURRENT date) - 1 month + 15 DAYS AND yourdatecolum < this_month(CURRENT date)
  END)

Check out the THIS_MONTH function - there are multiple ways to do it. Also DAYS_TO_END_OF_MONTH might be helpful

Upvotes: 0

Related Questions