Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL to divide one row by another row based on formula or rule. Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 8:52 AM
Points: 48, Visits: 73
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/2013 A 1 5
3/18/2013 B 2 16
3/18/2013 C 3 21
3/18/2013 D 4 11
3/18/2013 E 5 45
3/18/2013 F 6 22
3/18/2013 G 7 5
3/19/2013 A 1 2
3/19/2013 B 2 7
3/19/2013 C 3 15
3/19/2013 D 4 9
3/19/2013 E 5 19
3/19/2013 F 6 12
3/19/2013 G 7 3

Please suggest how to proceed to get below result. Formula Column is used to get Result(%) column.

Formula Date Result(%)
B/A 3/19/2013 0.285714286
D/C 3/19/2013 0.6
F/(E-G) 3/19/2013 0.75
B/A 3/18/2013 0.3125
D/C 3/19/2013 0.523809524
F/(E-G) 3/19/2013 0.55

Thanks in advance.
Post #1432770
Posted Tuesday, March 19, 2013 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 12,744, Visits: 31,078
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1432800
Posted Tuesday, March 19, 2013 11:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432811
Posted Tuesday, March 19, 2013 11:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 22,507, Visits: 30,226
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432814
Posted Tuesday, March 19, 2013 11:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432831
Posted Thursday, March 21, 2013 7:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 8:52 AM
Points: 48, Visits: 73
Thanks for the replay.

I am having almost 600 records, so is there any other way to get the result without case statement.
Post #1433799
Posted Thursday, March 21, 2013 8:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:55 AM
Points: 532, Visits: 984
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.
Post #1433801
Posted Thursday, March 21, 2013 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1433802
Posted Thursday, March 21, 2013 10:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 03, 2014 8:52 AM
Points: 48, Visits: 73
thanks for the clarification. I will try to use this code.
Post #1434127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse