Missing weekend data to Zero

  • Hi All,

    I have a requirement that if there is no weekend data then i should mark the next row with zero. Here for 123 , A there is no sales received for week 4 and so we need to make it to zero. and the same for 123,B there is no sales for week 3 so we need to make it to zero.

    And say if my current month is August and i am in middle of that month then I should not consider that. this is applicable only for the current month. August has 31,32,33,34 so EOW is 202234


    I have a reference time data table attached here which will hold weeks start and week end data something like

    Yearweek ,eow





    Drop table if exists #temp
    Create table #temp(IT INT,CG CHAR(10),Sales Float,Week INT,EOW INT)
    Insert into #temp
    Select 123,'A',10,202201,202204
    UNION ALL Select 123,'A',10,202202,202204
    UNION ALL Select 123,'A',9,202203,202204
    UNION ALL Select 123,'A',8,202204,202204
    UNION ALL Select 1234,'A',10,202201,202204
    UNION ALL Select 1234,'A',10,202202,202204
    UNION ALL Select 1234,'A',9,202203,202204
    UNION ALL Select 123,'B',10,202201,202204
    UNION ALL Select 123,'B',10,202202,202204
    UNION ALL Select 123,'B',10,202231,202234
    UNION ALL Select 123,'B',10,202231,202234
    UNION ALL Select 123,'B',9,202232,202234
    UNION ALL Select 1234,'A',10,202231,202234
    UNION ALL Select 1234,'A',10,202232,202234
    UNION ALL Select 1234,'A',9,202233,202234



    • This topic was modified 2 months ago by  LearnSQL.
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • with rn_cte as (
    select *, eow-[Week] wk_diff,
    row_number() over (partition by it, cg, eow order by (eow-[Week])) rn
    from #temp)
    select *, calc.exp_row, [week]+xr.n-1 exp_week
    from rn_cte rc
    cross apply (values (iif(rc.rn=1 and rc.wk_diff>1, 1, 0))) calc(exp_row)
    cross apply (select top(calc.exp_row+1) v.mo_num
    from (values (1),(2)) v(mo_num)) xr(n)
    order by it, cg, eow, [week]+xr.n-1;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I see this as two parts.

    First part is to identify all IT CG combinations that don't have a Week = EOW entry where the EOW is less than the current month EOW and create a zero sales record for them. I'd probably do some combination of cte queries and/or an except statement.

    Second part is to add these new records to the #temp data. I'd probably use a union.

    For the IT=1234, CG=A:




    There is no record with Week = 202204, so we create and add a record that looks like 1234,'A',0,202204,202204

  • I've not done a deep dive on it but isn't this the same request as the following link with different test data?



    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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