Issue while joining two tables with no unique columns
-
I have two tables with no common columns:
Graph_Range
Start_Range End_Range
0.10 0.20
0.20 0.30
0.30 0.40
Graph_data
Asset_Value factor Case
500 0.12 1
270 0.13 2
300 0.27 3
240 0.23 4
200 0.13 5
100 0.12 6
I need results as
Lower Limit Upper Limit Sum_Asset
0.10 0.20 1,070 i.e. 500 + 270 + 200 + 100
0.20 0.30 540 i.e. 300 + 240
0.30 0.40 0
i.e. the Sum_Asset should give sum of all asset values for which factor falls between Start and end range in the other 2 columns.
I am not sure how can I get results in such data set as UNION, CROSS JOIN,INNER JOIN are not working. Either I am getting repaeted entires or error messages. Please help.
I have used this query
SELECT A.Start_Range, A.End_Range, SUM(B.asset) AS Sum_Asset
FROM dbo.Graph_Range AS A , dbo.Graph_Data AS B
GROUP BY A.Start_Range, A.End_Range , b.Factor
HAVING (B.Factor < A.End_Range) AND (B.Factor > A.Start_Range)
-
DECLARE @Graph_Range TABLE(Start_Range FLOAT, End_Range FLOAT);
INSERT INTO @Graph_Range(Start_Range, End_Range)
VALUES (0.10, 0.20), (0.20, 0.30), (0.30, 0.40);
DECLARE @Graph_data TABLE(Asset_Value INT, factor FLOAT, [Case] INT);
INSERT INTO @Graph_data(Asset_Value, factor, [Case])
VALUES (500, 0.12, 1),(270, 0.13, 2),(300, 0.27, 3),(240, 0.23, 4),(200, 0.13, 5), (100, 0.12, 6);
SELECT r.Start_Range AS [Lower Limit],
r.End_Range AS [Upper Limit],
COALESCE(SUM(Asset_Value),0) AS Sum_Asset
FROM @Graph_Range r
LEFT OUTER JOIN @Graph_data d ON d.factor BETWEEN r.Start_Range AND r.End_Range
GROUP BY r.Start_Range, r.End_Range
ORDER BY r.Start_Range, r.End_Range;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
DROP TABLE #Graph_Range
CREATE TABLE #Graph_Range (Start_Range DECIMAL (5,2), End_Range DECIMAL (5,2))
INSERT INTO #Graph_Range (Start_Range, End_Range)
SELECT 0.10, 0.20 UNION ALL
SELECT 0.20, 0.30 UNION ALL
SELECT 0.30, 0.40
DROP TABLE #Graph_data
CREATE TABLE #Graph_data (Asset_Value INT, factor DECIMAL (5,2), [Case] INT)
INSERT INTO #Graph_data (Asset_Value, factor, [Case])
SELECT 500, 0.12, 1 UNION ALL
SELECT 270, 0.13, 2 UNION ALL
SELECT 300, 0.27, 3 UNION ALL
SELECT 240, 0.23, 4 UNION ALL
SELECT 200, 0.13, 5 UNION ALL
SELECT 100, 0.12, 6
SELECT
[Lower Limit] = r.Start_Range,
[Upper Limit] = r.End_Range,
[Sum_Asset] = ISNULL(SUM(d.Asset_Value),0)
FROM #Graph_Range r
LEFT JOIN #Graph_data d ON d.factor BETWEEN r.Start_Range AND r.End_Range
GROUP BY r.Start_Range, r.End_Range
ORDER BY r.Start_Range, r.End_Range
Quick work Mark 😉
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
... and identical solutions too!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Mark-101232 (6/6/2013)
... and identical solutions too!Must be correct then!
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (6/6/2013)
Mark-101232 (6/6/2013)
... and identical solutions too!Must be correct then!
I'm not sure they're correct. If a row has factor 0.20 then it will be in both ranges.
The OP must either change the ranges end or start values or change the between for something slightly different, acording to the business rules.
LEFT OUTER JOIN @Graph_data d ON d.factor >= r.Start_Range AND d.factor < r.End_Range
--OR
LEFT OUTER JOIN @Graph_data d ON d.factor > r.Start_Range AND d.factor <= r.End_Range
-
A long time ago in a galaxy far, far away a bunch of folks were arguing about an exotic and rarely-mentioned internal feature of SQL Server for Pangaians 238857. The majority of the folks said "It works this way!" and for a short while everyone was happy, until Darth White corrected them by explaining in great detail how and why the rarely-mentioned internal feature worked - and that how it worked was not a matter of opinion but a matter of fact.
The business logic is flawed - good spot, Luis!
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thanks. Issue is resolved now 🙂
-
nidhi.finance1 (6/7/2013)
Thanks. Issue is resolved now 🙂Cool. Please post the solution so we can learn from it. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply