Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Add 5 days to a date

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Wed Jul 25, 2018 8:11 pm    Post subject: Add 5 days to a date
Reply with quote

Hello

I have a table with the next columns:
- date
- weekend-indication (value Y or N)
- holiday-indication (value Y or N)

I want to have all the dates of that table and also every date + 5 working days. A working day is a day where weekend-indication ans holiday-indication are N.
So I have to join the table with itself.

If this is my input
2018-01-01, weekend-indication N, holiday-indication N
2018-01-02, weekend-indication Y, holiday-indication N
2018-01-03, weekend-indication Y, holiday-indication N
2018-01-04, weekend-indication N, holiday-indication N
2018-01-05, weekend-indication N, holiday-indication N
2018-01-06, weekend-indication N, holiday-indication N
2018-01-07, weekend-indication N, holiday-indication Y
2018-01-08, weekend-indication N, holiday-indication Y
2018-01-09, weekend-indication Y, holiday-indication N
2018-01-10, weekend-indication Y, holiday-indication N
2018-01-11, weekend-indication N, holiday-indication N
2018-01-12, weekend-indication N, holiday-indication N

this would be my output for 2018-01-01:
2018-01-01, 2018-01-12

How do I do this?

Regards
Ron
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Wed Jul 25, 2018 9:07 pm    Post subject:
Reply with quote

Why below don't made it to your list?
Code:
2018-01-04, weekend-indication N, holiday-indication N
2018-01-05, weekend-indication N, holiday-indication N
2018-01-06, weekend-indication N, holiday-indication N
2018-01-04, weekend-indication N, holiday-indication N
2018-01-11, weekend-indication N, holiday-indication N


Please explain the requirements in much better way, its not clear as what you want exactly and make a use of code tags.
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10543
Location: italy

PostPosted: Wed Jul 25, 2018 9:15 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

Quote:
Please explain the requirements in much better way, its not clear as what you want exactly and make a use of code tags.


the requirement was/is more than clear ...

add FIVE working days to some date

what is not clear is the requirement to do it with a single query

adding working days to a date is something that has been around for at least
45 years or probably a bit more
and almost every organisation I knew had a callable routine to do it
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Wed Jul 25, 2018 9:24 pm    Post subject:
Reply with quote

Quote:
what is not clear is the requirement to do it with a single query
I have asked question already why other rows missed in the output.
Quote:
this would be my output for 2018-01-01:
2018-01-01, 2018-01-12
and how did 2018-01-12 date calculated?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Wed Jul 25, 2018 9:46 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

The things that are Not clear are:

1. Will you have an input date to the query? In the example, will you have '2018-01-01' as input?

2. The example seems to be only a part of the requirement since you want all the dates and then +5 working dates
Quote:
I want to have all the dates of that table and also every date + 5 working days


So, If I add one more row to your example as:
Code:
2018-01-13, weekend-indication N, holiday-indication N

Then, what'll be your output?

Will it be:
Code:
2018-01-01, 2018-01-12
2018-01-02, 2018-01-13
2018-01-03, 2018-01-13

OR will you still show 2018-01-12 as +5 date to 2018-01-02/03 ?
Code:
2018-01-01, 2018-01-12
2018-01-02, 2018-01-12
2018-01-03, 2018-01-12


3. Will you show all the dates even if there is No +5 date ?

4. Will this query limit to a Month/Year or just ALL THE DATES in the table?

.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Wed Jul 25, 2018 9:46 pm    Post subject:
Reply with quote

I am showing just a subset of our table.
As I said, in our table a working day is a day where weekend-indication and holiday-indication have a value of N.
So in my example 2018-01-04 won't make it, because:
- 2018-01-05 and 2018-01-06 are working days
- 2018-01-07 and 2018-01-08 are holidays
- 2018-01-09 and 2018-01-10 is weekend
- 2018-01-11 and 2018-01-12 are working days

so totally 4 working days. If I had added 2018-01-13 to the list (with both indicators N) I would have a extra row:
2018-01-04, 2018-01-13
But as I said , it is just a subset

In my opinion it should be possible in one query (with a correlated subquery maybe??)

Regards
Ron
Back to top
View user's profile Send private message
enrico-sorichetti

Senior Member


Joined: 14 Mar 2007
Posts: 10543
Location: italy

PostPosted: Wed Jul 25, 2018 9:46 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

Quote:
and how did 2018-01-12 date calculated?

by considering days to add those with
the weekend flag Y or the holiday flag Y
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Wed Jul 25, 2018 9:55 pm    Post subject:
Reply with quote

Hai RahulG31

my answers to your questions

1. yes
2. you are right, output will be the first one, so
2018-01-01, 2018-01-12
2018-01-02, 2018-01-13
2018-01-03, 2018-01-13
3. all the dates
4. all the dates in the table.

I want to built a temporary table (CTE: Common Table Expression) with rows that contain two attributes:
date and date + 5 working days

With a specific date I then want to select this row from this temporary table.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Thu Jul 26, 2018 3:02 am    Post subject: Reply to: Add 5 days to a date
Reply with quote

You'll probably need to use ROW_NUMBER() function of DB2 somewhere.

I haven't tested this but you can try something like:
Code:
​SELECT Y.D2, X.D1
FROM
   (  SELECT B.MYDATE D2,
        ROW_NUMBER() OVER(ORDER BY B.MYDATE ASC)  R2
       FROM MYTABLE B ) Y,
      (  SELECT A.MYDATE D1,
        ROW_NUMBER() OVER(ORDER BY A.MYDATE ASC) R1
       FROM MYTABLE A
      WHERE A.WEEKEND_IND = 'N'
           AND A.HOLIDAY_IND = 'N') X
 WHERE R1= R2 + 5;


