How to Combine Output of Several Queries into One Text File in SSIS
I am really new to SSIS and struggling.
I have to create a text file apending the output of several queries.
How do I use SSIS to create this text file. I have several queries that look like
--============================================
DECLARE @ValuateDate DATETIME
SET @ValuateDate = '2010-12-31 00:00:00.000'
SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,'
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
DECLARE @AsOfDate DATETIME
SET @AsOfDate = '2010-12-31 00:00:00.000'
SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT CONVERT(VARCHAR, xdate, 112) + ',' +
xperiod + ',' +
CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,'
FROM RC
WHERE [Status] = 'A'
AND xdate = @AsOfDate
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
DECLARE @yDate DATETIME
SET @yDate = '2010-12-31 00:00:00.000'
SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT yperiod + ',' +
CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,'
FROM DY
WHERE [Status] = 'A'
AND yDate = @yDate
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
CREATE TABLE #TTXYZ (OutputXYZ VARCHAR(2000))
INSERT INTO #TTXYZ
SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' +
'140' + ',' + '150' + ',' + '200'
DECLARE @FileDate DATETIME
SET @FileDate = '2010-12-31 00:00:00.000'
INSERT INTO #TTXYZ
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[40]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[60]) + ',' +
CONVERT(VARCHAR(250), A.[70]) + ',' +
CONVERT(VARCHAR(250), A.[75]) + ',' +
CONVERT(VARCHAR(250), A.[80]) + ',' +
CONVERT(VARCHAR(250), A.[85]) + ',' +
CONVERT(VARCHAR(250), A.[90]) + ',' +
CONVERT(VARCHAR(250), A.[95]) + ',' +
CONVERT(VARCHAR(250), A.[100])+ ',' +
CONVERT(VARCHAR(250), A.[105])+ ',' +
CONVERT(VARCHAR(250), A.[110])+ ',' +
CONVERT(VARCHAR(250), A.[115])+ ',' +
CONVERT(VARCHAR(250), A.[120])+ ',' +
CONVERT(VARCHAR(250), A.[125])+ ',' +
CONVERT(VARCHAR(250), A.[130])+ ',' +
CONVERT(VARCHAR(250), A.[140])+ ',' +
CONVERT(VARCHAR(250), A.[150])+ ',' +
CONVERT(VARCHAR(250), A.[200])
FROM
(
SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
FROM
(SELECT Term, Percentile, Strike
FROM XYZ
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
SELECT * FROM #TTXYZ
SELECT ',,,,,,,,,,,,,,,,,,,'
DROP TABLE #TTXYZ
--============================================
The text file should look something like this:
Valuate Date,,12/31/2010,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
AsOfDate,,MidRate,,,,,,,,,,,,,,,,,
20101231,1D,0.125,,,,,,,,,,,,,,,,,
20101231,1M,0.13,,,,,,,,,,,,,,,,,
20101231,2M,0.10412,,,,,,,,,,,,,,,,,
20101231,3M,0.22345,,,,,,,,,,,,,,,,,
20101231,40Y,2.11258,,,,,,,,,,,,,,,,,
20101231,41Y,2.1175,,,,,,,,,,,,,,,,,
20101231,42Y,2.13,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
DivDate,MidRate,,,,,,,,,,,,,,,,,,
1Y,1.0158,,,,,,,,,,,,,,,,,,
2Y,1.1511,,,,,,,,,,,,,,,,,,
3Y,1.2997,,,,,,,,,,,,,,,,,,
4Y,1.3615,,,,,,,,,,,,,,,,,,
5Y,1.7253,,,,,,,,,,,,,,,,,,
6Y,1.4961,,,,,,,,,,,,,,,,,,
7Y,1.3326,,,,,,,,,,,,,,,,,,
8Y,1.6157,,,,,,,,,,,,,,,,,,
9Y,1.2117,,,,,,,,,,,,,,,,,,
10Y,1.9524,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
TERMS,40,50,60,70,75,80,85,90,95,100,105,110,115,120,125,130,140,150,200
1M,81.15,69.39,59.21,49.87,45.24,40.68,37.12,31.5,26.18,20.53,15.76,14.85,17.14,13.44,13.95,14.46,15.26,15.75,15.75
3M,59.28,52.11,45.49,39.1,35.95,32.84,29.94,27.02,23.63,20.34,18.02,15.4,13.83,13.63,13.84,14.07,14.56,14.82,14.84
6Y,35.86,33.35,31.09,29.1,28.16,27.23,26.38,25.53,24.75,23.98,23.27,22.57,21.93,21.31,20.75,20.21,19.27,18.43,15.91
7Y,35.9,33.53,31.42,29.57,28.69,27.83,27.04,26.26,25.53,24.82,24.16,23.51,22.91,22.33,21.8,21.29,20.38,19.57,16.87
8Y,36.05,33.85,31.88,30.15,29.34,28.54,27.8,27.08,26.4,25.73,25.11,24.5,23.94,23.39,22.89,22.39,21.52,20.72,17.88
9Y,36.21,34.15,32.3,30.68,29.91,29.16,28.47,27.79,27.15,26.52,25.93,25.36,24.82,24.3,23.82,23.35,22.5,21.73,18.8
10Y,36.36,34.42,32.67,31.14,30.42,29.71,29.05,28.41,27.8,27.21,26.65,26.11,25.6,25.1,24.64,24.19,23.37,22.62,19.66
,,,,,,,,,,,,,,,,,,,
TERMS,1Y,2Y,3Y,4Y,5Y,7Y,10Y,15Y,20Y,25Y,30Y,,,,,,,,
1M,120.6,95.5,80.1,65.7,58.4,45.5,38.1,31,28.5,26.8,26.55,,,,,,,,
3M,108.3,86.7,71,59.2,53.6,42.1,35,29.7,27.6,27.06,26.01,,,,,,,,
6M,101.9,78,62.7,53,48,38.9,32.9,27.9,26.1,25.47,24.55,,,,,,,,
20Y,17.1,16.9,16.5,16.2,15.9,15.7,15.5,14.6,14.2,14.2,14.2,,,,,,,,
25Y,16.3,16.2,15.9,15.6,15.3,15.2,15.1,14.6,14.5,14.6,14.5,,,,,,,,
30Y,16.1,16,15.8,15.5,15.2,15.3,15.5,15.2,15.1,15,14.8,,,,,,,,
,,,,,,,,,,,,,,,,,,,
ABC,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
3M,-11.10642722,-11.10642722,-11.10642722,-5.034760873,0,2.020418172,6.751897694,11.17845703,14.41822452,,,,,,,,,,
6M,-4.061528263,-4.061528263,-4.061528263,-2.190803914,0,1.007284799,3.602992542,6.275349445,8.346291692,,,,,,,,,,
7Y,9.704440423,4.95659497,1.826853579,0.76317559,0,-0.279144977,-0.772692213,-0.856299565,-0.584548035,,,,,,,,,,
10Y,9.130007634,4.953796941,1.948158101,0.842153631,0,-0.324276784,-0.945269402,-1.142308261,-0.929713449,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
DEF,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
3M,1.931776901,1.931776901,-3.382620316,-1.777061882,0,0.838619806,3.064402039,5.431664422,7.299156372,,,,,,,,,,
7Y,9.880792642,5.179110337,1.972568598,0.840461905,0,-0.317995112,-0.916605968,-1.097951702,-0.882094443,,,,,,,,,,
10Y,9.459319348,5.24784503,2.126397839,0.936019485,0,-0.371497208,-1.119129386,-1.426048012,-1.266737679,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
WSA,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
3M,22.55034953,6.28060709,0.744749395,-0.017592948,0,0.190959777,1.131886745,2.66980541,4.168811468,,,,,,,,,,
6M,18.07934327,5.518183038,0.96549076,0.18809114,0,0.052355906,0.54368373,1.536622924,2.593729168,,,,,,,,,,
7Y,10.62443244,5.816540324,2.343224259,1.031917981,0,-0.412016685,-1.257564234,-1.649180639,-1.532972916,,,,,,,,,,
10Y,10.2638564,5.852399925,2.4643242,1.109968746,0,-0.456779748,-1.423193984,-1.891203496,-1.780995164,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
QWE,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
3M,24.16571236,11.00234028,3.616140238,1.372458127,0,-0.391544886,-0.667506579,0.14072811,1.383460652,,,,,,,,,,
6M,19.91175493,9.145274204,3.081024577,1.210736786,0,-0.3870637,-0.86677434,-0.527821288,0.264048438,,,,,,,,,,
7Y,11.64557121,6.407356397,2.580801365,1.127486247,0,-0.435819166,-1.246404662,-1.423328511,-1.062914852,,,,,,,,,,
10Y,11.17357136,6.365218906,2.660755918,1.183204244,0,-0.465777829,-1.330438184,-1.478521941,-1.062087615,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,
KJH,-300,-200,-100,-50,0,50,100,200,300,,,,,,,,,,
3M,26.4342366,12.98064585,4.792483456,1.987329468,0,-0.686653993,-1.655899651,-1.293878676,-0.212921367,,,,,,,,,,
6M,21.78094891,10.72050444,3.987423025,1.677647978,0,-0.611560644,-1.645687343,-1.71856794,-1.102346407,,,,,,,,,,
7Y,12.12092879,6.673612202,2.693369334,1.176688393,0,-0.453003854,-1.280840196,-1.425003492,-1.018638868,,,,,,,,,,
10Y,11.68205606,6.630545933,2.763086374,1.224651527,0,-0.476853138,-1.333363294,-1.418332068,-0.942887045,,,,,,,,,,
lcerni
May 2nd, 2011 6:56pm
Question: Do the various sections of this output all have the same number of fields? Even the ones with just commas?
If so, you could probably handle this with one Flat File Destination adapter that has String fields named Col1 through ColX and then have multiple data flows on the control Flow. each Data Flow would load one section into the flat file.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
May 2nd, 2011 10:11pm
i thing you can use Bulk Copy Command Line utility tool to export table data into text file based on your requirement...
please refer the Bulk Copy usage..http://msdn.microsoft.com/en-us/library/ms191485.aspx
-- Sathish
May 3rd, 2011 7:13am
I would like to recommend to use script task to write this data into the text file. Have a execute SQL task to get the required output. For that as mentioned by Todd C , you should have the same number of columns on all the above batches.If not you have
to have seperate execute SQL task for each and a script task to write the same into text file.
For how to use script task to write to file ...check the below thread
http://social.msdn.microsoft.com/Forums/en-NZ/sqlintegrationservices/thread/82639c70-1954-4c15-9f6e-2ece1c69ef78
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 7:44am
Yes, the output for each each query should have the same number of columns. That is why all the commas at the end of the lines.lcerni
May 3rd, 2011 7:59am
If all the columns are going to be same , you can make union of all the SELECT statement . follow the below steps
1. Add a data flow component to the package
2. Inside data flow component , add a OLEDB source component.
3. In the source component , change the data access mode to SQL Command.
4. In the SQL Command text box, put the UNION select statement.
5. Then add Flat file destination to the data flow area with the flatfile connection to the destination file.
6. Connect the source and the flat file detination components.
Here is the altered select statment with UNION
--============================================
DECLARE @ValuateDate DATETIME
SET @ValuateDate = '2010-12-31 00:00:00.000'
DECLARE @AsOfDate DATETIME
SET @AsOfDate = '2010-12-31 00:00:00.000'
DECLARE @yDate DATETIME
SET @yDate = '2010-12-31 00:00:00.000'
CREATE TABLE #TTXYZ (OutputXYZ VARCHAR(2000))
INSERT INTO #TTXYZ
SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200'
DECLARE @FileDate DATETIME
SET @FileDate = '2010-12-31 00:00:00.000'
INSERT INTO #TTXYZ
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[40]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[60]) + ',' +
CONVERT(VARCHAR(250), A.[70]) + ',' +
CONVERT(VARCHAR(250), A.[75]) + ',' +
CONVERT(VARCHAR(250), A.[80]) + ',' +
CONVERT(VARCHAR(250), A.[85]) + ',' +
CONVERT(VARCHAR(250), A.[90]) + ',' +
CONVERT(VARCHAR(250), A.[95]) + ',' +
CONVERT(VARCHAR(250), A.[100])+ ',' +
CONVERT(VARCHAR(250), A.[105])+ ',' +
CONVERT(VARCHAR(250), A.[110])+ ',' +
CONVERT(VARCHAR(250), A.[115])+ ',' +
CONVERT(VARCHAR(250), A.[120])+ ',' +
CONVERT(VARCHAR(250), A.[125])+ ',' +
CONVERT(VARCHAR(250), A.[130])+ ',' +
CONVERT(VARCHAR(250), A.[140])+ ',' +
CONVERT(VARCHAR(250), A.[150])+ ',' +
CONVERT(VARCHAR(250), A.[200])
FROM
(
SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
FROM
(SELECT Term, Percentile, Strike
FROM XYZ
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT CONVERT(VARCHAR, xdate, 112) + ',' +
xperiod + ',' +
CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,'
FROM RC
WHERE [Status] = 'A'
AND xdate = @AsOfDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT yperiod + ',' +
CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,'
FROM DY
WHERE [Status] = 'A'
AND yDate = @yDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT * FROM #TTXYZ
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
DROP TABLE #TTXYZ
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 8:31am
If all the columns are going to be same , you can make union of all the SELECT statement . follow the below steps
1. Add a data flow component to the package
2. Inside data flow component , add a OLEDB source component.
3. In the source component , change the data access mode to SQL Command.
4. In the SQL Command text box, put the UNION select statement.
5. Then add Flat file destination to the data flow area with the flatfile connection to the destination file.
6. Connect the source and the flat file detination components.
May 3rd, 2011 8:31am
Eror at Data Flow Task [Source - TTLJ[[1]]:SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared."
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: )x80040E14 Desciption: "Invalid object name '#TTVC'"
Additional information: Exception from HRESULT: 0xC0202009(Micorsoft.sqlserver.DTSPipelineWrap)
--======================
This is the error that I get when I try to copy and paste my code to SQL Command and hit OK. How do I resolve this error?
--======================
This is my code:
DECLARE @ValuateDate DATETIME
DECLARE @AsOfDate DATETIME
DECLARE @DYDate DATETIME
DECLARE @FileDate DATETIME
DECLARE @Tenor3YP VARCHAR(5)
DECLARE @Tenor5YP VARCHAR(5)
DECLARE @Tenor10YP VARCHAR(5)
DECLARE @Tenor20YP VARCHAR(5)
DECLARE @Tenor30YP VARCHAR(5)
SET @ValuateDate = '2011-04-25 00:00:00.000'
SET @AsOfDate = @ValuateDate
SET @DYDate = @ValuateDate
SET @FileDate = @ValuateDate
SET @Tenor3YP = '3Y/P'
SET @Tenor5YP = '5Y/P'
SET @Tenor10YP = '10Y/P'
SET @Tenor20YP = '20Y/P'
SET @Tenor30YP = '30Y/P'
--============================================
------------------
CREATE TABLE #TTVC (OutputVC VARCHAR(2000))
INSERT INTO #TTVC
SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150'
+ ',' + '200'
INSERT INTO #TTVC
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[40]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[60]) + ',' +
CONVERT(VARCHAR(250), A.[70]) + ',' +
CONVERT(VARCHAR(250), A.[75]) + ',' +
CONVERT(VARCHAR(250), A.[80]) + ',' +
CONVERT(VARCHAR(250), A.[85]) + ',' +
CONVERT(VARCHAR(250), A.[90]) + ',' +
CONVERT(VARCHAR(250), A.[95]) + ',' +
CONVERT(VARCHAR(250), A.[100])+ ',' +
CONVERT(VARCHAR(250), A.[105])+ ',' +
CONVERT(VARCHAR(250), A.[110])+ ',' +
CONVERT(VARCHAR(250), A.[115])+ ',' +
CONVERT(VARCHAR(250), A.[120])+ ',' +
CONVERT(VARCHAR(250), A.[125])+ ',' +
CONVERT(VARCHAR(250), A.[130])+ ',' +
CONVERT(VARCHAR(250), A.[140])+ ',' +
CONVERT(VARCHAR(250), A.[150])+ ',' +
CONVERT(VARCHAR(250), A.[200])
FROM
(
SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
FROM
(SELECT Term, Percentile, Strike
FROM VolatilityCurve
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TTSC (OutputSC VARCHAR(2000))
INSERT INTO #TTSC
SELECT 'TERMS' + ',' + '1Y' + ',' + '2Y' + ',' + '3Y' + ',' + '4Y' + ',' + '5Y' + ',' + '7Y' + ',' + '10Y' + ',' + '15Y' + ',' + '20Y' + ',' + '25Y' + ',' + '30Y' + ',,,,,,,,'
INSERT INTO #TTSC
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[1Y]) + ',' +
CONVERT(VARCHAR(250), A.[2Y]) + ',' +
CONVERT(VARCHAR(250), A.[3Y]) + ',' +
CONVERT(VARCHAR(250), A.[4Y]) + ',' +
CONVERT(VARCHAR(250), A.[5Y]) + ',' +
CONVERT(VARCHAR(250), A.[7Y]) + ',' +
CONVERT(VARCHAR(250), A.[10Y]) + ',' +
CONVERT(VARCHAR(250), A.[15Y]) + ',' +
CONVERT(VARCHAR(250), A.[20Y]) + ',' +
CONVERT(VARCHAR(250), A.[25Y]) + ',' +
CONVERT(VARCHAR(250), A.[30Y]) + ',,,,,,,,'
FROM
(
SELECT Term, [1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurve
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '7Y', 11
UNION All
SELECT '10Y', 12
UNION All
SELECT '15Y', 13
UNION All
SELECT '20Y', 14
UNION All
SELECT '25Y', 13
UNION All
SELECT '30Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TT3YP (Output3YP VARCHAR(2000))
INSERT INTO #TT3YP
SELECT @Tenor3YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO #TT3YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurveTenor
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor3YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TT5YP (Output5YP VARCHAR(2000))
INSERT INTO #TT5YP
SELECT @Tenor5YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO #TT5YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurveTenor
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor5YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TT10YP (Output10YP VARCHAR(2000))
INSERT INTO #TT10YP
SELECT @Tenor10YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO #TT10YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurveTenor
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor10YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TT20YP (Output20YP VARCHAR(2000))
INSERT INTO #TT20YP
SELECT @Tenor20YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO #TT20YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurveTenor
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor20YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
CREATE TABLE #TT30YP (Output30YP VARCHAR(2000))
INSERT INTO #TT30YP
SELECT @Tenor30YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO #TT30YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SwaptionCurveTenor
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor30YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
--============================================
--============================================
--============================================
SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT CONVERT(VARCHAR, rydate, 112) + ',' +
ryperiod + ',' +
CONVERT(VARCHAR(250), rymidrate) + ',,,,,,,,,,,,,,,,,'
FROM RiskYieldCurve
WHERE [Status] = 'A'
AND rydate = @AsOfDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT dyperiod + ',' +
CONVERT(VARCHAR(250), dymidrate) + ',,,,,,,,,,,,,,,,,,'
FROM DividendYield
WHERE [Status] = 'A'
AND DYDate = @DYDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TTVC
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TTSC
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TT3YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TT5YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TT10YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TT20YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM #TT30YP
--============================================
DROP TABLE #TT3YP
DROP TABLE #TT5YP
DROP TABLE #TT10YP
DROP TABLE #TT20YP
DROP TABLE #TT30YP
DROP TABLE #TTVC
DROP TABLE #TTSC
--============================================lcerni
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 10:54am
lcerni:
You REALLY need to break this up into manageable portions. AND temp tables (#TT3YP) don't work in Execute SQL Tasks. They might work OK in Source Adapters, but really, I would get this to work with ONE section at a time.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
May 3rd, 2011 11:21am
So lets say I work with the first two queries. In the DataFlow I will have One Source - Query1 which links to Destination - Text1 for the first query. Then I would have a second Source - Query2 which links to a Destination - Text2. How
do I append the results of the second query to the same text file without overriding the output from the first query? Do I need to link Destination - Text1 to Source - Query2 so that it runs sequentially?lcerni
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 12:30pm
Yes, that's the basic idea. See if you can get two Data flows to run, one right after another, that both write to the same text file destination, AND see if you can make it so that the second DFT (and subsequent ones) do NOT overwrite existing data.
If you can get that simple scenario to work, you're half way there. Just need to add the appropriate additional Data Flow Tasks that all dump to the same destination.
If you CAN'T get that to work, then you might try breaking the secitons out onto one Data Flow, with each secition being its own Source Adapter, and then combine them in a series of Union All tasks. (I think this was suggested somewhere along the line here.)
good luck!Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
May 3rd, 2011 12:58pm
I cannot get nothing to work. Any other ideas out there.lcerni
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 2:58pm
I cannot get nothing to work. Any other ideas out there.
lcerni
Hi,
Can you give me more info about your package? or you still didn't create one?
ThanksMCITP - BI 2008 http://asqlb.blogspot.com/
May 3rd, 2011 4:20pm
Convert the #temp table to a table variable. Also Make sure to add a SET NOCOUNT ON at the starting of the script.
Try the below one
--============================================
SET NOCOUNT ON
DECLARE @ValuateDate DATETIME
SET @ValuateDate = '2010-12-31 00:00:00.000'
DECLARE @AsOfDate DATETIME
SET @AsOfDate = '2010-12-31 00:00:00.000'
DECLARE @yDate DATETIME
SET @yDate = '2010-12-31 00:00:00.000'
DECLARE @TTXYZ TABLE (OutputXYZ VARCHAR(2000))
INSERT INTO @TTXYZ
SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' + ',' + '150' + ',' + '200'
DECLARE @FileDate DATETIME
SET @FileDate = '2010-12-31 00:00:00.000'
INSERT INTO @TTXYZ
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[40]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[60]) + ',' +
CONVERT(VARCHAR(250), A.[70]) + ',' +
CONVERT(VARCHAR(250), A.[75]) + ',' +
CONVERT(VARCHAR(250), A.[80]) + ',' +
CONVERT(VARCHAR(250), A.[85]) + ',' +
CONVERT(VARCHAR(250), A.[90]) + ',' +
CONVERT(VARCHAR(250), A.[95]) + ',' +
CONVERT(VARCHAR(250), A.[100])+ ',' +
CONVERT(VARCHAR(250), A.[105])+ ',' +
CONVERT(VARCHAR(250), A.[110])+ ',' +
CONVERT(VARCHAR(250), A.[115])+ ',' +
CONVERT(VARCHAR(250), A.[120])+ ',' +
CONVERT(VARCHAR(250), A.[125])+ ',' +
CONVERT(VARCHAR(250), A.[130])+ ',' +
CONVERT(VARCHAR(250), A.[140])+ ',' +
CONVERT(VARCHAR(250), A.[150])+ ',' +
CONVERT(VARCHAR(250), A.[200])
FROM
(
SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
FROM
(SELECT Term, Percentile, Strike
FROM XYZ
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,'
UNION ALL
SELECT CONVERT(VARCHAR, xdate, 112) + ',' +
xperiod + ',' +
CONVERT(VARCHAR(250), xmidrate) + ',,,,,,,,,,,,,,,,,'
FROM RC
WHERE [Status] = 'A'
AND xdate = @AsOfDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT yperiod + ',' +
CONVERT(VARCHAR(250), ymidrate) + ',,,,,,,,,,,,,,,,,,'
FROM DY
WHERE [Status] = 'A'
AND yDate = @yDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT * FROM @TTXYZ
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
SET NOCOUNT OFF
Free Windows Admin Tool Kit Click here and download it now
May 4th, 2011 1:40am
Under Control Flow created Data Flow Task.
Under Data Flow created OLE DB Source and Flat File Destination Tasks.
Set up OLE DB Source Editor with Data access mode as "SQL command from variable"
Variable name as User::ExecuteNameHere
Under Variables set up Variable ExecuteInput with DataType as String and Value as Execute ProcedureNameHere ParameterHere
Set EvaluateAsExpression as True
Set Expression as "EXECUTE ProcedureNameHere '" + (DT_WSTR, 10) @[User::EvaluationDate] + "'"
CREATE PROCEDURE [dbo].[ProcedureNameHere]
@EvaluationDate DATETIME
AS
BEGIN
SET NOCOUNT ON
--DECLARE @ValuateDate DATETIME
DECLARE @AsOfDate DATETIME
DECLARE @DYDate DATETIME
DECLARE @FileDate DATETIME
DECLARE @Tenor3YP VARCHAR(5)
DECLARE @Tenor5YP VARCHAR(5)
DECLARE @Tenor10YP VARCHAR(5)
DECLARE @Tenor20YP VARCHAR(5)
DECLARE @Tenor30YP VARCHAR(5)
--SET @ValuateDate = '2011-04-25 00:00:00.000'
SET @AsOfDate = @ValuateDate
SET @DYDate = @ValuateDate
SET @FileDate = @ValuateDate
SET @Tenor3YP = '3Y/P'
SET @Tenor5YP = '5Y/P'
SET @Tenor10YP = '10Y/P'
SET @Tenor20YP = '20Y/P'
SET @Tenor30YP = '30Y/P'
--============================================
--NEED TO CREATE TEMP TABLES BECAUSE OF ORDER BY
------------------
DECLARE @TTVC TABLE (OutputVC VARCHAR(500))
INSERT INTO @TTVC
SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130' + ',' + '140' +
',' + '150' + ',' + '200'
INSERT INTO @TTVC
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[40]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[60]) + ',' +
CONVERT(VARCHAR(250), A.[70]) + ',' +
CONVERT(VARCHAR(250), A.[75]) + ',' +
CONVERT(VARCHAR(250), A.[80]) + ',' +
CONVERT(VARCHAR(250), A.[85]) + ',' +
CONVERT(VARCHAR(250), A.[90]) + ',' +
CONVERT(VARCHAR(250), A.[95]) + ',' +
CONVERT(VARCHAR(250), A.[100])+ ',' +
CONVERT(VARCHAR(250), A.[105])+ ',' +
CONVERT(VARCHAR(250), A.[110])+ ',' +
CONVERT(VARCHAR(250), A.[115])+ ',' +
CONVERT(VARCHAR(250), A.[120])+ ',' +
CONVERT(VARCHAR(250), A.[125])+ ',' +
CONVERT(VARCHAR(250), A.[130])+ ',' +
CONVERT(VARCHAR(250), A.[140])+ ',' +
CONVERT(VARCHAR(250), A.[150])+ ',' +
CONVERT(VARCHAR(250), A.[200])
FROM
(
SELECT Term, [40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200]
FROM
(SELECT Term, Percentile, Strike
FROM VC
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([40],[50],[60],[70],[75],[80],[85],[90],[95],[100],[105],[110],[115],[120],[125],[130],[140],[150],[200])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TTSC TABLE (OutputSC VARCHAR(500))
INSERT INTO @TTSC
SELECT 'TERMS' + ',' + '1Y' + ',' + '2Y' + ',' + '3Y' + ',' + '4Y' + ',' + '5Y' + ',' + '7Y' + ',' + '10Y' + ',' + '15Y' + ',' + '20Y' + ',' + '25Y' + ',' + '30Y' + ',,,,,,,,'
INSERT INTO @TTSC
SELECT
CONVERT(VARCHAR(250), A.Term )+ ',' +
CONVERT(VARCHAR(250), A.[1Y]) + ',' +
CONVERT(VARCHAR(250), A.[2Y]) + ',' +
CONVERT(VARCHAR(250), A.[3Y]) + ',' +
CONVERT(VARCHAR(250), A.[4Y]) + ',' +
CONVERT(VARCHAR(250), A.[5Y]) + ',' +
CONVERT(VARCHAR(250), A.[7Y]) + ',' +
CONVERT(VARCHAR(250), A.[10Y]) + ',' +
CONVERT(VARCHAR(250), A.[15Y]) + ',' +
CONVERT(VARCHAR(250), A.[20Y]) + ',' +
CONVERT(VARCHAR(250), A.[25Y]) + ',' +
CONVERT(VARCHAR(250), A.[30Y]) + ',,,,,,,,'
FROM
(
SELECT Term, [1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y]
FROM
(SELECT Term, Percentile, Strike
FROM SC
WHERE [Status] = 'A'
AND FileDate=@FileDate
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([1Y],[2Y],[3Y],[4Y],[5Y],[7Y],[10Y],[15Y],[20Y],[25Y],[30Y])
) AS pvt
) A,
(
SELECT 'Terms' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '7Y', 11
UNION All
SELECT '10Y', 12
UNION All
SELECT '15Y', 13
UNION All
SELECT '20Y', 14
UNION All
SELECT '25Y', 13
UNION All
SELECT '30Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TT3YP TABLE (Output3YP VARCHAR(500))
INSERT INTO @TT3YP
SELECT @Tenor3YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO @TT3YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SCT
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor3YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TT5YP TABLE (Output5YP VARCHAR(500))
INSERT INTO @TT5YP
SELECT @Tenor5YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO @TT5YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SCT
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor5YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TT10YP TABLE (Output10YP VARCHAR(500))
INSERT INTO @TT10YP
SELECT @Tenor10YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO @TT10YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SCT
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor10YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TT20YP TABLE (Output20YP VARCHAR(500))
INSERT INTO @TT20YP
SELECT @Tenor20YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO @TT20YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SCT
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor20YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
DECLARE @TT30YP TABLE (Output30YP VARCHAR(500))
INSERT INTO @TT30YP
SELECT @Tenor30YP + ',' + '-300' + ',' + '-200' + ',' + '-100' + ',' + '-50' + ',' + '0' + ',' + '50' + ',' + '100' + ',' + '200' + ',' + '300' + ',,,,,,,,,,'
INSERT INTO @TT30YP
SELECT
CONVERT(VARCHAR(250), A.Term ) + ',' +
CONVERT(VARCHAR(250), A.[-300]) + ',' +
CONVERT(VARCHAR(250), A.[-200]) + ',' +
CONVERT(VARCHAR(250), A.[-100]) + ',' +
CONVERT(VARCHAR(250), A.[-50]) + ',' +
CONVERT(VARCHAR(250), A.[0]) + ',' +
CONVERT(VARCHAR(250), A.[50]) + ',' +
CONVERT(VARCHAR(250), A.[100]) + ',' +
CONVERT(VARCHAR(250), A.[200]) + ',' +
CONVERT(VARCHAR(250), A.[300]) + ',,,,,,,,,,'
FROM
(
SELECT Term, [-300],[-200],[-100],[-50],[0],[50],[100],[200],[300]
FROM
(SELECT Term, Percentile, Strike
FROM SCT
WHERE [Status] = 'A'
AND FileDate = @FileDate
AND Tenor = @Tenor30YP
) ps
PIVOT
(
SUM (Strike)
FOR Percentile IN ([-300],[-200],[-100],[-50],[0],[50],[100],[200],[300])
) AS pvt
) A,
(
SELECT 'Term' AS Term, 0 AS [Order]
UNION All
SELECT '1M' AS Term, 1 AS [Order]
UNION All
SELECT '3M', 2
UNION All
SELECT '6M', 3
UNION All
SELECT '9M', 4
UNION All
SELECT '1Y', 5
UNION All
SELECT '2Y', 6
UNION All
SELECT '3Y', 7
UNION All
SELECT '4Y', 8
UNION All
SELECT '5Y', 9
UNION All
SELECT '6Y', 10
UNION All
SELECT '7Y', 11
UNION All
SELECT '8Y', 12
UNION All
SELECT '9Y', 13
UNION All
SELECT '10Y', 14
) B
WHERE A.Term = B.Term
ORDER BY [Order]
------------------
--============================================
DECLARE @TTLJC TABLE (Col1 VARCHAR(500))
INSERT INTO @TTLJC
-- SELECT 'Col1'
--UNION ALL
SELECT 'Valuate Date' + ',,' + CONVERT(VARCHAR, @ValuateDate, 101) + ',,,,,,,,,,,,,,,,,' AS Col1
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,' AS Col1
--============================================
UNION ALL
SELECT 'AsOfDate' + ',,' + 'MidRate' + ',,,,,,,,,,,,,,,,,' AS Col1
UNION ALL
SELECT CONVERT(VARCHAR, rydate, 112) + ',' +
ryperiod + ',' +
CONVERT(VARCHAR(250), rymidrate) + ',,,,,,,,,,,,,,,,,'
FROM RiskYieldCurve
WHERE [Status] = 'A'
AND rydate = @AsOfDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT 'DivDate' + ',' + 'MidRate' + ',,,,,,,,,,,,,,,,,,'
UNION ALL
SELECT dyperiod + ',' +
CONVERT(VARCHAR(250), dymidrate) + ',,,,,,,,,,,,,,,,,,'
FROM DividendYield
WHERE [Status] = 'A'
AND DYDate = @DYDate
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TTVC
--DOING IT THIS WAY BECAUSE OF ORDER BY
-- SELECT 'TERMS' + ',' + '40' + ',' + '50' + ',' + '60' + ',' + '70' + ',' + '75' + ',' + '80' + ',' + '85' + ',' + '90' + ',' + '95' + ',' + 'ATM' + ',' + '105' + ',' + '110' + ',' + '115' + ',' + '120' + ',' + '125' + ',' + '130'
+ ',' + '140' + ',' + '150' + ',' + '200'
--
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TTSC
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TT3YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TT5YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TT10YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TT20YP
UNION ALL
SELECT ',,,,,,,,,,,,,,,,,,,'
--============================================
UNION ALL
SELECT * FROM @TT30YP
--============================================
SELECT CAST(Col1 AS NVARCHAR(500)) AS Col1 FROM @TTLJC
--============================================
--============================================
ENDlcerni
July 12th, 2011 1:57pm