date question

I am trying to do a date calc that has me confused. i want to use a date in the past to as my starting point to get the most recent full 12 mont period

3/1/2003 = 3/1/2017 - 3/1/2018

6/1/2005 = 6/1/2016 - 6/1/2017

does this make sense? any help appreciated.

Hello,

Does exist any rule on how to compute the date? I don’t see any for these 2 examples. If there is no rule, you would need to implement it in the code manually or save combinations to a table and load related dates from the table when necessary.

Regards
Ondrej

I am confused. Can you explain in greater detail? I dont understand how you are getting 3/1/2003 when you subtract 3/1/2017 and 3/1/2018. What is the formula you are using? Also are you using z/OS or LUW?

I am trying to get the most current 12 months of data based on a start date. so in this instance the start date is 3/1/2003. The most current 12 months would be from 3/1/2017 – 3/1/2018. The next record has a start date of 6/4/2006 … the most current full 12 months based on that date would 6/4/2016 -6/4/2017… does that help?

From: Db2Developer [mailto:bounce-Db2Developer@toadworld.com]

Sent: May 22, 2018 8:30

To: toaddb2@toadworld.com

Subject: RE: [Toad for IBM DB2 - Discussion Forum] date question

RE: date question

Reply by Db2Developer

I am confused. Can you explain in greater detail? I dont understand how you are getting 3/1/2003 when you subtract 3/1/2017 and 3/1/2018. What is the formula you are using? Also are you using z/OS or LUW?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for IBM DB2 Forum
notifications altogether.

Toad for IBM DB2 - Discussion Forum

Flag
this post as spam/abuse.


DJ Kirby

Business Analyst

Bay and Bay Transportation

dkirby@bayandbay.com

Tel: 651-480-4912

Fax: 651-480-7996

Cell:612-325-4362

www.bayandbay.com


This message is private and confidential. If you have received this message in error, please notify us and remove it from your system.

Ah now I follow. I will try to write something up for you. Seems like pretty simple SQL. Most likely just put the SQL into a function and call that function. Let me get back to you shortly.

Are you using Db2 for z/OS or LUW? There is different functionality between them.

This makes absolutely no sense. What is the correlation between the start date and the most current 12 months of data. Until you provide that I don’t know how the question can be answered.

How does this work? Not sure about performance, but I think its what you want.

with cte_dates as
(
Select cast(‘5/22/2018’ as date) as start_date
from sysibm.sysdummy1

union all

Select cast(‘3/1/2003’ as date) as start_date
from sysibm.sysdummy1

union all

Select cast(‘6/4/2006’ as date) as start_date
from sysibm.sysdummy1
)
Select a.start_date,
case
–last year
when month(a.start_datE) <= month(current timestamp)
then month(a.start_date)
|| ‘/1/’
|| cast(cast(year(current timestamp)as int) -1 as char(4))
–2 years ago
else
month(a.start_date)
|| ‘/1/’
|| cast(cast(year(current timestamp)as int) -2 as char(4))
end as begin_date,
case
–current year
when month(a.start_datE) <= month(current timestamp)
then month(a.start_date)
|| ‘/1/’
|| year(current timestamp)
–last year
else
month(a.start_date)
|| ‘/1/’
|| cast(cast(year(current timestamp)as int) -1 as char(4))
end as end_date
from cte_dates a

7840.toad.png