Make last Week to zero

  • Hi All,

    I have a requirement to set last week to zero for that month.

    In Jan 2022 we have 4 weeks & In Feb 2022 we have 4 weeks & In March 2022 we have 5 weeks.

    Drop table if exists #RefTable 
    Create Table #RefTable (yearwk int,yearmnt int)
    Insert into #RefTable
    Values
    (202201,202201),
    (202202,202201),
    (202203,202201),
    (202204,202201),
    (202205,202202),
    (202206,202202),
    (202207,202202),
    (202208,202202),
    (202209,202203),
    (202210,202203),
    (202211,202203),
    (202212,202203),
    (202213,202203)

    Select * from #RefTable
    Create Table  #Table (Data char(10), Week int, sales int)
    Insert into #Table
    SELECT 'A',202201,10
    UNION SELECT 'A',202202,20
    UNION SELECT 'A',202203,30
    UNION SELECT 'A',202204,40
    UNION SELECT 'B',202205,20
    UNION SELECT 'B',202206,40
    UNION SELECT 'B',202207,60
    UNION SELECT 'C',202209,20
    UNION SELECT 'C',202210,30


    Select * from #Table

    Need to add zero with next week if it is missed in the current month.

    Here for Month 2 we are missing data for week8, so i need to add zero for week8.

    lly  for Month 3 we are missing data from week11, so i need to add zero for week11

    Range

    Thanks!

     

  • You have Data items A, B and C all happening on different yearwks. Will that always be the case, or could you have overlapping data items, eg

    ('A',202201,10) and ('B',202201,20)

    ?

     

    • This reply was modified 1 week, 4 days ago by  Phil Parkin.
    • This reply was modified 1 week, 4 days ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • HI Phil,

    Thanks i have slightly changed it as it was not coming for Month 3, but where i am getting two more additional rows which is not the use case.

     

    DECLARE @MinWeek INT
    ,@MaxWeek INT;

    SELECT @MinWeek = MIN(t.yearWk)
    ,@MaxWeek = MAX(t.YearWk)
    FROM #RefTable t;

    SELECT t.Data
    ,rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week
    WHERE rt.yearwk
    BETWEEN @MinWeek AND @MaxWeek
    ORDER BY rt.yearwk
    ,t.Data;

     

  • Yes, my initial solution was not correct, which I realised after re-reading your post.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Data may or may not overlap.

  • LearnSQL wrote:

    Data may or may not overlap.

    So, just to confirm, if we added the following row to your sample data

    ('A', 202208,50)

    you would still wish to generate the

    ('B',202208,0)

    row?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • No , it should not be the case.

    But it is possible where in the current load it may not come, but where as it can again resume in May month.

    Range

  • Any thoughts or suggestions

  • @Phil, Do you have any suggestions ?

  • Sorry for the delayed response. This gets closer, I think:

    WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
    SELECT bd.Data
    ,bd.Week
    ,bd.Sales
    FROM BaseData bd
    WHERE bd.Data IS NOT NULL
    ORDER BY bd.Week
    ,bd.Data;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Here is a better version, which avoids the creation of a zero row when the preceding row was at the end of the month:

    WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    ,YearMnt = rt.yearmnt
    ,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
    SELECT bd.Data
    ,bd.Week
    ,bd.Sales
    FROM BaseData bd
    WHERE bd.Data IS NOT NULL
    AND bd.YearMnt = bd.PrevYearMnt
    ORDER BY bd.Week
    ,bd.Data;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks Phil.

    Here i have added a new value for C for week 19 and i am not suppose to get the Zero(0) for month 3 for week 11 as we have trailing value for C.

    Range

    Drop table if exists #RefTable 
    Create Table #RefTable (yearwk int,yearmnt int)
    Insert into #RefTable
    Values
    (202201,202201),
    (202202,202201),
    (202203,202201),
    (202204,202201),
    (202205,202202),
    (202206,202202),
    (202207,202202),
    (202208,202202),
    (202209,202203),
    (202210,202203),
    (202211,202203),
    (202212,202203),
    (202213,202203),
    (202214,202204),
    (202215,202204),
    (202216,202204),
    (202217,202204),
    (202218,202205),
    (202219,202205)

    --Select * from #RefTable
    Drop Table if exists #Table
    Create Table #Table (Data char(10), Week int, sales int)
    Insert into #Table
    SELECT 'A',202201,10
    UNION SELECT 'A',202202,20
    UNION SELECT 'A',202203,30
    UNION SELECT 'A',202204,40
    UNION SELECT 'B',202205,20
    UNION SELECT 'B',202206,40
    UNION SELECT 'B',202207,60
    UNION SELECT 'C',202209,20
    UNION SELECT 'C',202210,30
    UNION SELECT 'C',202217,30


    --Select * from #Table

    ; WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    ,YearMnt = rt.yearmnt
    ,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
    SELECT bd.Data
    ,bd.Week
    ,bd.Sales
    FROM BaseData bd
    WHERE bd.Data IS NOT NULL
    AND bd.YearMnt = bd.PrevYearMnt
    ORDER BY bd.Week
    ,bd.Data;

    Attachments:
    You must be logged in to view attached files.
  • Try this (as my query gets uglier and uglier!):

    WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    ,YearMnt = rt.yearmnt
    ,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
    SELECT bd.Data
    ,bd.Week
    ,bd.Sales
    FROM BaseData bd
    WHERE bd.Data IS NOT NULL
    AND bd.YearMnt = bd.PrevYearMnt
    AND NOT EXISTS
    (
    SELECT 1
    FROM #Table t
    WHERE t.Data = bd.Data
    AND t.Week > bd.Week
    AND bd.Sales = 0
    )
    ORDER BY bd.Week
    ,bd.Data;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks Phil.

    It was not working as expected as when have the below record then it is behaving differently. It is not showing up for Week 18.

    From :

    UNION SELECT 'C',202209,20
    UNION SELECT 'C',202210,30
    UNION SELECT 'C',202217,30

    To:
    UNION SELECT 'C',202209,20
    UNION SELECT 'C',202210,30
    UNION SELECT 'C',202218,30


  • The refinements continue!

    WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    ,YearMnt = rt.yearmnt
    ,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY rt.yearwk)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
    SELECT bd.Data
    ,bd.Week
    ,bd.Sales
    FROM BaseData bd
    WHERE bd.Data IS NOT NULL
    AND
    (
    bd.YearMnt = bd.PrevYearMnt
    OR bd.Sales <> 0
    )
    AND NOT EXISTS
    (
    SELECT 1
    FROM #Table t
    WHERE t.Data = bd.Data
    AND t.Week > bd.Week
    AND bd.Sales = 0
    )
    ORDER BY bd.Week
    ,bd.Data;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

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