Issue with joining 2 temporary tables

Question

Issue with joining 2 temporary tables

SSChasing Mays

Points: 640

November 21, 2021 at 4:52 pm

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]

Issue15

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

Issue16

-- Albertsons  (122880.591)
     SELECT [LOC],
   SUM(TOTFCST) AS 'Forecast'
   FROM SCPOMGR.FCSTPERFSTATIC
   GROUP BY [LOC]
   ORDER BY [LOC]

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

JeremyU SSChasing Mays Points: 640 More actions · Answer 2

@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?

JeremyU SSChasing Mays Points: 640 More actions · Answer 3

@Phil I don't have such an issue with Actuals. Over there, I get the very same result as with a join.

JeremyU SSChasing Mays Points: 640 More actions · Answer 4

@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]

Issue17

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

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;

This reply was modified 3 years, 1 month ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

JeremyU SSChasing Mays Points: 640 More actions · Answer 7

@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#).

Issue18

JeremyU SSChasing Mays Points: 640 More actions · Answer 8

@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]

Phil Parkin SSC Guru Points: 246986 More actions · Answer 9

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

JeremyU SSChasing Mays Points: 640 More actions · Answer 10

@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.

ScottPletcher SSC Guru Points: 101122 More actions · Answer 11

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".