Issue when joining 3 temp tables

  • Hey everyone,

    I have an issue joining 3 temp tables.

    I have such tables

    1) UDT_CKB_SNAPSHOT - list of different items and events (items on sale) some promotions etc

    2) FCSTPERFSTATIC - Forecast

    3) HISTWIDE_CHAIN - Actuals

    I am going to write the whole code but the thing that doesn't work is a join when selecting all three temp tables. Everything else works just fine (type just in case if someone wants to see the whole picture).

    So the issue is the line in bold italics.

    I need to join all three tables on dates to make sure I select the same inventory during the same time period. Whenever I join those three tables I either get Forecast or Actuals right, but never both.

    When i type this I get Forecast correct, but Actuals incorrect  a.[DMDPostDate]=u.[STARTDATE] and f.[STARTDATE]=a.[DMDPostDate] (Forecast correct 6998.649, Actuals are not correct 826)

    -- AND u.[STARTDATE]=f.[STARTDATE] and f.[STARTDATE]=u.[STARTDATE] (Actuals are correct 10369, Forecast not correct 8322.315)

    -- and a.[DMDPostDate]=f.[STARTDATE] (Forecast correct 6998.649, Actuals not correct)

    -- AND u.[STARTDATE]=a.[DMDPostDate] (Forecast correct 6998.649, Actuals not correct)

    -- AND u.[STARTDATE]=f.[STARTDATE] (Actuals are correct 10369, Forecast not correct)

    -- and u.[STARTDATE]=f.[STARTDATE] and u.[STARTDATE] = a.[DMDPostDate] (Forecast correct 6998.649, Actuals not correct)

    When I run all three queries separately everything works but when I am selecting them one of them depending how I join STARTDATE goes wrong. I've spent almost the whole day trying to troubleshoot, but i still don't understand how I can make it work.

    I will appreciate any advice.

    Issue1

     

    WITH UDT_CKB_SNAPSHOT AS
    (
    SELECT [UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE],[STARTDATE],[ENDDATE] ,[EVENT_NAME]
    FROM [BYIntegration].[SCPOMGR].[UDT_CKB_SNAPSHOT]
    WHERE [EVENT_TYPE]='Promotion' AND
    [STARTDATE] BETWEEN '2021-09-26' AND '2021-12-08'

    GROUP BY [UPC] ,[MASTERCHAINNAME],[EVENT_TYPE],[STARTDATE],[ENDDATE] ,[EVENT_NAME]
    ),

    FCSTPERFSTATIC AS
    ( SELECT [DMDUNIT],[STARTDATE],[LOC],SUM([TOTFCST]) AS 'Forecast'
    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]
    WHERE [STARTDATE] BETWEEN '2021-09-26' AND '2021-12-08'
    GROUP BY [DMDUNIT], [STARTDATE], [LOC]
    ),

    HISTWIDE_CHAIN AS
    ( SELECT [DMDUNIT] ,[DMDPostDate],[LOC] ,SUM([HistoryQuantity]) AS 'HistoryQuantity'
    FROM [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN]
    WHERE [DMDPostDate] BETWEEN '2021-09-26' AND '2021-12-08'

    GROUP BY [DMDUNIT], [DMDPostDate], [LOC]
    )

    SELECT U.[UPC] AS 'Item', U.[MASTERCHAINNAME] AS 'Chain', U.[STARTDATE] AS 'Start Date', U.[EVENT_TYPE] , U.[EVENT_NAME], SUM(F.Forecast) AS 'Forecast', SUM(A.HistoryQuantity) AS 'Actuals'

    FROM UDT_CKB_SNAPSHOT U
    LEFT OUTER JOIN FCSTPERFSTATIC F
    on U.[UPC]=F.[DMDUNIT] AND U.[MASTERCHAINNAME]=F.[LOC]

    LEFT OUTER JOIN HISTWIDE_CHAIN A
    ON U.[UPC]=a.[DMDUNIT] AND U.[MASTERCHAINNAME]=a.[LOC]
    AND a.[DMDPostDate]=u.[STARTDATE] and f.[STARTDATE]=a.[DMDPostDate]

    GROUP BY U.[UPC], U.[MASTERCHAINNAME], U.[STARTDATE], U.[EVENT_TYPE] , U.[EVENT_NAME]

    • This topic was modified 1 month, 1 week ago by  JeremyU.
  • Without the CREATE TABLE and INSERT scripts, all we can do is guess. Could you post them?

  • @pietlinden

    I am not sure I understand. I don't have any CREATE TABLE and INSERT scripts. The whole code that I had I wrote over here. I don't have anything else.

  • We cannot give you a definitive answer, because we can't see your data or table structures. But the fact that each of the queries works individually, but not when joined, demonstrates that it's the joining which is causing the issues. It's likely that the underlying row counts and groupings are going haywire as part of the join, meaning in particular that the aggregates (Forecast and Actuals) are wrong.

    Try changing the final query by removing the GROUPing and take a look at the dataset which is being aggregated. It should provide a clue as to what is going wrong.

    SELECT Item = U.UPC
    ,Chain = U.MASTERCHAINNAME
    ,[Start Date] = U.STARTDATE
    ,U.EVENT_TYPE
    ,U.EVENT_NAME
    ,Forecast = F.Forecast
    ,Actuals = A.HistoryQuantity
    FROM UDT_CKB_SNAPSHOT U
    LEFT OUTER JOIN FCSTPERFSTATIC F
    ON U.UPC = F.DMDUNIT
    AND U.MASTERCHAINNAME = F.LOC
    LEFT OUTER JOIN HISTWIDE_CHAIN A
    ON U.UPC = A.DMDUNIT
    AND U.MASTERCHAINNAME = A.LOC
    AND A.DMDPostDate = U.STARTDATE
    AND F.STARTDATE = A.DMDPostDate;

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • "I have such tables

    1) UDT_CKB_SNAPSHOT - list of different items and events (items on sale) some promotions etc

    2) FCSTPERFSTATIC - Forecast

    3) HISTWIDE_CHAIN - Actuals"

    You are being asked to provide scripts that define the tables that are being used, and sample data to populate those tables with data that someone can use to try to help you. You can right click on tables in SSMS to Script table as Create. The insert scripts can be manually created or cobbled together from data you get from a select.

    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

    Without that, it's hard for anybody to simulate the problem & prototype/test a solution.

  • Phil Parkin when I remove the grouping I have the very same numbers, just not grouped, but when I copy and paste them in Excel and calculate the numbers they are identical to when I sum them in SQL. Unfortunately, it doesn't help.

  • JeremyU wrote:

    but when I copy and paste them in Excel and calculate the numbers they are identical to when I sum them in SQL.

    In Excel, you see the incorrect totals after doing this?

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • ;WITH UDT_CKB_SNAPSHOT AS
    (
    SELECT [UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE],[STARTDATE],[ENDDATE] ,[EVENT_NAME]
    FROM [BYIntegration].[SCPOMGR].[UDT_CKB_SNAPSHOT]
    WHERE [EVENT_TYPE]='Promotion' AND
    [STARTDATE] BETWEEN '2021-09-26' AND '2021-12-08'

    GROUP BY [UPC] ,[MASTERCHAINNAME],[EVENT_TYPE],[STARTDATE],[ENDDATE] ,[EVENT_NAME]
    ),

    FCSTPERFSTATIC AS
    ( SELECT [DMDUNIT],[STARTDATE],[LOC],SUM([TOTFCST]) AS 'Forecast'
    FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]
    WHERE [STARTDATE] BETWEEN '2021-09-26' AND '2021-12-08'
    GROUP BY [DMDUNIT], [STARTDATE], [LOC]
    ),

    HISTWIDE_CHAIN AS
    ( SELECT [DMDUNIT] ,[DMDPostDate],[LOC] ,SUM([HistoryQuantity]) AS 'HistoryQuantity'
    FROM [BYIntegration].[SCPOMGR].[HISTWIDE_CHAIN]
    WHERE [DMDPostDate] BETWEEN '2021-09-26' AND '2021-12-08'
    GROUP BY [DMDUNIT], [DMDPostDate], [LOC]
    )

    SELECT U.[UPC] AS 'Item', U.[MASTERCHAINNAME] AS 'Chain', U.[STARTDATE] AS 'Start Date', U.[EVENT_TYPE] , U.[EVENT_NAME],
    SUM(F.Forecast) AS 'Forecast', SUM(A.HistoryQuantity) AS 'Actuals'

    FROM UDT_CKB_SNAPSHOT U
    LEFT OUTER JOIN FCSTPERFSTATIC F
    ON U.[UPC]=F.[DMDUNIT] AND U.[MASTERCHAINNAME]=F.[LOC] AND
    F.[STARTDATE] = U.[STARTDATE] --<<--

    LEFT OUTER JOIN HISTWIDE_CHAIN A
    ON U.[UPC]=a.[DMDUNIT] AND U.[MASTERCHAINNAME]=a.[LOC]
    AND a.[DMDPostDate]=u.[STARTDATE] AND
    (f.[STARTDATE IS NULL OR f.[STARTDATE]=a.[DMDPostDate]) --<<--

    GROUP BY U.[UPC], U.[MASTERCHAINNAME], U.[STARTDATE], U.[EVENT_TYPE] , U.[EVENT_NAME]

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 8 posts - 1 through 7 (of 7 total)

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