bwFAw74t

1CREATE PROCEDURE sp_CreateWeeklyRowBasedStockDataTable
2AS 
3BEGIN
4	/*********************************************/
5	/* Step 1.                                   */
6	/* Get names for columns in select statement */
7	/*********************************************/
8	SET DATEFIRST 1
9
10	DECLARE @columnNames NVARCHAR(MAX) = 
11		(SELECT (
12			SELECT DISTINCT TOP 1023 '['
13			+ CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + '-' + RIGHT(Replicate('0', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2)
14			+ '], '
15			FROM company_stocks
16			WHERE	DATEPART(DW, company_stocks_date) = 1
17			ORDER BY 1 DESC
18			FOR XML PATH('')
19			) AS names)
20			
21	SET @columnNames = SUBSTRING(@columnNames, 1, (len(@columnNames) -1))
22
23	/*******************************************/
24	/* Step 2.								   */
25	/* Get names for creating a table with one */
26	/* column for each record				   */
27	/*******************************************/
28	DECLARE @tableColumnNames NVARCHAR(MAX) = 
29		(SELECT (
30			SELECT DISTINCT TOP 1023 '[' 
31				+ CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + '-' + RIGHT(Replicate('0', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2)
32				+ '] smallmoney NULL, '
33			FROM company_stocks
34			WHERE	DATEPART(DW, company_stocks_date) = 1
35			ORDER BY 1 DESC
36			FOR XML PATH('')
37			) AS names)
38			
39	/******************/
40	/* Step 3.        */
41	/* Generate table */
42	/******************/
43	DECLARE @query NVARCHAR(MAX) = 
44	'CREATE TABLE rowBasedStockDataWeekly (
45		company_stocks_symbol CHAR(7) NOT NULL,'
46	+ @tableColumnNames
47	+ ' PRIMARY KEY (company_stocks_symbol))'
48	
49	DROP TABLE IF EXISTS rowBasedStockDataWeekly
50	EXEC sp_executesql @query
51
52	/***************/
53	/* Step 4.     */
54	/* Fill table  */
55	/***************/
56	SET @query=' 
57	DECLARE @temp_company_stocks TABLE 
58		(
59		company_stocks_symbol char(7),
60		company_stocks_date char(7),
61		company_stocks_adjusted_price_close smallmoney
62		)
63
64	INSERT INTO @temp_company_stocks
65	SELECT	company_stocks_symbol,      
66			( CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + ''-'' + RIGHT(Replicate(''0'', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2) ) as company_stocks_date,      
67			avg(company_stocks_adjusted_price_close)
68	FROM	company_stocks
69	GROUP BY company_stocks_symbol, ( CAST(DATEPART(yy,company_stocks_date) as VARCHAR(MAX)) + ''-'' + RIGHT(Replicate(''0'', 2) + CAST( DATEPART(wk, company_stocks_date) AS NVARCHAR), 2) )
70
71	INSERT INTO rowBasedStockDataWeekly
72	SELECT	company_stocks_symbol, 
73			'
74				+ @columnNames + 
75			'
76	FROM	@temp_company_stocks
77	PIVOT( 
78		avg(company_stocks_adjusted_price_close) 
79		for company_stocks_date 
80		IN ( ' + @columnNames + ')
81	)
82	AS Tests'
83
84	EXEC sp_executesql @query
85END
86GO