February 16, 2022 at 10:52 pm
I have two CTEs. From one UDT_CKB_SNAPSHOT I am taking Event Names, from the 2nd CTE called FCSTPERFSTATIC I am taking Forecast and Actuals. I connect those two tables on Unit#, Location and Startdate. And everything is correct. However, I need to add 2 more columns namely 'Forecast for the 2nd week' and 'Actuals for the 2nd week'. Right now I am taking Forecast and Actuals only for 1st week. I basically join Startdate of UDT_CKB_SNAPSHOT and Startdate of FCSTPERFSTATIC. What I need to do now is to basically create maybe 2nd CTE where I can take Startdate of UDT_CKB_SNAPSHOT and add + 7 days to it and join to FCSTPERFSTATIC.
I need for 'Forecast 2nd week' and 'Actuals 2nd week' to look at STARTDATE of UDT_CKB_SNAPSHOT and take Actuals and Forecast for the next week.
I don't know if that is even doable. Does someone know how it can be done?
Full code
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 @LastWeekDATE AND @CurrentDate
GROUP BY [UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE] ,[STARTDATE] ,[ENDDATE] ,[EVENT_NAME] ),
FCSTPERFSTATIC AS
( SELECT [DMDUNIT] ,[STARTDATE] ,[LOC] ,SUM([TOTFCST]) AS 'Forecast' ,SUM([TOTHIST]) AS 'Actuals'
FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]
WHERE [STARTDATE] BETWEEN @LastWeekDATE AND @CurrentDate
GROUP BY [DMDUNIT], [STARTDATE], [LOC] )
SELECT U.[UPC] AS 'Item', U.[MASTERCHAINNAME] AS 'Chain', U.[EVENT_TYPE] , U.[STARTDATE] AS 'Start Date', U.[EVENT_NAME], SUM(F.Forecast) AS 'Forecast 1 week',
SUM(f.Actuals) AS 'Actuals 1 week'
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]
--WHERE u.UPC='9781620917718' AND u.MASTERCHAINNAME='WALMART'
GROUP BY U.[UPC], U.[MASTERCHAINNAME], U.[EVENT_TYPE] , U.[STARTDATE], U.[EVENT_NAME]
February 17, 2022 at 9:42 pm
Pretty sure it depends on your data. I recommend posting some sample data, some DDL, and your expected results so we have something to go on and test.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 18, 2022 at 12:34 pm
The first CTE (named 'UDT_CKB_SNAPSHOT') contains GROUP BY but no HAVING clause and no aggregate function(s) in the select list. It's equivalent to SELECT DISTINCT which is much more readable. Suppose 2 dummy duplicate rows named 'data_cte' then the first CTE could look like this
declare
@LastWeekDATE date='20220222',
@CurrentDate date='20220922';
with
data_cte([UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE] ,[STARTDATE] ,[ENDDATE] ,[EVENT_NAME]) as (
select 'xyz', 'jjj', 'Promotion', cast('20220222' as date), cast('20220224' as date), 'xxx'
union all
select 'xyz', 'jjj', 'Promotion', '20220222', '20220224', 'xxx'),
UDT_CKB_SNAPSHOT AS (
SELECT distinct [UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE] ,[STARTDATE] ,[ENDDATE] ,[EVENT_NAME]
FROM data_cte
WHERE [EVENT_TYPE]='Promotion' AND
[STARTDATE] BETWEEN @LastWeekDATE
AND @CurrentDate)
select *
from UDT_CKB_SNAPSHOT;
The output is 1 deduplicated row
UPCMASTERCHAINNAMEEVENT_TYPESTARTDATEENDDATE EVENT_NAME
xyzjjj Promotion2022-02-222022-02-24xxx
You need another row but with STARTDATE +7 days. To make 2 rows out of 1 you could CROSS JOIN, aka "Cartesian product" or "row multiply". Suppose a virtual table 'v' with 1 column 'n' and 2 rows { 0, 1 }. By CROSS JOIN'ing it creates the 2 rows you need. It doesn't matter what the row values of the virutial table 'v' are at all. They could both be NULL and the row expansion would still occur. But it's very, very handy for calculations to use an integer sequence. What if you want to project 2 weeks, 3 weeks, etc. then you could use a tally function. An interesting explanation can be found in this great article by Jeff Moden.
declare
@LastWeekDATE date='20220222',
@CurrentDate date='20220922';
with
data_cte([UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE] ,[STARTDATE] ,[ENDDATE] ,[EVENT_NAME]) as (
select 'xyz', 'jjj', 'Promotion', cast('20220222' as date), cast('20220224' as date), 'xxx'
union all
select 'xyz', 'jjj', 'Promotion', '20220222', '20220224', 'xxx'),
UDT_CKB_SNAPSHOT AS (
SELECT distinct [UPC] ,[MASTERCHAINNAME] ,[EVENT_TYPE] ,[STARTDATE] ,[ENDDATE] ,[EVENT_NAME]
FROM data_cte
WHERE [EVENT_TYPE]='Promotion' AND
[STARTDATE] BETWEEN @LastWeekDATE
AND @CurrentDate)
select *, dateadd(day, v.n*7, STARTDATE) calc_STARTDATE
from UDT_CKB_SNAPSHOT
cross join (values (0), (1)) v(n);
UPCMASTERCHAINNAMEEVENT_TYPESTARTDATEENDDATEEVENT_NAMEncalc_STARTDATE
xyzjjjPromotion2022-02-222022-02-24xxx02022-02-22
xyzjjjPromotion2022-02-222022-02-24xxx12022-03-01
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply