Add 5 days to a date

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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

Rohit Umarjikar · Posted: Wed Jul 25, 2018 9:07 pm

Why below don't made it to your list?

enrico-sorichetti · Posted: Wed Jul 25, 2018 9:15 pm

Rohit Umarjikar · Posted: Wed Jul 25, 2018 9:24 pm

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

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

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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

enrico-sorichetti · Posted: Wed Jul 25, 2018 9:46 pm

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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.

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

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

I haven't tested this but you can try something like:

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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

Regards
Ron

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

Show the query you are trying to execute.

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

---------+---------+---------+---------+---------+---------+---------+-
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

RahulG31 · Active User Joined: 20 Dec 2014 Posts: 446 Location: USA

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

SELECT B.DAT D2

.

Arun Raj · Posted: Thu Jul 26, 2018 7:59 pm

I did not look at the whole sql, but just for the error - looks like you are missing a D2 in this line here

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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

Rohit Umarjikar · Posted: Fri Jul 27, 2018 3:19 am

Try this,

Nic Clouston · Posted: Fri Jul 27, 2018 1:43 pm

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.

Ron Klop · New User Joined: 28 Sep 2012 Posts: 28 Location: holland

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

Rohit Umarjikar · Posted: Fri Jul 27, 2018 4:50 pm

Why did you add group by and order by ?

Nic Clouston · Posted: Fri Jul 27, 2018 7:44 pm

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).