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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
@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;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
@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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
@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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply