CODEWITHSUNDEEP
sqldatedb2ibm-midrange
William
William

Reputation: 415

Pulling a dynamic day range from the previous year in DB2

I have two current SQL queries that I currently use to compare GM% from previous year vs. GM% this year. This is a daily report that I run every morning. The date arithmetic is not very solid and I am trying to find an alternative. Previously I thought that the report would only be for Monday forward, and not including the current day (ie if ran on Tuesday, it would only pull Monday. If ran on Monday it would not pull anything.) Recently that has changed to where when the report is ran on Monday, they want to see Friday-Sunday. What I am considering is setting it to pull the previous 5 day, not including the current day. (Ran on Monday would pull Thur, Fri, Sat, Sun.) The problem is that it has to be a day this year vs same day last year comparison. Anyone who;s tried this knows that it is not easy to get this. Here is my current code for the date arithmetic. I am at a loss guys, I could use some help.

Where DB1.TB1.CLM1>=Current Date-364 days - (DAYOFWEEK(CURRENT DATE) - 2) DAYS 
      And DB1.TB1.CLM1< Current Date- 364 days

Upvotes: 1

Views: 1634

Answers (2)

Christian Polychroniadis
Christian Polychroniadis

Reputation: 1

Use the function YEAR to extract only that part if that's the only things that it is dynamic.

BETWEEN YEAR(current date)-1 || '-01-01' AND YEAR(current date)+1 || '-12-31'

Upvotes: 0

ExactaBox
ExactaBox

Reputation: 3395

If I hear you right, on Tuesday you would pull stats for Monday. Wed, you pull stats for Mon-Tues. Friday, you pull Mon-Thurs. And for all of these, you need the equivalent day prior year.

The trick is that now on Monday, you need to pull the previous weekend, i.e. Thu-Sun.

You have not defined what to do on Sunday, so I'm leaving that case out.

Try this WHERE statement:

where
   ( -- do this after Monday
   dayofweek(current date) > 2 and
   DB1.TB1.CLM1 between ((current date - 364 days) - (dayofweek(current date) - 2) days) and (current_date - 365 days)
   ) 
   or
   ( -- do this on Monday
   dayofweek(current date) = 2 and
   DB1.TB1.CLM1 between (current date - 368 days) and (current date - 365 days)
   )

Upvotes: 2

Related Questions