April 11, 2011 at 9:10 pm
I am going to try and make the example easier to follow, in hopes I can explain myself properly.
I have two tables named: LiveRun and Run
The LiveRun table has three columns: nRunId, nTotal, nWaste
The Run table has 4 columns: nRunId, nTotal, nWaste, nProductRunId
The Run table stores a history of all the runs that have run in the past. If you were to group the Run table by nProductRunId, you can get the nTotal and nWaste for the "Product Run". The problem is, that it may leave out an active run in the LiveRun table that needs to be included in that nTotal and nWaste.
So the question is, how could I write the join that will sum these two tables up to give a LIVE look at the current total (past runs and present). If I join the two tables, this is how I would do it:
FROM LiveRun (NOLOCK) LEFT OUTER JOIN Run ON LiveRun.nRunId = Run.nRunId
but no matter how I join this (left outer join or right outer join) I get either: 1 record (from the LiveRun table) or all the records in the Run table as NULLS but one record that ties to the LiveRun. The problem is there may be three other records in the Run table that I want to sum up nTotal and nWaste.
Hope this makes sense....any suggestions?
Cheers!
April 11, 2011 at 11:49 pm
nailers (4/11/2011)
I am going to try and make the example easier to follow, in hopes I can explain myself properly.I have two tables named: LiveRun and Run
The LiveRun table has three columns: nRunId, nTotal, nWaste
The Run table has 4 columns: nRunId, nTotal, nWaste, nProductRunId
The Run table stores a history of all the runs that have run in the past. If you were to group the Run table by nProductRunId, you can get the nTotal and nWaste for the "Product Run". The problem is, that it may leave out an active run in the LiveRun table that needs to be included in that nTotal and nWaste.
...
Is nRunID the pk of both tables?
How do you identify - in words - a row in LiveRun which you want to be included? I reckon it should be like this: "Get rows from LiveRun where the pk (nRunID) isn't in Run"
It would help if you could post the CREATE TABLE statements (DDL) for both tables.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 12, 2011 at 3:53 am
It is far from clear what you want.
I suspect you may need to use UNION ALL instead of JOIN.
Maybe something like:
;WITH Combined
AS
(
SELECT nRunId, nTotal, nWaste, -1 AS nProductRunId
FROM LiveRun
UNION ALL
SELECT nRunId, nTotal, nWaste, nProductRunId
FROM Run
)
SELECT nProductRunId, SUM(nTotal) AS nTotal, SUM(nWaste) AS nWaste
FROM Combined
GROUP BY nProductRunId
April 12, 2011 at 9:43 am
Since you don't have a productID for the live runs, which groupings do you want them totalised into??
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply