March 19, 2013 at 10:35 am
Hi All,
I have a below table and need to divide one row by another row based on the formula.
Date Name ID Result
3/18/2013A15
3/18/2013B216
3/18/2013C321
3/18/2013D411
3/18/2013E545
3/18/2013F622
3/18/2013G75
3/19/2013A12
3/19/2013B27
3/19/2013C315
3/19/2013D49
3/19/2013E519
3/19/2013F612
3/19/2013G73
Please suggest how to proceed to get below result. Formula Column is used to get Result(%) column.
FormulaDateResult(%)
B/A3/19/20130.285714286
D/C3/19/20130.6
F/(E-G)3/19/20130.75
B/A3/18/20130.3125
D/C3/19/20130.523809524
F/(E-G)3/19/20130.55
Thanks in advance.
March 19, 2013 at 11:19 am
the OP's data as sample data:
i got that far, but my eyes blurred on the expectations:
With MySampleData (Date, Name, ID, Result )
AS
(
SELECT CONVERT(datetime,'3/18/2013') ,'A',1,5 UNION ALL
SELECT '3/18/2013','B',2,16 UNION ALL
SELECT '3/18/2013','C',3,21 UNION ALL
SELECT '3/18/2013','D',4,11 UNION ALL
SELECT '3/18/2013','E',5,45 UNION ALL
SELECT '3/18/2013','F',6,22 UNION ALL
SELECT '3/18/2013','G',7,5 UNION ALL
SELECT '3/19/2013','A',1,2 UNION ALL
SELECT '3/19/2013','B',2,7 UNION ALL
SELECT '3/19/2013','C',3,15 UNION ALL
SELECT '3/19/2013','D',4,9 UNION ALL
SELECT '3/19/2013','E',5,19 UNION ALL
SELECT '3/19/2013','F',6,12 UNION ALL
SELECT '3/19/2013','G',7,3
)
select * from MySampleData
Lowell
March 19, 2013 at 11:35 am
Most of OP output results do not match relevant formulas, eg:
B/A 3/19/2013= 7/ 2 = 3.5 not 0.285714286!
Any way thank's to Lowell for data setup:
declare @MySampleData table (Date datetime, Name char(1), ID int, Result DECIMAL(20,9))
insert @MySampleData
SELECT CONVERT(datetime,'3/18/2013') ,'A',1,5 UNION ALL
SELECT '3/18/2013','B',2,16 UNION ALL
SELECT '3/18/2013','C',3,21 UNION ALL
SELECT '3/18/2013','D',4,11 UNION ALL
SELECT '3/18/2013','E',5,45 UNION ALL
SELECT '3/18/2013','F',6,22 UNION ALL
SELECT '3/18/2013','G',7,5 UNION ALL
SELECT '3/19/2013','A',1,2 UNION ALL
SELECT '3/19/2013','B',2,7 UNION ALL
SELECT '3/19/2013','C',3,15 UNION ALL
SELECT '3/19/2013','D',4,9 UNION ALL
SELECT '3/19/2013','E',5,19 UNION ALL
SELECT '3/19/2013','F',6,12 UNION ALL
SELECT '3/19/2013','G',7,3
;WITH cte_dat
AS
(
SELECT *
,CASE WHEN Name IN ('B','A') THEN 'B/A'
WHEN Name IN ('D','C') THEN 'D/C'
WHEN Name IN ('E','G') THEN 'E-G'
ELSE NULL
END AS Formula
FROM @MySampleData
)
SELECT Formula
,Date
,CASE WHEN Formula = 'B/A' THEN
MAX(CASE WHEN Name = 'B' THEN Result ELSE NULL END)/MAX(CASE WHEN Name = 'A' THEN Result ELSE NULL END)
WHEN Formula = 'D/C' THEN
MAX(CASE WHEN Name = 'D' THEN Result ELSE NULL END)/MAX(CASE WHEN Name = 'C' THEN Result ELSE NULL END)
WHEN Formula = 'E-G' THEN
MAX(CASE WHEN Name = 'E' THEN Result ELSE NULL END)-MAX(CASE WHEN Name = 'G' THEN Result ELSE NULL END)
END AS Result
FROM cte_dat
WHERE Formula IS NOT NULL
GROUP BY Formula, Date
ORDER BY Date, Formula
March 19, 2013 at 11:42 am
Eugene Elutin (3/19/2013)
Most of OP output results do not match relevant formulas, eg:B/A 3/19/2013= 7/ 2 = 3.5 not 0.285714286!
Any way thank's to Lowell for data setup:
declare @MySampleData table (Date datetime, Name char(1), ID int, Result DECIMAL(20,9))
insert @MySampleData
SELECT CONVERT(datetime,'3/18/2013') ,'A',1,5 UNION ALL
SELECT '3/18/2013','B',2,16 UNION ALL
SELECT '3/18/2013','C',3,21 UNION ALL
SELECT '3/18/2013','D',4,11 UNION ALL
SELECT '3/18/2013','E',5,45 UNION ALL
SELECT '3/18/2013','F',6,22 UNION ALL
SELECT '3/18/2013','G',7,5 UNION ALL
SELECT '3/19/2013','A',1,2 UNION ALL
SELECT '3/19/2013','B',2,7 UNION ALL
SELECT '3/19/2013','C',3,15 UNION ALL
SELECT '3/19/2013','D',4,9 UNION ALL
SELECT '3/19/2013','E',5,19 UNION ALL
SELECT '3/19/2013','F',6,12 UNION ALL
SELECT '3/19/2013','G',7,3
;WITH cte_dat
AS
(
SELECT *
,CASE WHEN Name IN ('B','A') THEN 'B/A'
WHEN Name IN ('D','C') THEN 'D/C'
WHEN Name IN ('E','G') THEN 'E-G'
ELSE NULL
END AS Formula
FROM @MySampleData
)
SELECT Formula
,Date
,CASE WHEN Formula = 'B/A' THEN
MAX(CASE WHEN Name = 'B' THEN Result ELSE NULL END)/MAX(CASE WHEN Name = 'A' THEN Result ELSE NULL END)
WHEN Formula = 'D/C' THEN
MAX(CASE WHEN Name = 'D' THEN Result ELSE NULL END)/MAX(CASE WHEN Name = 'C' THEN Result ELSE NULL END)
WHEN Formula = 'E-G' THEN
MAX(CASE WHEN Name = 'E' THEN Result ELSE NULL END)-MAX(CASE WHEN Name = 'G' THEN Result ELSE NULL END)
END AS Result
FROM cte_dat
WHERE Formula IS NOT NULL
GROUP BY Formula, Date
ORDER BY Date, Formula
Try flipping the division: 2/7 = 0.2857142857142857.
March 19, 2013 at 11:54 am
Lynn Pettis (3/19/2013)
Eugene Elutin (3/19/2013)
Most of OP output results do not match relevant formulas, eg:B/A 3/19/2013= 7/ 2 = 3.5 not 0.285714286!
Try flipping the division: 2/7 = 0.2857142857142857.
Yep, I know, but then it will be A/B not B/A
March 21, 2013 at 7:59 am
Thanks for the replay.
I am having almost 600 records, so is there any other way to get the result without case statement.
March 21, 2013 at 8:05 am
I'm not sure what the issue is.. it doesn't matter how many records you have when you only have 3 formulae for the case statement. It's not like the case statement is going to grow dependent on your data size.
March 21, 2013 at 8:07 am
shashi.bi245 (3/21/2013)
Thanks for the replay.I am having almost 600 records, so is there any other way to get the result without case statement.
What hardware are you using to run your SQL Server on? ZX Spectrum?
Even this one should process 600 records with no issue.
Seriously, why do you think that CASE WHEN will effect performance?
Or, I guess, you mean something else. Then please provide clear detailed requirements.
Please check the link at the bottom of my signature to find out what really helps to ask your question in a way to attract most relevant and prompt help.
March 21, 2013 at 10:10 pm
thanks for the clarification. I will try to use this code.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy