Post reply

Looking at startdate of one table and calculate the numbers from a different tab

  • February 15, 2022 at 7:44 pm

    #3984027

    I have such a situation. I have a table UDT_CKB_SNAPSHOT where we have different events (promotions). I need to take these event names, start dates of those events.

    I have a 2nd table called FCSTPERFSTATIC where we have Actuals and Forecast.

    What I need to do is basically is to look at STARTDATE of UDT_CKB_SNAPSHOT and calculate the forecast and actuals (from FCSTPERFSTATIC) for the next 3 weeks.

    The way how it is set up right now is that I connected STARTDATE of UDT_CKB_SNAPSHOT and STARTDATE of FCSTPERFSTATIC.

    But what I need is basically take STARTDATE of UDT_CKB_SNAPSHOT and if the event startdate is on 02/06, I need to see Forecast and Actuals from 02/06, 02/13 and 02/20 from FCSTPERFSTATIC.

    Right now it returns only one week of data.

     

    DECLARE @CurrentDateTime DATETIME = GETDATE();
    DECLARE @CurrentDate DATE = @CurrentDateTime;

    DECLARE @LastWeekDt DATE = DATEADD(WW, -3, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
    DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

    DECLARE @Last1Ms DATE = DATEADD(WW, -5, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
    DECLARE @Last1Months VARCHAR(8) = convert(varchar(8),cast (@Last1Ms as date),112);

    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',
    SUM(f.Actuals) AS 'Actuals'


    FROM UDT_CKB_SNAPSHOT U  
    LEFT OUTER JOIN FCSTPERFSTATIC F
    on U.[UPC]=F.[DMDUNIT] AND U.[MASTERCHAINNAME]=F.[LOC]   and u.[STARTDATE]=f.[STARTDATE]
    and f.[STARTDATE] BETWEEN @Last1Months AND @CurrentDate  

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

     

    Issue1

  • February 15, 2022 at 8:52 pm

    #3984034

     

    Hello

    Can the admin just delete this post?

    I figured we just need to put "> ="  and f.[STARTDATE] > = u.[STARTDATE]

Viewing 2 posts - 1 through 1 (of 1 total)

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