April 18, 2018 at 2:29 pm
I have some values in a table that need calculations performing on them as follows;
Data
Date Tag Value
1/1/2018 X 2
1/1/2018 A 3
1/1/2018 B 4
1/1/2018 C 5
1/2/2018 X 5
1/2/2018 A 31
1/2/2018 B 42
1/2/2018 C 53
The caculation needs to be at the day level so for 1/1/2018
calcA = X*A = 2*3 = 6
calcB = X*B = 2*4 = 8
calcC = X*C = 2*5 = 10
and for 1/2/2018
calcA = X*A = 2*31 = 62
calcB = X*B = 2*42 = 84
calcC = X*C = 2*53 = 106
How do I structure the query to achieve this?
Stephen
April 18, 2018 at 2:54 pm
Stephen Yale - Wednesday, April 18, 2018 2:29 PMI have some values in a table that need calculations performing on them as follows;
Data
Date Tag Value
1/1/2018 X 2
1/1/2018 A 3
1/1/2018 B 4
1/1/2018 C 5
1/2/2018 X 5
1/2/2018 A 31
1/2/2018 B 42
1/2/2018 C 53The caculation needs to be at the day level so for 1/1/2018
calcA = X*A = 2*3 = 6
calcB = X*B = 2*4 = 8
calcC = X*C = 2*5 = 10and for 1/2/2018
calcA = X*A = 2*31 = 62
calcB = X*B = 2*42 = 84
calcC = X*C = 2*53 = 106How do I structure the query to achieve this?
Stephen
I am unable to see why your 'X' value for 1/2/2018 is 2, and not 5.
So, assuming that it is 5, the following SQL will do the trick/*
CREATE TABLE #Data (
[Date] DATE NOT NULL
, Tag CHAR(1) NOT NULL
, Value INT NOT NULL
);
INSERT INTO #Data ( [Date], Tag, Value )
VALUES
( '1/1/2018', 'X', 2 )
, ( '1/1/2018', 'A', 3 )
, ( '1/1/2018', 'B', 4 )
, ( '1/1/2018', 'C', 5 )
, ( '1/2/2018', 'X', 5 )
, ( '1/2/2018', 'A', 31 )
, ( '1/2/2018', 'B', 42 )
, ( '1/2/2018', 'C', 53 );
*/
WITH cteX AS (
SELECT *
FROM #Data
WHERE Tag = 'X'
)
, cteOther AS (
SELECT *
FROM #Data
WHERE Tag != 'X'
)
SELECT
x.[Date]
, Formula = QUOTENAME(x.Tag) + ' * ' + QUOTENAME(o.Tag)
, FormulaValues = QUOTENAME(x.Value) + ' * ' + QUOTENAME(o.Value)
, Result = x.Value * o.Value
FROM cteX AS x
CROSS APPLY (
SELECT d.*
FROM #Data AS d
WHERE d.[Date] = x.[Date]
AND d.Tag != 'X'
) AS o;
April 18, 2018 at 3:15 pm
Yes it should have been 5.
I should have also said how I want the results! I can just union together
Date Tag Value
1/1/2018 A 3
1/1/2018 B 4
1/1/2018 C 5
1/1/2018 calcA 6
1/1/2018 calcB 8
1/1/2018 calcC 10
1/1/2018 X 2
1/2/2018 A 31
1/2/2018 B 42
1/2/2018 C 53
1/2/2018 calcA 62
1/2/2018 calcB 84
1/2/2018 calcC 106
1/2/2018 X 5
Thank you for you swift reply!
Stephen
April 18, 2018 at 10:27 pm
Stephen Yale - Wednesday, April 18, 2018 3:15 PMYes it should have been 5.
I should have also said how I want the results! I can just union togetherDate Tag Value
1/1/2018 A 3
1/1/2018 B 4
1/1/2018 C 5
1/1/2018 calcA 6
1/1/2018 calcB 8
1/1/2018 calcC 10
1/1/2018 X 2
1/2/2018 A 31
1/2/2018 B 42
1/2/2018 C 53
1/2/2018 calcA 62
1/2/2018 calcB 84
1/2/2018 calcC 106
1/2/2018 X 5
Thank you for you swift reply!Stephen
WITH cteX AS (
SELECT *
FROM #Data
WHERE Tag = 'X'
)
SELECT [Date], Tag, Value
FROM #Data AS o
UNION ALL
SELECT
x.[Date]
, Tag = 'calc' + c.Tag
, Value = x.Value * c.Value
FROM cteX AS x
CROSS APPLY (
SELECT d.*
FROM #Data AS d
WHERE d.[Date] = x.[Date]
AND d.Tag != 'X'
) AS c;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply