Select Every Date for Date Range and Insert

Question

Using SQL Server 2008
I have a table A which has start date, end date and value. For each date within the start date and end date in Table A, I need to insert (or update if already exists) that date in table B such that the value in this table is value in A/DateDiff(Day,StartDate of A,EndDate of A).

Example:

Table A

ID    StartDate        EndDate           Value    
1     01 Jan 2014      03 Jan 2014       33
2     01 Feb 2014      02 Feb 2014       20
3     02 Jan 2014      03 Jan 2014       10

Table B

ID    Date            Value
1     01 Jan 2014     11
2     02 Jan 2014     16
3     03 Jan 2014     16
4     01 Feb 2014     10
5     02 Feb 2014     10

The way values are computed are - For ID 1, there are 3 days which means 11 units per day. So 1st, 2nd, 3rd Jan all get 11 units. Then because there are additional units with date range 2nd Jan to 3rd Jan which amount to 5 units per day, 2nd and 3rd Jan will be (11+5) 16. 1st and 2nd Feb just have one record so they will simply be 20/2 = 10.

I can think of a solution using loops, but want to avoid it entirely. Is there any way I can achieve this through a set based solution? It is important for me to do this in bulk using set based approach.

I am trying to read through various articles and seems like CTE, Calendar Table or Tally Table might help but the examples I have seen require setting variables and passing start date and end date which I think will work for single record but not when doing all records at a time. Please suggest.

Thanks!

Your values don't make sense. Shouldn't they be 11, 16, 33, 10, 20? — Gordon Linoff
The way values are computed are - For ID 1, there are 3 days which means 11 units per day. So 1st, 2nd, 3rd Jan all get 11 units. Then because there are additional units with date range 2nd Jan to 3rd Jan which amount to 5 per day, 2nd and 3rd Jan will be 16. 1st and 2nd Feb just have one record so they will simply be 20/2 = 10. — devSuper
. . I'm tempted to say that you should delete this question and start over with the description. — Gordon Linoff

mellamokb mellamokb · Accepted Answer · 2014-03-22T22:21:31

I think this should do it (DEMO):

;with cte as (
  select
     id
    ,startdate
    ,enddate
    ,value / (1+datediff(day, startdate, enddate)) as value
    ,startdate as date
  from units
  union all
  select id, startdate, enddate, value, date+1 as date
  from cte
  where date < enddate
)
select
   row_number() over (order by date) as ID
  ,date
  ,sum(value) as value
from cte
group by date

The idea is to use a Recursive CTE to explode the date ranges into one record per day. Also, the logic of value / (1+datediff(day, startdate, enddate)) distributes the total value evenly over the number of days in each range. Finally, we group by day and sum together all the values corresponding to that day to get the output:

| ID |                            DATE | VALUE |
|----|---------------------------------|-------|
|  1 |  January, 01 2014 00:00:00+0000 |    11 |
|  2 |  January, 02 2014 00:00:00+0000 |    16 |
|  3 |  January, 03 2014 00:00:00+0000 |    16 |
|  4 | February, 01 2014 00:00:00+0000 |    10 |
|  5 | February, 02 2014 00:00:00+0000 |    10 |

From here you can join with your result table (Table B) by date, and update/insert the value as needed. That logic might look something like this (test it first of course before running in production!):

update B set B.VALUE = R.VALUE from TableB B join Result R on B.DATE = R.DATE
insert TableB (DATE, VALUE)
  select DATE, VALUE from Result R where R.DATE not in (select DATE from TableB)

Select Every Date for Date Range and Insert

1 Answers