Make last Week to zero

Question

Post reply

Make last Week to zero

LearnSQL

SSC-Addicted

Points: 407

September 14, 2022 at 8:01 am

#4087442

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!

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

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

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

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 2 years, 3 months ago by Phil Parkin.
This reply was modified 2 years, 3 months ago by Phil Parkin.

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 2

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;

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

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

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 4

LearnSQL

SSC-Addicted

Points: 407

September 14, 2022 at 8:36 am

#4087467

Data may or may not overlap.

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

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?

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 6

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

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

LearnSQL

SSC-Addicted

Points: 407

September 14, 2022 at 12:11 pm

#4087542

Any thoughts or suggestions

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

LearnSQL

SSC-Addicted

Points: 407

September 15, 2022 at 6:19 am

#4087909

@Phil, Do you have any suggestions ?

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

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;

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

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

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;

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

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.

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

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;

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 13

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

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

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;

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