I think this should give you a good sense on how to proceed.

Let me know if this works Or gets you closer to what you plan to achieve.

You can look more into ROW_NUMBER() here:
https://www.ibm.com/support/knowledgecenter/en/SSPT3X_2.1.2/com.ibm.swg.im.infosphere.biginsights.bigsql.doc/doc/bsql_row_number.html

.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Thu Jul 26, 2018 5:10 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

it says

SQLCODE = -206, ERROR: Y.D2 IS NOT VALID IN THE CONTEXT WHERE IT IS
USED

I tried to solve is, but I don't see it icon_sad.gif

Regards
Ron
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Thu Jul 26, 2018 6:53 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

Show the query you are trying to execute.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Thu Jul 26, 2018 7:19 pm    Post subject:
Reply with quote

---------+---------+---------+---------+---------+---------+---------+-
SELECT Y.D2, X.D1
FROM
( SELECT B.DAT,
ROW_NUMBER() OVER(ORDER BY B.DAT ASC) R2
FROM GWSDDBA5.gwtb403 B ) Y,
( SELECT A.DAT D1,
ROW_NUMBER() OVER(ORDER BY A.DAT ASC) R1
FROM GWSDDBA5.GWTB403 A
WHERE A.NED_WKNd_IND = 'N'
AND A.NED_FSTD_IND = 'N') X
WHERE R1 = R2 + 5;
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -206, ERROR: Y.D2 IS NOT VALID IN THE CONTEXT WHERE
USED
Code:
---------+---------+---------+---------+---------+---------+---------+-
   SELECT Y.D2, X.D1                                                   
   FROM                                                               
      (  SELECT B.DAT,                                                 
           ROW_NUMBER() OVER(ORDER BY B.DAT ASC)  R2                   
          FROM GWSDDBA5.gwtb403 B ) Y,                                 
         (  SELECT A.DAT D1,                                           
           ROW_NUMBER() OVER(ORDER BY A.DAT ASC) R1                   
          FROM GWSDDBA5.GWTB403 A                                     
         WHERE A.NED_WKNd_IND = 'N'                                   
              AND A.NED_FSTD_IND = 'N') X                             
    WHERE R1 = R2 + 5;                                                 
---------+---------+---------+---------+---------+---------+---------+-
DSNT408I SQLCODE = -206, ERROR:  Y.D2 IS NOT VALID IN THE CONTEXT WHERE
         USED
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 447
Location: USA

PostPosted: Thu Jul 26, 2018 7:58 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

You don't have a D2 anywhere in your query. You need:

SELECT B.DAT D2

.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2448
Location: @my desk

PostPosted: Thu Jul 26, 2018 7:59 pm    Post subject:
Reply with quote

I did not look at the whole sql, but just for the error - looks like you are missing a D2 in this line here
Code:
SELECT B.DAT D2
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Thu Jul 26, 2018 8:06 pm    Post subject:
Reply with quote

you were right. Runs now, but strange results. Will check it later


D2 D1
---------+---------+--
2018-05-27 2025-02-14
2018-05-28 2025-02-17
2018-05-29 2025-02-18
2018-05-30 2025-02-19
2018-05-31 2025-02-20
2018-06-01 2025-02-21
Code:
D2                D1       
---------+---------+--
2018-05-27  2025-02-14
2018-05-28  2025-02-17
2018-05-29  2025-02-18
2018-05-30  2025-02-19
2018-05-31  2025-02-20
2018-06-01  2025-02-21
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Fri Jul 27, 2018 3:19 am    Post subject:
Reply with quote

Try this,
Code:
 select temp.a, temp2.NXT_5_DT
   from table temp,
 (select max(temp1.a) as NXT_5_DT, 'A' as NXT_5_DT_F
   from table temp1
  where temp1.a > '2018-01-01'
    and temp1.b = 'N'
    and temp1.c = 'N'
   fetch first 5 rows only) temp2
where temp.a = '2018-01-01'
          'A'= temp2.NXT_5_DT_F
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2126
Location: UK

PostPosted: Fri Jul 27, 2018 1:43 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

Please use the code tags when presenting code/data/anything requiring spacing to be maintained. I have coded your last 2 posts in such a way that you can see the difference.
Back to top
View user's profile Send private message
Ron Klop

New User


Joined: 28 Sep 2012
Posts: 24
Location: holland

PostPosted: Fri Jul 27, 2018 4:45 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

select X.dat, Y.NXT_5_DT
from gwsddba5.gwtb403 X ,
(select max(a.dat) as NXT_5_DT, 'A' as NXT_5_DT_F
from gwsddba5.gwtb403 a
where a.dat > '2018-01-01'
and A.NED_WKNd_IND = 'N'
AND A.NED_FSTD_IND = 'N'
group by a.dat
order by a.dat asc
fetch first 5 rows only) Y
where x.dat = '2018-01-01'
and 'A'= Y.NXT_5_DT_F
;
---------+---------+---------+---------+---------+-----
DAT NXT_5_DT
---------+---------+---------+---------+---------+-----
2018-01-01 2018-01-02
2018-01-01 2018-01-03
2018-01-01 2018-01-04
2018-01-01 2018-01-05
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2064
Location: NY,USA

PostPosted: Fri Jul 27, 2018 4:50 pm    Post subject:
Reply with quote

Why did you add group by and order by ?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2126
Location: UK

PostPosted: Fri Jul 27, 2018 7:44 pm    Post subject: Reply to: Add 5 days to a date
Reply with quote

If you cannot be bothered to present your data properly (using code tags) why should anyone be bothered to help you? Topic locked (and unlocked again).
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Process file after 7 days of hold charlessxavier All Other Mainframe Topics 4 Tue May 22, 2018 3:54 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us