Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.
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:
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)
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?
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
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
also...I need the change_alldates to extend to today's date...
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!
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!
Hi,
create a numbers table or tally table that has more rows than the maximum date range in days. I note that you are using row_number() function.
do a left outer join and insert blank missing rows.
Use the "quirky update" to copy the values down into the new blank rows.
Regards
David
create a numbers table or tally table that has more rows than the maximum date range in days. I note that you are using row_number() function.
do a left outer join and insert blank missing rows.
Use the "quirky update" to copy the values down into the new blank rows.
Regards
David
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.
I'm working on updating to use quirky update once I get this morning's weekend 'fires' under control...thanks.
I found another solution use cte without use of quirky update that is working perfectly...
For my own reference later: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147942 (answer by PavanKK
declare @tab table
(propertypid int,
bookingid int,
blockedrooms int,
changedate datetime,
roomdate datetime)
insert into @tab (propertypid, bookingid, blockedrooms, changedate, roomdate)
select propertypid, bookingid, blockedrooms, changedate, roomdate from omni_weedpace_testing
;with cteall as
(select propertypid, bookingid, min(changedate) changedate, max(changedate) maxchangedate
from @tab
group by propertypid, bookingid
union all
select c.propertypid, c.bookingid, dateadd(dd,1,c.changedate)
from cteall c
where dateadd(dd,1,c.changedate)
ctemissing as
(select c.propertypid, c.bookingid, t.blockedrooms,c.changedat
from cteall c
cross join @tab t
where c.bookingid = t.bookingid
and datediff(d,t.changedate,c.
insert into @tab
select c.propertypid, c.bookingid, c.blockedrooms,c.changedat
from ctemissing c
where not exists ( select 1 from @tab where bookingid = c.bookingid and changedate = c.changedate )
and c.diff = ( select min(i.diff) from ctemissing i where c.bookingid = i.bookingid and c.changedate = i.changedate )
order by bookingid
select * from @tab order by bookingid, changedate
For my own reference later: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147942 (answer by PavanKK
declare @tab table
(propertypid int,
bookingid int,
blockedrooms int,
changedate datetime,
roomdate datetime)
insert into @tab (propertypid, bookingid, blockedrooms, changedate, roomdate)
select propertypid, bookingid, blockedrooms, changedate, roomdate from omni_weedpace_testing
;with cteall as
(select propertypid, bookingid, min(changedate) changedate, max(changedate) maxchangedate
from @tab
group by propertypid, bookingid
union all
select c.propertypid, c.bookingid, dateadd(dd,1,c.changedate)
from cteall c
where dateadd(dd,1,c.changedate)
ctemissing as
(select c.propertypid, c.bookingid, t.blockedrooms,c.changedat
from cteall c
cross join @tab t
where c.bookingid = t.bookingid
and datediff(d,t.changedate,c.
insert into @tab
select c.propertypid, c.bookingid, c.blockedrooms,c.changedat
from ctemissing c
where not exists ( select 1 from @tab where bookingid = c.bookingid and changedate = c.changedate )
and c.diff = ( select min(i.diff) from ctemissing i where c.bookingid = i.bookingid and c.changedate = i.changedate )
order by bookingid
select * from @tab order by bookingid, changedate
Hi,
Yes you found the article I was referring to, the one by Jeff Moden.
The quirky update is very fast, compared to other solutions in that space of using cursors or while loops.
If you look at the execution plan, I think you'll find that the select 1 from @tab where bookingid ...) runs once per row in the select ... from ctemissing c. That isn't particularly fast.
If you read the article, it shows calculating the running balance for all transactions over all accounts in ONE clustered index update.
Anyway, I'm glad you have found a solution.
BTW my implimentation of the quirky update, I've found that I have to loop through the 'accounts' - fortunately only 200 or so due to other factors such as having to ignore some, and find other data from the 'account' table, and Jeff emphasised No joins. It still works better than having to loop over the transaction rows.
Regards
David
Yes you found the article I was referring to, the one by Jeff Moden.
The quirky update is very fast, compared to other solutions in that space of using cursors or while loops.
If you look at the execution plan, I think you'll find that the select 1 from @tab where bookingid ...) runs once per row in the select ... from ctemissing c. That isn't particularly fast.
If you read the article, it shows calculating the running balance for all transactions over all accounts in ONE clustered index update.
Anyway, I'm glad you have found a solution.
BTW my implimentation of the quirky update, I've found that I have to loop through the 'accounts' - fortunately only 200 or so due to other factors such as having to ignore some, and find other data from the 'account' table, and Jeff emphasised No joins. It still works better than having to loop over the transaction rows.
Regards
David
The correct solution selection should be
angelnjj's comment http:#35771743 (0 points) -- Solution
dtodd's comment http:#35771986 (500 points) -- assisted solution
angelnjj's comment http:#35771743 (0 points) -- Solution
dtodd's comment http:#35771986 (500 points) -- assisted solution
All,
Following an 'Objection' by aikimark (at http://www.experts-exchange.com/Q_27041769.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as originally requested by the author (accepting the Author's answer as the solution and awarding full points to an Expert comment for an assisted answer.
Please see the 'Close Request Pending' box at the top of the thread for point allocation details.
Thank you,
_alias99
Community Support Moderator
Following an 'Objection' by aikimark (at http://www.experts-exchange.com/Q_27041769.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as originally requested by the author (accepting the Author's answer as the solution and awarding full points to an Expert comment for an assisted answer.
Please see the 'Close Request Pending' box at the top of the thread for point allocation details.
Thank you,
_alias99
Community Support Moderator
Premium Content
You need an Expert Office subscription to comment.Start Free Trial