Toad World® Forums

date question


#1

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.


#2

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


#3

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?


#4

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.


#5

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.


#6

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.


#7

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