question

sagar 2 avatar image
sagar 2 asked

retrieving dates from 2 sets of dates

Hello, I am using IBM DB2, I have a table which gives me following data: START_DATE/END_DATE/LEAVE_NAME 08-01-2011/08-03-2011/CASUAL LEAVE I want the output as: START_DATE/END_DATE/LEAVE_NAME/date 08-01-2011/08-03-2011/CASUAL LEAVE/08-01-2011 08-01-2011/08-03-2011/CASUAL LEAVE/08-02-2011 08-01-2011/08-03-2011/CASUAL LEAVE/08-03-2011 I want one extra date column which gives me date from the start and end date. The StartDate and EndDate column are of "date" datatype. Plz help!! I m using IBM DB2.
db2
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
Whilst @Håkan Winther is right in that this is a MS-SQL Server site, maybe we can still point you in the right direction for the solution. What you need to do is expand the days between start and finish. This can be done using a numbers or tally table. I have managed this through the use of a common table expression (the code starting with "WITH"). This just generates a list of integers, starting with 0. I then multiply the original data using the inner join, driving the join by calculating the difference in days between the start and end of leave entry. In your example the leave is 3 days, so three rows are generated. I then create a final column that turns the row number into the actual date that the row represents. Take a look at the code, I have no idea how it would translate to DB2, but I think the general idea should be translatable. DECLARE @TestTable AS TABLE (START_DATE datetime, END_DATE datetime, LEAVE_NAME varchar(20)) INSERT INTO @TestTable (START_DATE, END_DATE, LEAVE_NAME) VALUES ('2011-08-01', '2011-08-03', 'Casual Leave') ; WITH nums AS (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS N FROM sys.all_columns AC CROSS JOIN sys.all_columns AC2) SELECT START_DATE, END_DATE, LEAVE_NAME, DATEADD(DAY, n, START_DATE) AS CurrDate FROM @TestTable TT INNER JOIN nums ON nums.n
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You're right - DB2 is a mystery to us all :)
1 Like 1 ·
+1 for the SQL Server solution. :-)
0 Likes 0 ·
Thanks @Pavel! It should be portable, not in the sense of copy-paste, but the general idea should work.
0 Likes 0 ·
+1. But I would use a #temp table instead of a CTE, to be able to add a clustered index on the tally table. That makes quite a difference to the performance.
0 Likes 0 ·
@WilliamD:: Thank you very much
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.