split ONE record into multiple lines 1

[Jumroo]

can someone help me to split ONE record into multiple lines with the same batch_id if the service start date and service end date are more than 30 days apart.

For e.g the current data is in the format below
batch_id service start date service end date
99999 2/15/2008 4/25/2008

Need to be transformed to

batch_id service start date service end date
99999 2/15/2008 3/16/2008
99999 3/16/2008 4/15/2008
99999 4/15/2008 4/25/2008



appreciate any help!

 

[ESquared]

First, you might want to think about whether your start & end dates should be inclusive or exclusive. Customers tend to think in inclusive terms, so your exclusive end dates above will look confusing: to a customer it will seem that the ranges overlap by one day since they end and start on the same day! For example, 3/16/2008 looks like it is in two rows. Think in customer terms where a day is the WHOLE day rather than a datetime stamp at 12am. (Even then you have a datetime value shared between the rows.)

Also, it's good that you're going with 30 days because trying to do it by months would be quite difficult. You'd have to deal with leap years, what to do when the start date is Feb 28/29, or the start date is Jan 31. (Does the next period end on Feb 28 or Mar 2?)

SET NOCOUNT ON
CREATE TABLE Numbers (@Num int identity(1,1) NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED)
INSERT Numbers DEFAULT VALUES
WHILE Scope_Identity() < 1000 INSERT Numbers DEFAULT VALUES

SELECT
   *,
   ServiceStart = B.StartDate + (N.Num - 1) * 30,
   ServiceEnd = CASE WHEN B.EndDate <= B.StartDate + N.Num * 30 THEN B.EndDate ELSE B.StartDate + N.Num * 30 END
FROM
   BatchData B
   INNER JOIN Numbers N ON B.EndDate > (N.Num - 1) * 30 + B.StartDate + 1
ORDER BY
   blahid,
   N.Num

 

[Jumroo]

ooops...the dates should be like this.

batch_id service start date service end date
99999 2/15/2008 3/15/2008
99999 3/16/2008 4/15/2008
99999 4/16/2008 4/25/2008

just 30 days difference and dates cannot overlap.

Thanks

 

[Jumroo]

Thank you Esquared!!

can you help with the new query to have NON overlapping dates


like the e.g below

batch_id service start date service end date
99999 2/15/2008 3/15/2008
99999 3/16/2008 4/15/2008
99999 4/16/2008 4/25/2008

 

[ESquared]

SELECT
   *,
   ServiceStart = B.StartDate + (N.Num - 1) * 30,
   ServiceEnd = CASE WHEN EndDate < StartDate + N.Num * 30 - 1 THEN EndDate ELSE StartDate + N.Num * 30 - 1 END
FROM
   BatchData B
   INNER JOIN Numbers N ON EndDate >= (N.Num - 1) * 30 + StartDate
ORDER BY
   B.Batch_ID
   N.Num

All I did was subtract 1 from the service end date calculation and remove the +1 from the JOIN condition.

But be very careful. Both plces have to be correct for your input and output data. If the input is an exclusive time but the output should be an inclusive one, it will be different.