Solved

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

Posted on 2010-09-15
10
414 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Closing Comment

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

Expert Comment

by:BrandonGalderisi
Comment Utility
Yes.  Now problem = new question :D.
0
 
LVL 1

Author Comment

by:angelnjj
Comment Utility
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
Comment Utility
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
In your initial create table statement are you adding Day1?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

18 Experts available now in Live!

Get 1:1 Help Now