| Author |
 Topic  |
|
|
Sqlnewbie24
Starting Member
3 Posts |
 Posted - 09/10/2011 : 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 - 09/11/2011 : 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
India
52326 Posts |
|
|
Sqlnewbie24
Starting Member
3 Posts |
Posted - 09/11/2011 : 16:44:35
|
Thanks visakh16 for the link.
I will go over the code. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 09/12/2011 : 00:49:43
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Creating a very large table with a Loop : 365_day