Make last Week to zero

  • Thanks Phil, I believe this one is coming close, But what will happen if i have to two more columns DepartmentID and EmployeeID to my #table data.

    FROM Create Table #Table (Data char(10), Week int, sales int)

    TO Create Table #Table (EmployeeID int, DepartmentID int, Data char(10), Week int, sales int)

    What changes need to be done here in the below code.

    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;

     

  • That looks trivial and is something which I suggest you attempt yourself.

    Just add the columns to the BaseData CTE and then to the SELECT ... FROM BaseData.

    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 what i see when i add the new columns to select statement  which is not having value for 1234,A with Zero.

    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 (Empid int, Data char(10), Week int, sales int)
    Insert into #Table
    SELECT 1234,'A',202201,10
    UNION SELECT 123, 'A',202202,20
    UNION SELECT 123,'A',202203,30
    UNION SELECT 123,'A',202204,40
    UNION SELECT 1234,'B',202201,10
    UNION SELECT 123, 'B',202202,20
    UNION SELECT 123,'B',202203,30
    UNION SELECT 123,'B',202204,40
    UNION SELECT 124,'B',202205,20
    UNION SELECT 124,'B',202206,40
    UNION SELECT 1234,'B',202207,60
    UNION SELECT 111,'C',202209,20
    UNION SELECT 111,'C',202210,30
    UNION SELECT 112,'C',202211,30
    UNION SELECT 111,'C',202212,30
    UNION SELECT 123,'C',202213,30
    UNION SELECT 111,'C',202217,30

    --Select Top 2 * from #RefTable
    --Select Top 2 * from #Table


    ;WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY rt.yearwk))
    ,Empid = ISNULL(t.Empid, LAG(t.Empid, 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.Empid,
    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 bd.Empid=t.Empid
    AND t.Week > bd.Week
    AND bd.Sales = 0
    )
    And bd.Empid=1234
    ORDER BY bd.Week
    ,bd.Data;

     

  • Are you saying that the line in blue is incorrect?

    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.

  • I am not getting the blue lines.. that is what i was expecting vs what has i received.

    what i received is on left vs what it was suppose to be on right.

  • OK, I see the problem. The query logic is such that the generation of a notional Sales = 0 row depends on column 'Data', not on EmpId.

    In the case where Data = 'A', there are entries for the entire YearMonth 202201, so no new row is generated (your row in blue).

    If you want your query to generate new rows depending on EmpId rather than Data, the query must be adjusted.

    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.

  • I am stuck here with this now.. As i need to add few more columns like EmpID, DepID. And hence it is failing..

    I tried different partition by in the order , but it is still failing..

    ;WITH BaseData
    AS (SELECT Data = ISNULL(t.Data, LAG(t.Data, 1, NULL) OVER (ORDER BY t.Data,t.empid,rt.yearwk)) ,
    Empid = ISNULL(t.Empid, LAG(t.Empid, 1, NULL) OVER (ORDER BY t.Data,t.empid,rt.yearwk))
    ,Week = rt.yearwk
    ,Sales = ISNULL(t.sales, 0)
    ,YearMnt = rt.yearmnt
    ,PrevYearMnt = LAG(rt.yearmnt, 1) OVER (ORDER BY t.Data,t.empid, rt.yearwk)
    FROM #RefTable rt
    LEFT JOIN #Table t
    ON rt.yearwk = t.Week)
  • Which column, or columns, of data should be considered when generating the Sales = 0 rows?

    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.

  • As per the above example Combinations of Data, Empid ,sales by week.

  • That made it more complicated than I had expected and I had to adopt a different method, unfortunately containing cascading CTEs. Does this get you close?

    WITH BaseData1
    AS (SELECT c1.Empid
    ,c1.Data
    ,rt.yearwk
    ,rt.yearmnt
    ,Sales = SUM(IIF(rt.yearwk = c1.Week, c1.sales, 0))
    FROM #RefTable rt
    CROSS APPLY
    (
    SELECT t.Empid
    ,t.Data
    ,t.Week
    ,t.sales
    ,rt.yearwk
    ,rt.yearmnt
    FROM #Table t
    WHERE t.Week <= rt.yearwk
    AND t.Week + 2 > rt.yearwk
    ) c1
    GROUP BY c1.Empid
    ,c1.Data
    ,rt.yearwk
    ,rt.yearmnt)
    ,BaseData2
    AS (SELECT bd1.Empid
    ,bd1.Data
    ,bd1.yearwk
    ,bd1.yearmnt
    ,PrevYearMth = LAG(bd1.yearmnt, 1, NULL) OVER (PARTITION BY bd1.Empid, bd1.Data ORDER BY bd1.yearwk)
    ,bd1.Sales
    FROM BaseData1 bd1)
    SELECT bd2.Empid
    ,bd2.Data
    ,Week = bd2.yearwk
    ,bd2.Sales
    FROM BaseData2 bd2
    WHERE bd2.Sales > 0
    OR
    (
    bd2.PrevYearMth = bd2.yearmnt
    AND NOT EXISTS
    (
    SELECT 1
    FROM #Table t
    JOIN #RefTable rt
    ON rt.yearwk = t.Week
    WHERE t.Empid = bd2.Empid
    AND t.Data = bd2.Data
    AND t.Week > bd2.yearwk
    AND rt.yearmnt = bd2.yearmnt
    )
    )
    ORDER BY bd2.Empid
    ,bd2.Data
    ,bd2.yearwk;

    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.

  • It is working fine, but it is taking lot of time to do the process.

    Data is around 20 Billion records...

  • That is a lot of data. Have you looked at the execution plan? Are the underlying tables appropriately keyed and indexed?

    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 12 posts - 16 through 26 (of 26 total)

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