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 2008 Forums
 Transact-SQL (2008)
 Carry Over Calc in a Dynamic Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kalaiselvan
Posting Yak Master

India
110 Posts

Posted - 02/05/2013 :  03:54:57  Show Profile  Reply with Quote
Hi,
Below were the 2 tables. Both were Dynamic. We cant determine the number of Columns in both tables. Columns might increased.
From the 2nd Table data I need a calculated Table.

TABLE A:

[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 90 100 120
3 75 85 110

TABLE B:

[ID] [COLUMNS] [CO] [EQ]
1 COL1 0.2 100
2 COL2 0.5 110
3 COL3 0 90

From Table B Column [CO] is used for calculation. Output table will be

[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 90+(100*0.2) 100+(110*0.5) 120
3 75+((90+(100*0.2))*0.2) 85+((100+(110*0.5))*0.5) 110

If [CO] applied for a column then the Calculation starts. 1st Row will be as it is. And 2nd row will be added to the 1st Row*[CO]. Like the same for all rows. If [CO] is 0 then no calc.

Output Will be;
[ID] [COL1] [COL2] [COL3]
1 100 110 150
2 110 155 120
3 97 162.5 110

Please help me to fix this with a Query. Note that the Columns will be dynamic.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  04:02:03  Show Profile  Reply with Quote
see below. this should give you idea to get started

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx



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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  04:15:38  Show Profile  Reply with Quote
here's the full illustration for your example


declare @tableA table
(
[ID] int,
[COL1] int,
[COL2] int,
[COL3] int
)
insert @tableA
values(1, 100, 110, 150),
(2, 90, 100, 120),
(3, 75, 85, 110)

declare @tableB table
(
[ID] int,
[COLUMNS] varchar(10),
 [CO] decimal(5,1),
 [EQ] int
)

insert @tableB
values(1, 'COL1', 0.2, 100),
(2, 'COL2', 0.5, 110),
(3, 'COL3', 0, 90)

;With CTE1
AS
(
SELECT *
FROM @tableA t
UNPIVOT( [Val] FOR [COLUMNS] IN ([COL1],[COL2],[COL3]))u
),
CTE2
AS
(
SELECT ID,CAST(Val AS float) AS Val,COLUMNS
FROM CTE1 c1
WHERE NOT EXISTS(SELECT 1 FROM CTE1 WHERE ID < c1.ID)
UNION ALL
SELECT c1.ID,CAST(c1.Val + (c2.Val * b.CO) AS float),c1.COLUMNS
FROM CTE2 c2
INNER JOIN CTE1 c1
ON c1.ID = c2.ID + 1 
AND c1.COLUMNS=c2.COLUMNS
INNER JOIN @tableB b
ON b.COLUMNS=c2.COLUMNS
)

SELECT *
FROM CTE2
PIVOT(SUM(Val) FOR COLUMNS IN ([COL1],[COL2],[COL3]))p

output
----------------------------------------------
ID	COL1	COL2	COL3
1	100	110	150
2	110	155	120
3	97	162.5	110



------------------------------------------------------------------------------------------------------
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.05 seconds. Powered By: Snitz Forums 2000