We help IT Professionals succeed at work.
Get Started

Insert missing dates between two rows, but use quantity/value from previous date to populate those 'in between' dates.

angelnjj
angelnjj asked
on
258 Views
Last Modified: 2012-05-11
I closed this, but it is me again...I didn't notice, but in this code the dates between the changedates should be picking up the blocked rooms from the changedate before.  
Another data sample may help:

changedate      change_alldates      roomdate      blockedrooms
2011-02-22      2011-02-22      2011-05-16      70
2011-02-22      2011-02-22      2011-05-17      70
2011-02-24      2011-02-23      2011-05-15      40
2011-02-24      2011-02-23      2011-05-16      55
2011-02-24      2011-02-23      2011-05-17      60
2011-02-24      2011-02-23      2011-05-18      3
2011-02-24      2011-02-24      2011-05-15      40
2011-02-24      2011-02-24      2011-05-16      55
2011-02-24      2011-02-24      2011-05-17      60
2011-02-24      2011-02-24      2011-05-18      3
2011-03-08       2011-02-25      2011-05-15      44
2011-03-08       2011-02-25      2011-05-16      63
2011-03-08       2011-02-25      2011-05-17      63
2011-03-08       2011-02-25      2011-05-18      3
2011-03-08      2011-02-26      2011-05-15      44
2011-03-08      2011-02-26      2011-05-16      63
2011-03-08      2011-02-26      2011-05-17      63
2011-03-08      2011-02-26      2011-05-18      3

change_alldates represents the 'dates between' change dates...which is working perfect.  Problem is that the blocked rooms should repeat up from the previous change_date up to when the date actually changes.  So...would look like this:

changedate      change_alldates roomdate                           blockedrooms
2011-02-22      2011-02-22            2011-05-16      70
2011-02-22      2011-02-22            2011-05-17      70
2011-02-24      2011-02-23            2011-05-15      70
2011-02-24      2011-02-23            2011-05-16      70
2011-02-24      2011-02-24            2011-05-15      40
2011-02-24      2011-02-24            2011-05-16      55
2011-02-24      2011-02-24            2011-05-17      60
2011-02-24      2011-02-24            2011-05-18      3
2011-03-08       2011-02-25            2011-05-15      40
2011-03-08       2011-02-25            2011-05-16      55
2011-03-08       2011-02-25            2011-05-17      60
2011-03-08       2011-02-25            2011-05-18      3
2011-03-08      2011-02-26            2011-05-15      40
2011-03-08      2011-02-26            2011-05-16      55
2011-03-08      2011-02-26            2011-05-17      60
2011-03-08      2011-02-26            2011-05-18      3

It wouldn't be until change_alldates was 2011-03-08 and changedate = 2011-03-08 as well that the blocked rooms would change to:
2011-03-08      2011-03-08            2011-05-15      43
2011-03-08      2011-03-08            2011-05-16      63
2011-03-08      2011-03-08            2011-05-17      63
2011-03-08      2011-03-08            2011-05-18      3

The code I'm using:
with numbers as (select row_number() over (order by name) - 1 as n from sys.all_objects)
select   propertypid, bookingid, postas, changedate, changedate - isnull(n, 0) as change_alldates, roomdate, blockedrooms, ispickupcomplete, pickupdate, statusid    
	from
     (select   propertypid, bookingid, postas, changedate, roomdate, blockedrooms, ispickupcomplete, pickupdate, statusid, isnull(max(prevdate), changedate) as prevdate /*anjj*/
                  from (select a.propertypid, a.bookingid, a.postas, a.changedate, a.roomdate, a.blockedrooms, a.ispickupcomplete, a.pickupdate, a.statusid
							, b.changedate as prevdate 
							from #guestroominfo as a
                               left outer join #guestroominfo as b on a.propertypid = b.propertypid and a.changedate > b.changedate and a.bookingid = b.bookingid
							) as x
              group by propertypid, bookingid, postas, changedate, roomdate, blockedrooms, ispickupcomplete, pickupdate, statusid
		) as a
         left join numbers on numbers.n < datediff(d, prevdate, changedate)
order by propertypid, change_alldates, changedate, roomdate

Open in new window


I don't understand how to get it to enter the dates between, but use the rooms from the prevdate.  Do I need to do this in two steps?  One to gather the dates between and another to assign the blockedrooms for the date?
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 2 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE