Creating a very large table with a Loop : 365_day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating a very large table with a Loop : 365_day

Author

Topic

Sqlnewbie24
Starting Member

3 Posts

Posted - 2011-09-10 : 20:26:59

I need help to produce a table of different equipment and there rental duration from 0 days to 365 days as fields. To go from table1 to table2 with a loop or a set based approach

??help??

javascript:insertsmilie('

')
javascript:insertsmilie('

')

Background:

Table01 view -Start
Sample of field below

|Store_location|equipment|rent_start_date|rent_end_date|duration|

Details to table01

Rental can last anywhere from 0 days to 365 days and over.
This table holds a row for every equipment type rented out with it duration by store location. so multiple occurrences of the same equipment with different dates.
The duration is calculated as =>> datadiff(dd, rent_start_date, rent_end_date)

Table02: End result

|Store_location|equipment|0_days to 365_days duration fields|

Details for table02
In this table each equipment by store is only represented once.
The day fields 0_days to 365_days need be a count of all the same equipment type that share the same duration(as days rented out for).

Sqlnewbie24
Starting Member

3 Posts

Posted - 2011-09-11 : 04:24:41

Hi all
Following my earlier post . here is my solution.
Any comments and different solution would be greatly welcomed.

Table01 layout
Select store, equip_type , rent_start_date, rent_end_date, duration from table01

I have not test the code below, so it may contain errors

Select
,store,
,equip_type
Into dbo.table02
From dbo.table01

Declare @counter as int
Declare @New_column as varchar (9)

Set @counter = 0

/***************************************************************
appending 366 field on the end of table02 with a loop
************************************************************** */
While @counter < 366
Begin

/* *************************************************************
creating new column name to add on to the end of table02
***************************************************************/

Set @New_column = cast(@counter char (3))+’_days’

ALTER TABLE dbo.table02_duration
ADD @New_column

/*****************************************************************
upate new column with count of the same duration events
******************************************************************/
Update a
Set a.@New_column = sum( case when b.duration = @counter then 1 else 0 end)
From dbo. table02 a
Join table01 b
On a. store = b.store,
And a.equip_type = b. equip_type

@counter = @counter + 1

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-11 : 11:52:28

i think what you need is this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sqlnewbie24
Starting Member

3 Posts

Posted - 2011-09-11 : 16:44:35

Thanks visakh16 for the link.
I will go over the code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 00:49:43

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources