Issue with joining 2 temporary tables
-
I have such a situation. For some reason, when I join two temporary tables one column gets completely different results.
Specifically the code
WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
**ISSUE with amount of Forecast**
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 99484.136
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT A.[LOC], SUM(A.Actuals) AS 'Actuals', SUM(F.Forecast) AS 'Forecast'
FROM Actuals A FULL OUTER JOIN Forecast F
on A.[DMDUNIT] = F.[DMDUNIT]
AND f.[STARTDATE] = a.[DMDPostDate]
and a.[LOC] = f.[LOC]
GROUP BY A.[LOC]
ORDER BY [LOC]
However, when I just run the calculation separately how much was the forecast for Albertsons, I get a different result. So something must be wrong, but I don't understand what
-- Albertsons (122880.591)
SELECT [LOC],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
GROUP BY [LOC]
ORDER BY [LOC] -
That's probably because you are summing over the joined dataset and the joined dataset contains more rows than the dataset purely from SCPOMGR.FCSTPERFSTATIC – ie, you are probably summing some values more than once.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
@Phil Parkin but how come joined dataset has more rows? I am doing full outer join... i was always under the impression when we do it we return exactly the numbers from both tables. Am I wrong?
-
@Phil I don't have such an issue with Actuals. Over there, I get the very same result as with a join.
-
@Phil
When I change GROUP BY from A.[LOC] to F.[LOC] I have the reverse effect. Now my Actuals are correct, but Forecast is not.
Something is wrong with the GROUP BY. Depending if I GROUP BY a.[LOC] I get a correct result for 'Actuals' but incorrect for 'Forecast', if I GROUP BY f.[LOC] I get a correct result for 'Forecast', but incorrect for 'Actuals'.
WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
-- Actuals 1718789 (Albertsons) grouped by A.[LOC]
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 99484.136 (Albertsons) grouped by A.[LOC]
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT F.[LOC], SUM(F.Forecast) AS 'Forecast', SUM(A.Actuals) AS 'Actuals'
FROM Forecast F FULL OUTER JOIN Actuals A
on F.[DMDUNIT] = A.[DMDUNIT]
AND F.[STARTDATE] = A.[DMDPostDate]
and F.[LOC] = A.[LOC]
GROUP BY F.[LOC]
ORDER BY F.[LOC] -
Before I attempt to explain, can you try running this version please?
WITH ACTUALS
AS (SELECT LOC
,Actuals = SUM(HistoryQuantity)
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY LOC)
,Forecast
AS (SELECT LOC
,Forecast = SUM(TOTFCST)
FROM SCPOMGR.FCSTPERFSTATIC
GROUP BY LOC)
SELECT A.LOC
,Actuals = SUM(A.Actuals)
,Forecast = SUM(F.Forecast)
FROM ACTUALS A
FULL OUTER JOIN Forecast F
ON A.LOC = F.LOC
GROUP BY A.LOC
ORDER BY A.LOC;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here is another version – obviously untested, but does not rely on a JOIN:
WITH Totals
AS (SELECT LOC
,Tab = 'Actuals'
,tot = SUM(HistoryQuantity)
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY LOC, Tab
UNION ALL
SELECT LOC
,Tab = 'Forecast'
,tot = SUM(TOTFCST)
FROM SCPOMGR.FCSTPERFSTATIC
GROUP BY LOC, Tab)
SELECT t.LOC
,Actuals = SUM( CASE t.Tab
WHEN 'Actuals' THEN
t.tot
ELSE
0
END
)
,Forecast = SUM( CASE t.Tab
WHEN 'Forecast' THEN
t.tot
ELSE
0
END
)
FROM Totals t
GROUP BY t.LOC;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
@Phil the first one works just great. But is it possible to make sure that it is joined on 3 fields:
F.[DMDUNIT] = A.[DMDUNIT]
AND F.[STARTDATE] = A.[DMDPostDate]
and F.[LOC] = A.[LOC]
With Albertsons it worked amazing (1st query) but I am a little bit afraid that some other things will be off because I need to be sure 3 criteria are met (Unit# which is DMDUNIT, Startdate# and LOC#).
-
@Phil if you can explain me the code I will appreciate. Also is there a chance to join/link on 3 fields:
F.[DMDUNIT] = A.[DMDUNIT]
AND F.[STARTDATE] = A.[DMDPostDate]
and F.[LOC] = A.[LOC]
-
As you are presenting the data grouped only by LOC, what function do you think joining on those other columns performs?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
@Phil it has to take into consideration the date and item if it was available in a specific location and on a specific day. In fact, I have just figured how to do it. I was just selecting COALESCE (a.[LOC], f.[LOC]) with the joins to get the value from either table when there is NULL in any of them. It worked.
I appreciate your help though. Thank you.
-
I got an email with just the initial q, I just quickly reviewed all the comments above, but my first thought was this:
;WITH ACTUALS AS
(
SELECT [LOC], [DMDUNIT], [DMDPostDate],
SUM(HistoryQuantity) AS 'Actuals'
FROM SCPOMGR.HISTWIDE_CHAIN
GROUP BY [LOC], [DMDUNIT], [DMDPostDate]
),
Forecast AS
(
SELECT [LOC], [DMDUNIT], [STARTDATE],
SUM(TOTFCST) AS 'Forecast'
FROM SCPOMGR.FCSTPERFSTATIC
-- Forecast Albertsons 99484.136
GROUP BY [LOC], [DMDUNIT], [STARTDATE]
)
SELECT COALESCE(A.[LOC], F.[LOC]) AS [LOC], --<<--
SUM(A.Actuals) AS 'Actuals', SUM(F.Forecast) AS 'Forecast'
FROM Actuals A FULL OUTER JOIN Forecast F
on A.[DMDUNIT] = F.[DMDUNIT]
AND f.[STARTDATE] = a.[DMDPostDate]
and a.[LOC] = f.[LOC]
GROUP BY COALESCE(A.[LOC], F.[LOC]) --<<--
ORDER BY [LOC]SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply