Carry Over Calc in a Dynamic Table

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)

Carry Over Calc in a Dynamic Table

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-02-05 : 03:54:57

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

52326 Posts

Posted - 2013-02-05 : 04:02:03

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 04:15:38

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources