Make last Week to zero

Question

Make last Week to zero

Viewing 12 posts - 16 through 26 (of 26 total)

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

LearnSQL SSC-Addicted Points: 407 More actions · Answer 1

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;

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 3

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;

Phil Parkin SSC Guru Points: 246986 More actions · Answer 4

Are you saying that the line in blue is incorrect?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 5

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.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 7

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)

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

Which column, or columns, of data should be considered when generating the Sales = 0 rows?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 9

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 10

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

LearnSQL SSC-Addicted Points: 407 More actions · Answer 11

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

Data is around 20 Billion records...

Phil Parkin SSC Guru Points: 246986 More actions · Answer 12

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel