December 9, 2021 at 12:31 am
Hey everyone,
I have an issue joining 3 temp tables.
I have such tables
1) Snapshot
2) Forecast
3) 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 run all three queries separately everything works but when I am selecting them one of them depending how I join DATE 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.
WITH Snapshot AS
(
SELECT [IDUNIT] ,[LOCATION] ,[EVENT],[DATE],[ENDDATE] ,[NAME]
FROM Snapshot
),
FCSTPERFSTATIC AS
( SELECT [IDUNIT],[DATE],[LOCATION],SUM([FORECAST]) AS 'Forecast'
FROM Forecast
GROUP BY [IDUNIT], [DATE], [LOCATION]
),
ACTUALS AS
( SELECT [IDUNIT] ,[DATE],[LOCATION] ,SUM([Actuals]) AS 'Actuals'
FROM Actuals
GROUP BY [DMDUNIT], [DMDPostDate], [LOC]
)
SELECT U.[IDUNIT] AS 'Item', U.[LOCATION] AS 'Chain', U.[DATE] AS 'Start Date', U.[EVENT] , U.[EVENT_NAME], SUM(FORECAST) AS 'Forecast', SUM(ACTUALS) AS 'Actuals'
FROM Snapshot U
LEFT OUTER JOIN Forecast F
on U.[IDUNIT]=F.[IDUNIT] AND U.[LOCATION]=F.[LOCATION]
LEFT OUTER JOIN ACTUALS A
ON U.[IDUNIT]=a.[IDUNIT] AND U.[LOCATION]=a.[LOCATION]
AND a.[DATE]=u.[DATE]
GROUP BY U.[IDUNIT], U.[LOCATION], U.[DATE], U.[EVENT] , U.[NAME]
December 9, 2021 at 2:13 am
Without the CREATE TABLE and INSERT scripts, all we can do is guess. Could you post them?
December 9, 2021 at 2:23 am
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.
December 9, 2021 at 10:07 am
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;
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
December 9, 2021 at 2:15 pm
"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.
December 9, 2021 at 4:20 pm
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.
December 9, 2021 at 4:25 pm
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?
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
December 9, 2021 at 5:57 pm
;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) "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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply