x

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.
more ▼

asked Aug 09, 2011 at 01:36 AM in Default

sagar 2 gravatar image

sagar 2
111 6 8 9

have you tried www.stackoverflow.com
Aug 09, 2011 at 01:49 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 <= DATEDIFF(day, tt.START_DATE, tt.END_DATE)
more ▼

answered Aug 09, 2011 at 02:01 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

+1 for the SQL Server solution. :-)
Aug 09, 2011 at 02:05 AM Pavel Pawlowski
Thanks @Pavel! It should be portable, not in the sense of copy-paste, but the general idea should work.
Aug 09, 2011 at 02:18 AM WilliamD
+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.
Aug 09, 2011 at 02:22 AM Magnus Ahlkvist
@WilliamD:: Thank you very much
Aug 09, 2011 at 02:25 AM sagar 2
You're right - DB2 is a mystery to us all :)
Aug 09, 2011 at 02:32 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x19

asked: Aug 09, 2011 at 01:36 AM

Seen: 609 times

Last Updated: Aug 09, 2011 at 01:36 AM