Avatar of angelnjj
angelnjjFlag for United States of America

asked on 

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)
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?
SQL

Avatar of undefined
Last Comment
_alias99
Avatar of angelnjj
angelnjj
Flag of United States of America image

ASKER

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!
Avatar of David Todd
David Todd
Flag of New Zealand image

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
Avatar of angelnjj
angelnjj
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of angelnjj
angelnjj
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of aikimark
aikimark
Flag of United States of America image

@angelnjj

Do you have a correct link to the Jeff Moden article?
Avatar of aikimark
aikimark
Flag of United States of America image

The correct solution selection should be
angelnjj's comment http:#35771743 (0 points) -- Solution
dtodd's comment http:#35771986 (500 points) -- assisted solution
Avatar of _alias99
_alias99
Flag of United States of America image

All,
 
Following an 'Objection' by aikimark (at https://www.experts-exchange.com/questions/27041769/18-May-11-15-Automated-Request-for-Review-Objection-to-Split-Q-27036836.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
SQL
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.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo