Solved

Dynamically add columns to temp table based on data in source table

Posted on 2010-09-15
10
416 Views
Last Modified: 2012-05-10
Hello All

I have a table that stores customer reservation information.  I need to create a table that displays each day of their reservation in the same row, not just the date range.  May be better to show vs describe.

current Customers Table (sample, there are other columns)
(customer_id int
, property varchar(8)
, arrival_date datetime
, departure_date datetime
, reservation_id varchar(20)
)

Data looks like this:
1111     abc     9/12/2010     9/15/2010     C1020304
1112     abc     9/10/2010     9/11/2010     C1020304
1113     abc     9/22/2010     9/24/2010     C1020304
1114     abc     9/29/2010    10/02/2010    C1020304

What I NEED in the #temp table is
#Customers_ByDay
(customer_id int
, property varchar(8)
, arrival_date datetime
, departure_date datetime
, day1 datetime
, day2 datetime
, day3 datetime
, day4 datetime
, day5 datetime
, ... (creates on the fly)
, reservation_id varchar(20)
)

#Customers_ByDay would look like this:
                        arrival        departure  day1           day2         day3          day4            
1111     abc     9/12/2010  9/15/2012  9/12/2010  9/13/2010  9/14/2010  9/15/2010     C1020304
1112     abc     9/10/2010  9/11/2010  9/10/2010  NULL          NULL         NULL            C1020305
1113     abc     9/22/2010  9/24/2010  9/22/2010  9/23/2010  9/24/2010  NULL            C1020306
1113     abc     9/29/2010 10/02/2010 9/29/2010  9/30/2010  10/01/2010 10/02/2010  C1020307

day4 etc would expand and add a day5, day6 etc columns depending on the datediff between arrival & departure (at least I THINK that would work).

What I don't understand is how to cycle through the dates and insert this extra column when needed and populate it with the dates between the arrival and departure.

Please let me know if I need to explain further.

Thank you!
0
Comment
Question by:angelnjj
  • 6
  • 4
10 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33688308
You may run into problems if you have too many days to add based on your arrival/departure dates and the number of columns, but this below example shows the following logic in use.


Build the temp table with minimal columns
Add the necessary columns based on the max number of days in dynamic SQL
update the column data in dynamic sql.
SET NOCOUNT ON
GO
CREATE TABLE #Customers 
     (customer_id        int
     ,property           varchar(8)
     ,arrival_date       datetime
     ,departure_date     datetime
     ,reservation_id     varchar(20)
     )

INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (1,'aa','2010-01-01','2010-01-12','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (2,'ab','2010-01-01','2010-01-03','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (3,'ac','2010-01-01','2010-01-06','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (4,'ad','2010-01-01','2010-02-02','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (5,'ae','2010-01-01','2010-02-02','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (6,'af','2010-01-01','2010-02-03','abc')
INSERT INTO #Customers (customer_id,property,arrival_date,departure_date,reservation_id)  VALUES (7,'ag','2010-01-01','2010-02-06','abc')
GO
DECLARE 
      @maxDiff int
     ,@SQL     nvarchar(MAX)
     ,@usql    NVARCHAR(MAX)
     
SELECT @maxDiff = MAX(DATEDIFF(d,c.arrival_date,c.departure_date)) +1
FROM #Customers c
SELECT @maxDiff AS 'Day Columns To Add'
SET @sql = N'alter table #customers add '
SET @usql = 'update #customers set day1 = arrival_date'

;WITH n1 AS (SELECT 1 AS n UNION ALL SELECT 1)
,n2 AS (SELECT 1 AS n FROM n1 a,n1 b)
,n3 AS (SELECT 1 AS n FROM n2 a,n2 b)
,n4 AS (SELECT 1 AS n FROM n3 a,n3 b)
,nums AS (SELECT row_number()  OVER (ORDER BY n) AS num FROM n4)
,fields AS (SELECT TOP (@maxdiff) 'Day' + LTRIM(STR(num)) + ' datetime null' AS s,num FROM nums)
SELECT @sql = @sql  + CASE WHEN num = 1 THEN '' ELSE ',' END + s
     ,@usql = @usql + CASE WHEN num <= 1 THEN '' ELSE ',day' + ltrim(str(num)) + ' = case when ' + LTRIM(STR(num-1)) + ' <= datediff(d,arrival_date,departure_date) then arrival_date + ' + LTRIM(STR(num-1)) + ' else null end ' END 
FROM fields

EXEC sp_executesql @SQL
EXEC sp_executesql @usql

PRINT @usql

SELECT * FROM #Customers

GO


DROP TABLE #Customers

Open in new window

0
 
LVL 1

Author Comment

by:angelnjj
ID: 33688681
This is almost working in my live data, but I get this:
Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'Day1' in table '#customers' is specified more than once.
update #customers set day1 = arrival,
day2 = case when 1 <= datediff(d,arrival,departure) then arrival + 1 else null end ,
day3 = case when 2 <= datediff(d,arrival,departure) then arrival + 2 else null end ,
day4 = case when 3 <= datediff(d,arrival,departure) then arrival + 3 else null end ,
day5 = case when 4 <= datediff(d,arrival,departure) then arrival + 4 else null end ,
day6 = case when 5 <= datediff(d,arrival,departure) then arrival + 5 else null end ,
day7 = case when 6 <= datediff(d,arrival,departure) then arrival + 6 else null end ,
day8 = case when 7 <= datediff(d,arrival,departure) then arrival + 7 else null end ,
etc...
day44 = case when 43 <= datediff(d,arrival,departure) then arrival + 43 else null end ,
day45 = case when 44 <= datediff(d,arrival,departure) then arrival + 44 else null end ,
day46 = case when 45 <= datediff(d,arrival,departure) then arrival + 45 else null end ,
day47 = case when 46 <= datediff(d,arrival,departure) then arrival + 46 el (then it cuts off)...

I'm fairly new to doing things dynamically and am trying to 'see' how this gets done, but can't figure out why it thinks there is a duped 'Day 1' column...none of my arrival or depature dates are the same, so datediff would never be 0.
0
 
LVL 1

Author Comment

by:angelnjj
ID: 33688704
you mentioned having too many days to add presenting itself as a problem...as an FYI, for this run (it could be different each time, depending on period running for) @maxDiff = 90. Is that too many?  Is there a cut-off?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33692327
You just can't exceed 1024 (max # of tables).

The other thing is your SSMS display options may be truncating the generated SQL too soon.  Go into tools > options > Query Results > SQL Server > Results to Text/Grid.

Change text to 8192 and grid to 65536.  Those are the maxes.

Then add a print @SQL and print @usql before their exeutions.  Can you post your code back.  Because my example worked as I posted it.
0
 
LVL 1

Author Comment

by:angelnjj
ID: 33692880
You're correct, not sure what I was doing last night, but running today there are no problems and it is the same data...

I've another issue that is similar and tried this dynamic sql approach, but haven't been successful.  Would love your help.  Should I create seperate question?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Closing Comment

by:angelnjj
ID: 33692886
Fantastic - quick to respond and spot on query...did exactly what I was looking for.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33693338
Yes.  Now problem = new question :D.
0
 
LVL 1

Author Comment

by:angelnjj
ID: 33693478
spoke too soon - when I put this in an sp, it gives me the error re: Day 1 being there more than once.  Here is the exact error with the print @sql and print @usql included - I don't see an issue...

alter table ReservationMatrix add Day1 datetime null,Day2 datetime null,Day3 datetime null,Day4 datetime null,Day5 datetime null,Day6 datetime null,Day7 datetime null,Day8 datetime null,Day9 datetime null,Day10 datetime null,Day11 datetime null,Day12 datetime null,Day13 datetime null,Day14 datetime null,Day15 datetime null,Day16 datetime null,Day17 datetime null,Day18 datetime null,Day19 datetime null,Day20 datetime null,Day21 datetime null,Day22 datetime null,Day23 datetime null,Day24 datetime null,Day25 datetime null,Day26 datetime null,Day27 datetime null,Day28 datetime null,Day29 datetime null,Day30 datetime null,Day31 datetime null,Day32 datetime null,Day33 datetime null,Day34 datetime null,Day35 datetime null,Day36 datetime null,Day37 datetime null,Day38 datetime null,Day39 datetime null,Day40 datetime null,Day41 datetime null,Day42 datetime null,Day43 datetime null,Day44 datetime null,Day45 datetime null,Day46 datetime null,Day47 datetime null,Day48 datetime null,Day49 datetime null,Day50 datetime null,Day51 datetime null,Day52 datetime null,Day53 datetime null,Day54 datetime null,Day55 datetime null,Day56 datetime null,Day57 datetime null,Day58 datetime null,Day59 datetime null,Day60 datetime null,Day61 datetime null,Day62 datetime null,Day63 datetime null,Day64 datetime null,Day65 datetime null,Day66 datetime null,Day67 datetime null,Day68 datetime null,Day69 datetime null,Day70 datetime null,Day71 datetime null,Day72 datetime null,Day73 datetime null,Day74 datetime null,Day75 datetime null,Day76 datetime null,Day77 datetime null,Day78 datetime null,Day79 datetime null,Day80 datetime null,Day81 datetime null,Day82 datetime null,Day83 datetime null,Day84 datetime null,Day85 datetime null,Day86 datetime null,Day87 datetime null,Day88 datetime null,Day89 datetime null,Day90 datetime null
update ReservationMatrix set day1 = arrival,day2 = case when 1 <= datediff(d,arrival,departure) then arrival + 1 else null end ,day3 = case when 2 <= datediff(d,arrival,departure) then arrival + 2 else null end ,day4 = case when 3 <= datediff(d,arrival,departure) then arrival + 3 else null end ,day5 = case when 4 <= datediff(d,arrival,departure) then arrival + 4 else null end ,day6 = case when 5 <= datediff(d,arrival,departure) then arrival + 5 else null end ,day7 = case when 6 <= datediff(d,arrival,departure) then arrival + 6 else null end ,day8 = case when 7 <= datediff(d,arrival,departure) then arrival + 7 else null end ,day9 = case when 8 <= datediff(d,arrival,departure) then arrival + 8 else null end ,day10 = case when 9 <= datediff(d,arrival,departure) then arrival + 9 else null end ,day11 = case when 10 <= datediff(d,arrival,departure) then arrival + 10 else null end ,day12 = case when 11 <= datediff(d,arrival,departure) then arrival + 11 else null end ,day13 = case when 12 <= datediff(d,arrival,departure) then arrival + 12 else null end ,day14 = case when 13 <= datediff(d,arrival,departure) then arrival + 13 else null end ,day15 = case when 14 <= datediff(d,arrival,departure) then arrival + 14 else null end ,day16 = case when 15 <= datediff(d,arrival,departure) then arrival + 15 else null end ,day17 = case when 16 <= datediff(d,arrival,departure) then arrival + 16 else null end ,day18 = case when 17 <= datediff(d,arrival,departure) then arrival + 17 else null end ,day19 = case when 18 <= datediff(d,arrival,departure) then arrival + 18 else null end ,day20 = case when 19 <= datediff(d,arrival,departure) then arrival + 19 else null end ,day21 = case when 20 <= datediff(d,arrival,departure) then arrival + 20 else null end ,day22 = case when 21 <= datediff(d,arrival,departure) then arrival + 21 else null end ,day23 = case when 22 <= datediff(d,arrival,departure) then arrival + 22 else null end ,day24 = case when 23 <= datediff(d,arrival,departure) then arrival + 23 else null end ,day25 = case when 24 <= datediff(d,arrival,departure) then arrival + 24 else null end ,day26 = case when 25 <= datediff(d,arrival,departure) then arrival + 25 else null end ,day27 = case when 26 <= datediff(d,arrival,departure) then arrival + 26 else null end ,day28 = case when 27 <= datediff(d,arrival,departure) then arrival + 27 else null end ,day29 = case when 28 <= datediff(d,arrival,departure) then arrival + 28 else null end ,day30 = case when 29 <= datediff(d,arrival,departure) then arrival + 29 else null end ,day31 = case when 30 <= datediff(d,arrival,departure) then arrival + 30 else null end ,day32 = case when 31 <= datediff(d,arrival,departure) then arrival + 31 else null end ,day33 = case when 32 <= datediff(d,arrival,departure) then arrival + 32 else null end ,day34 = case when 33 <= datediff(d,arrival,departure) then arrival + 33 else null end ,day35 = case when 34 <= datediff(d,arrival,departure) then arrival + 34 else null end ,day36 = case when 35 <= datediff(d,arrival,departure) then arrival + 35 else null end ,day37 = case when 36 <= datediff(d,arrival,departure) then arrival + 36 else null end ,day38 = case when 37 <= datediff(d,arrival,departure) then arrival + 37 else null end ,day39 = case when 38 <= datediff(d,arrival,departure) then arrival + 38 else null end ,day40 = case when 39 <= datediff(d,arrival,departure) then arrival + 39 else null end ,day41 = case when 40 <= datediff(d,arrival,departure) then arrival + 40 else null end ,day42 = case when 41 <= datediff(d,arrival,departure) then arrival + 41 else null end ,day43 = case when 42 <= datediff(d,arrival,departure) then arrival + 42 else null end ,day44 = case when 43 <= datediff(d,arrival,departure) then arrival + 43 else null end ,day45 = case when 44 <= datediff(d,arrival,departure) then arrival + 44 else null end ,day46 = case when 45 <= datediff(d,arrival,departure) then arrival + 45 else null end ,day47 = case when 46 <= datediff(d,arrival,departure) then arrival
Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'Day1' in table 'ReservationMatrix' is specified more than once.
0
 
LVL 1

Author Comment

by:angelnjj
ID: 33693625
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33693634
In your initial create table statement are you adding Day1?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Left Join with Tuple returning more rows 10 90
Create A query for disabled users 5 30
Oracle query output question 4 36
How to base a filter depending on fields contents? 15 49
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now