January 31, 2018 at 11:24 am
I have a data table which has RowNo, EntityName, Value, KeyYear.
RowNo is Unique. EntityName and KeyYear combination is unique though you dont see constraint. Some of the middle rows should have calculations. Its easy to achieve this in excel but how in SQL ?
In this example: 1c should have calculated value of (1b-1a) for that KeyYear.
4c should have calculated value (1c+2a+3a) for that KeyYear. declare @data as table
(
RowNo TinyInt Unique Not Null,
EntityName Varchar(4),
[Value] Int,
KeyYear Int
)
Insert into @data
Values (1, '1', 100, 2015), (2, '1a', 200,2015), (3, '1b', 300,2015), (4, '1c', Null,2015), (5, '2a', 300,2015), (6, '3a', 400,2015), (7, '4c', Null,2015)
,(8, '1', 100, 2017),(9, '1a', 220, 2017), (10, '1b', 550, 2017), (11, '1c', NULL, 2017)
,(12, '2a', 120, 2017), (13, '3a', 350, 2017), (14, '4c', NULL, 2017)
select * from @data
I am not sure if there are any inbuilt functions that can help here. Can you please help with the query.
Output result (replacing 1c and 4c Nulls) should be like below that has calculation results in the value field for 1c and 4c entityname within KeyYear group.
RowNo EntityName Value KeyYear
1 1 100 2015
2 1a 200 2015
3 1b 300 2015
4 1c 100 2015
5 2a 300 2015
6 3a 400 2015
7 4c 800 2015
8 1 100 2017
9 1a 220 2017
10 1b 550 2017
11 1c 330 2017
12 2a 120 2017
13 3a 350 2017
14 4c 800 2017
Thanks,
Naveen.
Every thought is a cause and every condition an effect
January 31, 2018 at 12:10 pm
Naveen PK - Wednesday, January 31, 2018 11:24 AMIn this example: 1c should have calculated value of (1b-1a) for that KeyYear.
4c should have calculated value (1c+2a+3a) for that KeyYear.
Will it always be like this? What defines the formula?
January 31, 2018 at 12:39 pm
Luis Cazares - Wednesday, January 31, 2018 12:10 PMNaveen PK - Wednesday, January 31, 2018 11:24 AMIn this example: 1c should have calculated value of (1b-1a) for that KeyYear.
4c should have calculated value (1c+2a+3a) for that KeyYear.Will it always be like this? What defines the formula?
Formula varies but does not use aggregations. It only uses basic math operations (one of +-/*)
Thanks,
Naveen.
Every thought is a cause and every condition an effect
January 31, 2018 at 1:12 pm
Naveen PK - Wednesday, January 31, 2018 12:39 PMLuis Cazares - Wednesday, January 31, 2018 12:10 PMNaveen PK - Wednesday, January 31, 2018 11:24 AMIn this example: 1c should have calculated value of (1b-1a) for that KeyYear.
4c should have calculated value (1c+2a+3a) for that KeyYear.Will it always be like this? What defines the formula?
Formula varies but does not use aggregations. It only uses basic math operations (one of +-/*)
What defines the formula?
January 31, 2018 at 1:14 pm
Luis Cazares - Wednesday, January 31, 2018 1:12 PMNaveen PK - Wednesday, January 31, 2018 12:39 PMLuis Cazares - Wednesday, January 31, 2018 12:10 PMNaveen PK - Wednesday, January 31, 2018 11:24 AMIn this example: 1c should have calculated value of (1b-1a) for that KeyYear.
4c should have calculated value (1c+2a+3a) for that KeyYear.Will it always be like this? What defines the formula?
Formula varies but does not use aggregations. It only uses basic math operations (one of +-/*)
What defines the formula?
Business defines the formula. For simplicity, assume that formula is fixed in this case.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
January 31, 2018 at 1:27 pm
Naveen PK - Wednesday, January 31, 2018 1:14 PMBusiness defines the formula. For simplicity, assume that formula is fixed in this case.
In this case, you could use something like this:
WITH CTE AS(
SELECT RowNo,
EntityName,
Value,
KeyYear,
SUM( CASE WHEN EntityName = '1a' THEN -[Value]
WHEN EntityName = '1' THEN 0
ELSE Value END) OVER (PARTITION BY KeyYear ORDER BY RowNo ROWS UNBOUNDED PRECEDING) AS Calc_Value
FROM @data
)
UPDATE CTE SET
CTE.Value = CTE.Calc_Value
WHERE CTE.Value IS NULL;
Obviously, that won't help if the formula changes.
January 31, 2018 at 1:39 pm
Luis Cazares - Wednesday, January 31, 2018 1:27 PMNaveen PK - Wednesday, January 31, 2018 1:14 PMBusiness defines the formula. For simplicity, assume that formula is fixed in this case.
In this case, you could use something like this:
WITH CTE AS(
SELECT RowNo,
EntityName,
Value,
KeyYear,
SUM( CASE WHEN EntityName = '1a' THEN -[Value]
WHEN EntityName = '1' THEN 0
ELSE Value END) OVER (PARTITION BY KeyYear ORDER BY RowNo ROWS UNBOUNDED PRECEDING) AS Calc_Value
FROM @data
)
UPDATE CTE SET
CTE.Value = CTE.Calc_Value
WHERE CTE.Value IS NULL;Obviously, that won't help if the formula changes.
Its good approach but might not work exactly when formula refers random rows for division/multiplication.
For example: in case of 4c entityname, the formula can be 3a-1c within same keyyear.
Thanks,
Naveen.
Every thought is a cause and every condition an effect
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply