Link to home
Start Free TrialLog in
Avatar of PresidentLincoln
PresidentLincolnFlag for United States of America

asked on

DB2: Help creating a query that must run bi-monthly.

I am writing a DB2 query that will be run bi-monthly - once on the 16th of the month and once on the 1st of the following month:

  • When the report runs on the 16th of the month, it should pull all data from the 1st to the 15th of that same month.
  • When the report runs on the 1st of the following month, it should pull all data from the 16th of the previous month to the last day of the previous month.

I've found examples giving hints on how to accomplish this in T-SQL, but not necessarily in DB2.  I know a lot of T-SQL date functions don't translate well.  Can anyone assist with this piece of code?  Thanks.
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Which specific version of DB2: Unix/Linux/AIX, Windows, Z/OS, iSeries, etc.?  Sadly, even for something as simple as working with dates, there can be differences depending on the specific db engine version.


Avatar of PresidentLincoln

ASKER

Scott, I'm using IBM Data Studio 4.1.3 for an AIX DB.  If you need any more versioning info after that, let me know.

Please try this and see if the syntax is valid.  DB2's kind of a loner on allowing this type of (easy) syntax, so I'm not super-familiar with it.


SELECT ...

FROM ...

WHERE

    ((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 16 AND

        (column_date BETWEEN CURRENT DATE - 15 DAYS AND CURRENT DATE - 1 DAY)) OR

    ((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 1 AND

        (column_date BETWEEN  CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY))



ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Scott.  I needed a quick clarification - is "column_date" going to be the column that I want to specify the dates for?  If so, that column is a timestamp.  Will I need to convert it to a date?
Bill,

I run your version of the query and it runs with no syntax errors, but it returns 0 rows when I know it should have at least 10+.

Edit:  I believe I see why.  There's no logic in it to return anything if you run it for any days other than the 16th or the 1st.
Avatar of Bill Prew
Bill Prew

Right, Scott assumed based on your original post that the code would only run on the 1st and the 16th of the month.


»bp
For testing you may have to replace "CURRENT DATE" with a date variable that you set to say the 1st or 16th of a month for testing...


»bp

For testing today, just change one of them to "= 4" rather than "= 16" or "= 1" :-).

Just keep in mind that will change the selected data slightly since the calcs assumed 1 and 16 as the run days.

So changing

(DAY(CURRENT DATE) = 1 AND column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY)

to

(DAY(CURRENT DATE) = 4 AND column_date BETWEEN CURRENT DATE - 1 MONTH + 15 DAYS AND CURRENT DATE - 1 DAY)

will now select rows between 1/19/2020 and 2/3/2020.  It will still validate that this approach works, just be prepared for the dates to be skewed a bit in the test...


»bp
@Bill, your original suggestion worked like a charm.  Thanks.

@Scott, thanks for setting us on the right path.  Much appreciated.
Welcome, glad we could help.


»bp

Could:
((SELECT DAY(CURRENT DATE) AS current_date FROM sysibm.sysdummy1) = 16 AND
Just be:
(DAY(CURRENT DATE) = 16 AND


Maybe, I wasn't sure.  Again, DB2 syntax is rather quirky at times.