SQL to divide one row by another row based on formula or rule.

Question

Post reply

SQL to divide one row by another row based on formula or rule.

shashi.bi245

SSC-Addicted

Points: 401
More actions
March 19, 2013 at 10:35 am

#394423

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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 😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

shashi.bi245 SSC-Addicted Points: 401 More actions · Answer 5

Thanks for the replay.

I am having almost 600 records, so is there any other way to get the result without case statement.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

shashi.bi245 SSC-Addicted Points: 401 More actions · Answer 8

thanks for the clarification. I will try to use this code.