Link to home
Start Free TrialLog in
Avatar of MJ
MJ

asked on

Multiply row into multiple rows in SQL Server

In SQL Server, I have a row that has 3 columns.  1. StartDate, 2. EndDate and 3. TotalOwed.  Example: StartDate=Jan 1, 2020, EndDate = Jan 2, 2020, TotalOwed = $24.  

I need a way to break that single row (above) into mutiple rows.  In this example, it would be creating one new row for every hour (between startdate and enddate)

Since the span between startdate and enddate is 24 hours, then you would take $24 and divide by 24 hours, so that would give you $1 per hour.  

The final result would be 24 records, with a startdate and end date in one hour increments.  The result rows would look like this.  

StartDate Jan 1, 2020 12:00 am, EndDate, Jan 1 2020 1:00 am, TotalOwed = $1
StartDate Jan 1, 2020 1:00 am, EndDate, Jan 1 2020 2:00 am, TotalOwed = $1
StartDate Jan 1, 2020 3:00 am, EndDate, Jan 1 2020 3:00 am, TotalOwed = $1
StartDate Jan 1, 2020 4:00 am, EndDate, Jan 1 2020 5:00 am, TotalOwed = $1
... and so on, until I had 24 rows total.  

The start and end dates may be days or half a day.  It could be any number of hours.  

Is there a more optimal way of doing this, rather than creating a cursor that cycles through the data?  

Thanks.
Avatar of arnold
arnold
Flag of United States of America image

cursor with a function that iterates and inserts from start to end.
Avatar of MJ
MJ

ASKER

My question was, "a more optimal way, rather than creating a cursor"

You might be able to do this with a recursive CTE:

https://www.codeproject.com/Questions/1260562/Create-a-date-range-between-two-dates-in-SQL-serve


Cannot say if this will be more efficient.  Only possible.


>>The start and end dates may be days or half a day.


The example data you provided shows whole days.  Can we assume the value can include times as well?  Will they always be in the exact same format?


Like:

StartDate=Jan 1, 2020 1:00 am, EndDate = Jan 1, 2020 1:45 am, TotalOwed = $24


How do you want to handle rounding errors when it comes to pennies?

My question would be why?
Why add rows unnecessarily if you want to compute partial, you can extract and use datediff .....
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial