expanding the table - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

expanding the table

Author

Topic

umutos
Starting Member

12 Posts

Posted - 2010-08-14 : 11:45:01

Hello guys!

I have to expand a table but i am not able to do it. Maybe one of the experts here can help me a little bit:

I have this table given:


Security	Date	Cusip
AB	01.05.1997	hge678
AB	20.06.2001	frr124
AB	09.04.2007	ghh898
AC	04.07.2000	err231
AD	05.08.1999	mkk123
AD	01.01.2001	ad312

I have to expand this table for every security until 31.Dec.2008, so that every date has a row. For every date which is not in the table, I have to take the Cusip-number of the last available date.e.g. for security AB on every date between 01.05.1997 and 20.06.2001 I have to take the Cusip hge678.

The resulting table would look like this:


Security	Date	Cusip
AB	01.05.1997	hge678
AB	02.05.1997	hge678
AB	03.05.1997	hge678
	etc.	
AB	19.06.2001	hge678
AB	20.06.2001	frr124
AB	21.06.2001	frr124
	etc.	
AB	08.04.2007	frr124
AB	09.04.2007	ghh898
AB	10.04.2007	ghh899
	etc.	
AB	31.12.2008	ghh899
AC	04.07.2000	err231
AC	05.07.2000	err232
	etc.	
AC	31.12.2008	err232
AD	05.08.1999	mkk123
AD	06.08.1999	mkk124
	etc.	
AD	31.12.2000	mkk124
AD	01.01.2001	ad312
AD	02.01.2001	ad313
	etc.	
AD	31.12.2008	ad313

I hope that I was able to illustrate the problem. if not just tell me and i will try to explain it in other words. would really appreciate when somebody could help me!
Thank you very much!

Umutos

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 12:52:25

you need to generate a calendar table and cross join with it to generate dates for every security. then left join with your table to get Cusip values.

see below for generating calendar table

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

umutos
Starting Member

12 Posts

Posted - 2010-08-16 : 05:05:50

Thank you visakh!

To repeat your solution:
1. create calendar table
2. cross join to generate dates for every security
3. left join the resulting table with the original table to get cusips

I have done step 1 and 2 and get the following table:



AB	01.05.1997
AB	02.05.1997
AB	03.05.1997
	etc.
AB	19.06.2001
AB	20.06.2001
AB	21.06.2001
	etc.
AB	08.04.2007
AB	09.04.2007
AB	10.04.2007
	etc.
AB	31.12.2008
AC	04.07.2000
AC	05.07.2000
	etc.
AC	31.12.2008
AD	05.08.1999
AD	06.08.1999
	etc.
AD	31.12.2000
AD	01.01.2001
AD	02.01.2001
	etc.
AD	31.12.2008

Lets call this table „calendar“.

When I now left join “calendar” with the original table(lets call it “original”) with the command


Select *from calendar c1
Left join original o1 on c1.security=o1.security and c1.date=o1.date

I get the following table:




AB	01.05.1997	hge678
AB	02.05.1997	Null
AB	03.05.1997	Null
	etc.	
AB	19.06.2001	Null
AB	20.06.2001	frr124
AB	21.06.2001	Null
	etc.	
AB	08.04.2007	Null
AB	09.04.2007	ghh898
AB	10.04.2007	Null
	etc.	
AB	31.12.2008	Null
AC	04.07.2000	err231
AC	05.07.2000	Null
	etc.	
AC	31.12.2008	Null
AD	05.08.1999	mkk123
AD	06.08.1999	Null
	etc.	
AD	31.12.2000	Null
AD	01.01.2001	ad312
AD	02.01.2001	Null
	etc.	
AD	31.12.2008	Null

Do I have to use another join function?

Thank you for your help! I really appreciate that!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:39:54

No, you can use CROSS APPLY to get previously valid record and update current record (where cusip is null).
Visakh will show you shortly.

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-16 : 10:54:36

[code]Select c.Security,c.Date,c1.cusip from calendar c
cross apply (select top 1 cusip
from yourtable
where date < = c.date
order by date desc)c1
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources