One table two rows

Question

One table two rows

djj

SSCoach

Points: 18831
More actions
February 4, 2015 at 7:52 am

#314380

What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.
The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.
CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');
SELECT CAST(NV1.MatrixVal AS DECIMAL(10,2)) / CAST(NV2.MatrixVal AS DECIMAL(10,2))
FROM #NumVals NV1, #NumVals NV2
WHERE NV1.MatrixCat = 'OB Dial Attempts'
AND NV2.MatrixCat = '# Ready To Work Inventory';
DROP TABLE #NumVals;
So what am I missing?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

djj (2/4/2015)
What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.
The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.
CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');
SELECT CAST(NV1.MatrixVal AS DECIMAL(10,2)) / CAST(NV2.MatrixVal AS DECIMAL(10,2))
FROM #NumVals NV1, #NumVals NV2
WHERE NV1.MatrixCat = 'OB Dial Attempts'
AND NV2.MatrixCat = '# Ready To Work Inventory';
DROP TABLE #NumVals;
So what am I missing?

What is this supposed to do? You said it works but it is flawed. We don't have any idea what that means. Also, if you are going to use a cross join (which I wouldn't recommend unless your real table has only 2 rows) you should use an explicit cross join.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

djj SSCoach Points: 18831 More actions · Answer 2

Sean Lange (2/4/2015)
What is this supposed to do? You said it works but it is flawed. We don't have any idea what that means. Also, if you are going to use a cross join (which I wouldn't recommend unless your real table has only 2 rows) you should use an explicit cross join.

1) Need a query to take data from two separate rows of one table and divide them

-- row one 'OB Dial Attempts' divided by row two '# Ready To Work Inventory'

2) Notice I said 'I think there is a flaw in my logic'. The reason for my questioning the code is the join of the table.

So I guess my question is what is the proper way to join one table on itself to get data from two different rows then divide the data?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

djj (2/4/2015)
So I guess my question is what is the proper way to join one table on itself to get data from two different rows then divide the data?

That depends on the table and the situation. You need something to join the two tables on. In the sample you posted there is nothing that relates those two rows.

Also, why are you storing numeric values in a varchar column?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

djj SSCoach Points: 18831 More actions · Answer 4

Sean Lange (2/4/2015)
djj (2/4/2015)
So I guess my question is what is the proper way to join one table on itself to get data from two different rows then divide the data?
That depends on the table and the situation. You need something to join the two tables on. In the sample you posted there is nothing that relates those two rows.
Also, why are you storing numeric values in a varchar column?

The table I am getting the data from is a temporary table I am building for a report and has character data in the MatrixVal column.

I think I will change the way I am doing it and have a one row table with multiple columns, then at the end of the procedure I pivot.

I was so focused on getting the requested report I did not think...

Thanks for trying to help, and you did help in getting my brain to function. 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

djj (2/4/2015)
Sean Lange (2/4/2015)
djj (2/4/2015)
So I guess my question is what is the proper way to join one table on itself to get data from two different rows then divide the data?
That depends on the table and the situation. You need something to join the two tables on. In the sample you posted there is nothing that relates those two rows.
Also, why are you storing numeric values in a varchar column?
The table I am getting the data from is a temporary table I am building for a report and has character data in the MatrixVal column.
I think I will change the way I am doing it and have a one row table with multiple columns, then at the end of the procedure I pivot.
I was so focused on getting the requested report I did not think...
Thanks for trying to help, and you did help in getting my brain to function. 🙂

Glad I could be the direction changer you needed. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Nevyn SSChampion Points: 14210 More actions · Answer 6

Once you do all that, you may also want to check for divide by zero in your finished query ...

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

Nasty CROSS JOIN that.

Perhaps one of these instead?

CREATE TABLE #NumVals (ID INT, MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));

INSERT INTO #NumVals (ID, MatrixCat, MatrixVal) VALUES

(1, '# Ready To Work Inventory','606'),

(1, 'OB Dial Attempts','255'),

(2, '# Ready To Work Inventory','555'),

(2, 'OB Dial Attempts','333')

;

SELECT CAST(NV1.MatrixVal AS DECIMAL(10,2)) / CAST(NV2.MatrixVal AS DECIMAL(10,2))

FROM #NumVals NV1, #NumVals NV2

WHERE NV1.MatrixCat = 'OB Dial Attempts'

AND NV2.MatrixCat = '# Ready To Work Inventory';

-- Cross tab to identify numerator and denominator

SELECT Denom = MAX(CASE MatrixCat WHEN 'OB Dial Attempts' THEN MatrixVal END)

,Numer = MAX(CASE MatrixCat WHEN '# Ready To Work Inventory' THEN MatrixVal END)

FROM #NumVals

GROUP BY ID;

-- Or use logarithms

SELECT Result = EXP(SUM(LOG(MatrixVal) * CASE MatrixCat WHEN '# Ready To Work Inventory' THEN -1 ELSE 1 END))

FROM #NumVals

GROUP BY ID;

Go

DROP TABLE #NumVals;

Edit: In either case you still need to handle division by zero of course, because:

SELECT LOG(0);

Gives you:

Msg 3623, Level 16, State 1, Line 1

An invalid floating point operation occurred.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

djj SSCoach Points: 18831 More actions · Answer 8

Thank you everyone for your time. Your ideas are helpful.

I decided to go with variables as I was calculating all the data to put into my table that I wanted to manipulate. Got focused on creating a table that I did not need.

Thanks again,

djj