Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-15
10
Medium Priority
?
459 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 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
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 
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
 
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 39

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

661 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