Insert missing dates between two rows, but use quantity/value from previous date to populate those 'in between' dates.
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:
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:
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
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?
What will be the performance of hit if I use the quirky update (which I had to lookup... http://www.sqlservercentral.com/articles/T-SQL/68467/ ...is it the same you're talking about?). This is just one 'client'...I've got @50000 'clients' I need to go through to split their individual transaction history into day by day transactions.
I'm working on updating to use quirky update once I get this morning's weekend 'fires' under control...thanks.
Please see the 'Close Request Pending' box at the top of the thread for point allocation details.
Thank you,
_alias99
Community Support Moderator
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
ASKER
so last date whenever I run would be getdate() and have the blockedroom count from the most recent changedate. Does that make sense?
Thank you!