troubleshooting Question

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

Avatar of angelnjj
angelnjjFlag for United States of America asked on
SQL
9 Comments2 Solutions259 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
angelnjj

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros