Missing weekend data to Zero

Question

Post reply

Missing weekend data to Zero

LearnSQL

SSC-Addicted

Points: 407
More actions
October 2, 2022 at 12:38 am

#4094461
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
EDIT:
I have a reference time data table attached here which will hold weeks start and week end data something like
Yearweek ,eow
202201,202204
202202,202204
202203,202204
202204,202204
```
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 years, 2 months ago by LearnSQL.
Attachments:
You must be logged in to view attached files.

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 2

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

jschmidt 17654 SSCommitted Points: 1663 More actions · Answer 3

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:

1234,'A',10,202201,202204

1234,'A',10,202202,202204

1234,'A',9,202203,202204

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

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

https://www.sqlservercentral.com/forums/topic/make-last-week-to-zero

--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)

Missing weekend data to Zero

Attachments: