HELP...need to speed this up, taking +15 hrs now. Goal: insert rows for missing dates, based on previous date

• angelnjj

  SSC-Addicted

  Points: 451

  More actions

  August 26, 2011 at 10:16 am

  #256321

  Hello All,

  I think this is my first post...please forgive if I omit information necessary...but I think I've set it up so you can see a bit of the data and my current code to get an idea of what I've been trying, and where I'm stuck. I'm on SQL 2008

  Below is a sample of my original data. The real table has +600,000 rows, this is just one bookingid among many for a bookingid that only spans 2 months of changes.

  IF OBJECT_ID('TempDB..#SampleData','U') IS NOT NULL DROP TABLE #SampleData

  CREATE TABLE #SampleData (abbreviation varchar(20), propertypid int, bookingid int, changedate datetime

  , roomdate datetime, blockedrooms int, statusid int)

  INSERT INTO #SampleData

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 20 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 21 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 22 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 19 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 20 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 21 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 22 2011 12:00AM','0','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 19 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 20 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 21 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 22 2011 12:00AM','0','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 19 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 20 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 21 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 22 2011 12:00AM','0','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 19 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 20 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 21 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 22 2011 12:00AM','0','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 19 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 20 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 21 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 22 2011 12:00AM','0','3'

  select * from #SampleData

  My existing code pulls from this table and tries to insert rows for all dates between changedates for all of the roomdates and blockedrooms values from the previous changedate. Again, the sample spans just 2 months, my 'real' table (+600k rows) has bookingid's that have changes that span span years.

  My problem/where I need help is that my code takes a VERY long time to run on the entire data set (+15hrs)

  Existing Code:

  ;with cteall as

  (select propertypid, bookingid, min(changedate) as changedate, MAX(getdate()-1) as maxchangedate

  from #SampleData

  WHERE abbreviation in ('CompanyA', 'CompanyB', 'CompanyC')

  group by propertypid, bookingid

  union all

  select c.propertypid, c.bookingid, dateadd(dd,1,c.changedate), c.maxchangedate

  from cteall c

  where dateadd(dd,1,c.changedate) < c.maxchangedate) ,

  ctemissing as

  (select c.propertypid, c.bookingid, c.changedate, datediff(d,t.changedate,c.changedate) as diff

  , t.abbreviation, t.roomdate, t.blockedrooms, t.statusid

  from cteall c

  cross join #SampleData t

  where c.bookingid = t.bookingid and datediff(d,t.changedate,c.changedate) > 0)

  insert into #SampleData

  select abbreviation, propertypid, bookingid, changedate, roomdate, blockedrooms, statusid

  from ctemissing c

  where not exists ( select 1 from #SampleData

  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 propertypid, bookingid, changedate, roomdate

  option (maxrecursion 32767)

  select * into #MyResults from #SampleData order by propertypid, bookingid, changedate, roomdate

  I'm at a loss for ideas on how to speed it up, I feel like it is the cross join, but I don't know how else to get the 'dates between' AND the appropriate roomdate and blockedrooms number.

  Any assistance/suggestions would be helpful.

  For good measure...the final results should look like this:

  IF OBJECT_ID('TempDB..#FinalData','U') IS NOT NULL DROP TABLE #FinalData

  CREATE TABLE #FinalData (abbreviation varchar(20), propertypid int, bookingid int, changedate datetime

  , roomdate datetime, blockedrooms int, statusid int, inserted varchar(1))

  INSERT INTO #FinalData

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 22 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 24 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 24 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 24 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 24 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 25 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 25 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 25 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 25 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 26 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 26 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 26 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 26 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 27 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 27 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 27 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 27 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 28 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 28 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 28 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 28 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 29 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 29 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 29 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 29 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 30 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 30 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 30 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 30 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 23 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 01 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 01 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 01 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 01 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 02 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 02 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 02 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 02 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 03 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 03 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 03 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 03 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 04 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 04 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 04 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 04 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 05 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 05 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 05 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 05 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 06 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 06 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 06 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 06 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 07 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 07 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 07 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 07 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 08 2011 12:00AM','Sep 19 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 08 2011 12:00AM','Sep 20 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 08 2011 12:00AM','Sep 21 2011 12:00AM','165','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 08 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 19 2011 12:00AM','116','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 20 2011 12:00AM','116','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 21 2011 12:00AM','116','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 22 2011 12:00AM','0','1', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 11 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 11 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 11 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 11 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 12 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 12 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 12 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 12 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 13 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 13 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 13 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 13 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 14 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 14 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 14 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 14 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 15 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 15 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 15 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 15 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 16 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 16 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 16 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 16 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 18 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 18 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 18 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 18 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 19 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 19 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 19 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 19 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 20 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 20 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 20 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 20 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 21 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 21 2011 12:00AM','Sep 20 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 21 2011 12:00AM','Sep 21 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 21 2011 12:00AM','Sep 22 2011 12:00AM','0','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 21 2011 12:00AM','Sep 19 2011 12:00AM','116','2', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 19 2011 12:00AM','116','3', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 20 2011 12:00AM','116','3', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 21 2011 12:00AM','116','3', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 22 2011 12:00AM','0','3', 'N' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 23 2011 12:00AM','Sep 19 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 23 2011 12:00AM','Sep 20 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 23 2011 12:00AM','Sep 21 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 23 2011 12:00AM','Sep 22 2011 12:00AM','0','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 24 2011 12:00AM','Sep 19 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 24 2011 12:00AM','Sep 20 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 24 2011 12:00AM','Sep 21 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 24 2011 12:00AM','Sep 22 2011 12:00AM','0','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 25 2011 12:00AM','Sep 19 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 25 2011 12:00AM','Sep 20 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 25 2011 12:00AM','Sep 21 2011 12:00AM','116','3', 'Y' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 25 2011 12:00AM','Sep 22 2011 12:00AM','0','3', 'Y'

  select * from #FinalData

  ANY help you all could give would be much appreciated. I LOVE this site and hope to be able to answer questions vs reading and poaching other people's solutions some day...one can dream...

  Thanks, angelnjj

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  August 26, 2011 at 10:19 am

  #1373926

  Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Jeff Moden

  SSC Guru

  Points: 1004642

  More actions

  August 26, 2011 at 11:28 am

  #1373999

  Thanks for posting the "readily consumable" data on this. I'm not sure but I have an idea running around in the back of my mind that just might do it rather quickly. I'll try to get to it tonight.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• angelnjj

  SSC-Addicted

  Points: 451

  More actions

  August 26, 2011 at 12:11 pm

  #1374023

  Working on getting the execution plan following the link sent. I cut the time frame down and only including 3 companies so won't take as long to actually run...will post it shortly.

  Thank you!

