SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Creating a very large table with a Loop : 365_day
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sqlnewbie24
Starting Member

3 Posts

Posted - 09/10/2011 :  20:26:59  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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





















Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/11/2011 :  11:52:28  Show Profile  Reply with Quote
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/

Go to Top of Page

Sqlnewbie24
Starting Member

3 Posts

Posted - 09/11/2011 :  16:44:35  Show Profile  Reply with Quote
Thanks visakh16 for the link.
I will go over the code.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/12/2011 :  00:49:43  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000