• angelnjj

  SSC-Addicted

  Points: 451

  More actions

  August 26, 2011 at 1:30 pm

  #1374077

  Jeff Moden - Thank you any help is appreciated. I can't wait to see what is on your mind...

  GilaMonster - I don't know how to 'read' execution plans, so any notes or things that jump out at ya'll that I should note for future queries would be helpful too. I put the execution plan inside the .zip file so I could upload. I'm ashamed to say I don't have any indexes...would I put it on the identity column? Everything else in my data wouldn't be unique. Table statements below:

  #SampleData is actually called [RMS_PASSTHROUGH] in the stored procedure and has the same structure as the final table [RMS_DAILY]:

  ([id] [int] NULL,

  [abbreviation] [varchar](20) NULL,

  [propertypid] [int] NULL,

  [bookingid] [int] NULL,

  [changedate] [datetime] NULL,

  [roomdate] [datetime] NULL,

  [agreedrooms] [int] NULL,

  [blockedrooms] [int] NULL,

  [pickeduprooms] [int] NULL,

  [agreedroomrevenue] [money] NULL,

  [blockedroomrevenue] [money] NULL,

  [pickeduproomrevenue] [money] NULL,

  [pickupdate] [datetime] NULL,

  [postas] [varchar](300) NULL,

  [statusid] [int] NULL,

  [bookingtypeid] [int] NULL,

  [blendedrooms] [int] NULL,

  [blendedroomrevenue] [int] NULL,

  [roomdow] [varchar](20) NULL

  ) ON [PRIMARY]

• Jeff Moden

  SSC Guru

  Points: 1004642

  More actions

  August 26, 2011 at 7:45 pm

  #1374176

  This should do the trick for you. I didn't take the time to make much data from your original post so you might have to play with indexes to make this run fast but it should run a whole lot faster even without any indexes.

  The first thing you're going to need is a zero-based Tally Table so we can get rid of that awful recursive CTE. Please see the following article for what a Tally Table is and how it's used to replace certain RBAR such as recursive CTE's.

  http://www.sqlservercentral.com/articles/T-SQL/62867/

  Please see the following article for why Recursive CTE's shouldn't be used for the things you used them for.

  http://www.sqlservercentral.com/articles/T-SQL/74118/

  Here's the code to build a "Zero Based" Tally Table...

  --===================================================================

  -- Create a Tally table from 0 to 11000

  --===================================================================

  --===== Create and populate the Tally table on the fly.

  SELECT TOP 11001

  IDENTITY(INT,0,1) AS N

  INTO dbo.Tally

  FROM sys.ALL_Columns ac1

  CROSS JOIN sys.ALL_Columns ac2

  ;

  --===== Add a CLUSTERED Primary Key to maximize performance

  ALTER TABLE dbo.Tally

  ADD CONSTRAINT PK_Tally_N

  PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

  ;

  --===== Allow the general public to use it

  GRANT SELECT ON dbo.Tally TO PUBLIC

  ;

  GO

  Next, the code to generate a bit more data than your example so that we can make sure it works across different "categories"

  --=====================================================================================================================

  -- Create the test data. Except for the indexes I've included, this is NOT a part of the solution.

  --=====================================================================================================================

  --===== Do this in a nice, safe place that everyone has. Didn't use a temp table here

  -- because I was playing with the tuning advisor.

  USE TempDB;

  --===== Conditionally drop the test table to make reruns easier in SSMS

  IF OBJECT_ID('TempDB.dbo.SampleData','U') IS NOT NULL DROP TABLE dbo.SampleData;

  --===== Create the test table with a clustered PK to keep from fragging the table on inserts

  CREATE TABLE dbo.SampleData

  (

  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  Abbreviation VARCHAR(20),

  PropertyPID INT,

  BookingID INT,

  ChangeDate DATETIME,

  RoomDate DATETIME,

  BlockedRooms INT,

  StatusID INT

  )

  ;

  --===== Add a useful "covering" index.

  -- This won't actually "kick in" until we have more data.

  CREATE INDEX IX_SampleData_Cover01

  ON dbo.SampleData (Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate)

  ;

  --===== Create the original data from the post

  INSERT INTO dbo.SampleData

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 20 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 21 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 21 2011 12:00AM','Sep 22 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 19 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 20 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 21 2011 12:00AM','165','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Jul 22 2011 12:00AM','Sep 22 2011 12:00AM','0','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 19 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 20 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 21 2011 12:00AM','116','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 9 2011 12:00AM','Sep 22 2011 12:00AM','0','1' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 19 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 20 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 21 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 10 2011 12:00AM','Sep 22 2011 12:00AM','0','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 19 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 20 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 21 2011 12:00AM','116','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 17 2011 12:00AM','Sep 22 2011 12:00AM','0','2' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 19 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 20 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 21 2011 12:00AM','116','3' UNION ALL

  SELECT 'CompanyA', '5985', '1345739','Aug 22 2011 12:00AM','Sep 22 2011 12:00AM','0','3'

  ;

  --===== Add 3 more sets of slightly different data for CompanyA so we can see that it works for different

  -- PropertyPID's and BookingID's when the company abbreviation is the same.

  INSERT INTO dbo.SampleData

  (Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate, BlockedRooms, StatusID)

  SELECT Abbreviation,

  PropertyPID = PropertyPID + t.N,

  BookingID = BookingID + t.N,

  ChangeDate,

  RoomDate,

  BlockedRooms,

  StatusID

  FROM dbo.SampleData

  CROSS JOIN (SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 7) t (N)

  ORDER BY Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate --This is just for ease of viewing later

  ;

  --===== Add 4 sets of slightly different data for CompanyB for similar reasons and to make sure we can handle

  -- more than 1 company.

  INSERT INTO dbo.SampleData

  (Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate, BlockedRooms, StatusID)

  SELECT Abbreviation ='CompanyB',

  PropertyPID = PropertyPID + t.N,

  BookingID = BookingID + t.N,

  ChangeDate,

  RoomDate,

  BlockedRooms,

  StatusID

  FROM dbo.SampleData sd

  CROSS JOIN (SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 7 UNION ALL SELECT 11) t (N)

  WHERE sd.PropertyPID = (SELECT MAX(PropertyPID) FROM dbo.SampleData)

  ORDER BY Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate --This is just for ease of viewing later

  ;

  --===== Now, let's see what we've got. There should be 184 rows

  SELECT *

  FROM dbo.SampleData

  ORDER BY ID

  ;

  Last and certainly not least, the solution to the problem. As usual, the details are in the code.

  --=====================================================================================================================

  -- We're ready to rock. I don't expect the indexes will "kick in" for real until there's more data.

  -- Even though it was in the desired output you listed, I didn't include the "Y/N" column because you didn't

  -- include it in your original code. If that's a problem, post back and we'll fix you up. ;-)

  --=====================================================================================================================

  WITH

  cteFindEdgeDataAndGaps AS

  ( --=== This finds all the "trailing edge" data and calculates the "Gap Dates"

  SELECT lo.ChangeDate,

  GapStart = lo.ChangeDate + 1,

  GapEnd = (

  SELECT MIN(hi.ChangeDate)

  FROM dbo.SampleData hi

  WHERE hi.Abbreviation = lo.Abbreviation

  AND hi.PropertyPID = lo.PropertyPID

  AND hi.BookingID = lo.BookingID

  AND hi.ChangeDate > lo.ChangeDate

  )-1,

  lo.Abbreviation,

  lo.PropertyPID,

  lo.BookingID,

  lo.RoomDate,

  lo.BlockedRooms,

  lo.StatusID

  FROM dbo.SampleData lo

  WHERE lo.ChangeDate NOT IN (

  SELECT edge.ChangeDate-1

  FROM dbo.SampleData edge

  WHERE edge.Abbreviation = lo.Abbreviation

  AND edge.PropertyPID = lo.PropertyPID

  AND edge.BookingID = lo.BookingID

  )

  ) --=== This Cross Join with the zero-based Tally table creates all of the missing dates

  -- and duplicates the "trailing edge" data we found above.

  INSERT INTO dbo.SampleData

  (Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate, BlockedRooms, StatusID)

  SELECT Abbreviation,

  PropertyPID,

  BookingID,

  ChangeDate = GapStart + t.N,

  RoomDate,

  BlockedRooms,

  StatusID

  FROM cteFindEdgeDataAndGaps

  CROSS JOIN dbo.Tally t

  WHERE t.N BETWEEN 0 AND DATEDIFF(dd,GapStart,GapEnd)

  ;

  --===== Now, let's see what we've got. There should be 1084 rows total

  SELECT *

  FROM dbo.SampleData

  ORDER BY Abbreviation, PropertyPID, BookingID, ChangeDate, RoomDate

  ;

  If, after reading the Tally Table article, you have any questions, please post back.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• angelnjj

  SSC-Addicted

  Points: 451

  More actions

  August 30, 2011 at 1:50 pm

  #1375218

  Hello Jeff...

  Thank you VERY much, I read through the tally table article and there were a couple of points that confused me, but I think I need to re-read and work out with examples in front of me. Thanks for pointing me in the right direction.

  Re: my query, I've updated with the code provided. I re-ran, and indeed...it reduced time to 1.5hrs (wow). There were a couple of issues I needed to work out (needed change date for each booking to extend to today if the roomdate had not yet passed), but now things are working beautifully. I'm still in the process of verifying results, but it looks good to go.

  I can't thank you enough!

  Are there points or something that get rewarded to SQL saviours/gurus on this site?

  Angelj

• Jeff Moden

  SSC Guru

  Points: 1004642

  More actions

  August 30, 2011 at 6:23 pm

  #1375283

  Nope. No points or anything like that. I'm just glad to help. Thank you for your kind feedback. I always like to know how these things turn out.

  Once you get done verifying and tweeking the code to meet the real business requirements, you might be able to reduce the amount of time it takes by a similar order of magnitude by analyzing and, perhaps, adding a couple of well thought out indexes.

  If you take a look at the second link in my signature line below, you'll find Gail Shaw's ("Gila Monster" above) fine article on how to post for performance improvements. I recommend opening a new thread if you decide to do something like that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)