Previous Week data to current week data

Question

Post reply

Previous Week data to current week data

LearnSQL

SSC-Addicted

Points: 407

June 9, 2022 at 9:50 am

#4046787

Hi All,

how to get current week data to next week data.

#DataTab input table and #DataTabNew desired output table.

Range

DROP TABLE IF EXISTS #DataTab;
DROP TABLE IF EXISTS #DataTabNew;

CREATE TABLE #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(3) NOT NULL
   ,Rnge SMALLINT NOT NULL
);

CREATE TABLE #DataTabNew
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(3) NOT NULL
   ,Rnge SMALLINT NOT NULL
   ,NewRnge SMALLINT NOT NULL
);

INSERT #DataTab
(
    EmpNo
   ,WeekNo
   ,Rnge
)
VALUES
(111, 'W1', 12)
,(111, 'W2', 10)
,(111, 'W4', 17)
,(210, 'W1', 22)
,(210, 'W4', 55)
,(210, 'W8', 15)
,(210, 'W9', 26);

Select * FROM #DataTab 

Insert into #DataTabNew
Select 111 EmpNo,'W1'WeekNo,12 Rnge,0 NewRnge
UNION Select 111,'W2',10,12
UNION Select 111,'W3',0,10
UNION Select 111,'W4',17,0
UNION Select 210,'W1',22,0
UNION Select 210,'W2',0,0
UNION Select 210,'W3',0,0
UNION Select 210,'W4',55,0
UNION Select 210,'W5',0,55
UNION Select 210,'W6',0,0
UNION Select 210,'W7',0,0
UNION Select 210,'W8',15,0
UNION Select 210,'W9',26,15

Select * from #DataTabNew order by 1

Thanks!

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

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

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

In line 6 of DataTabNew, should NewRnge be 22?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

Yes Phil you are correct it should be 22 . Also How to take from last year last week data to this year first week data.

DROP TABLE IF EXISTS #DataTab;
DROP TABLE IF EXISTS #DataTabNew;

CREATE TABLE #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(30) NOT NULL
   ,Rnge SMALLINT NOT NULL
);

CREATE TABLE #DataTabNew
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(30) NOT NULL
   ,Rnge SMALLINT NOT NULL
   ,NewRnge SMALLINT NOT NULL
);

INSERT #DataTab
(
    EmpNo
   ,WeekNo
   ,Rnge
)
VALUES
(111, 'W51-2021', 13)
,(111, 'W52-2021', 14)
,(111, 'W1-2022', 12)
,(111, 'W2-2022', 10)
,(111, 'W4-2022', 17)
,(210, 'W1-2022', 22)
,(210, 'W4-2022', 55)
,(210, 'W8-2022', 15)
,(210, 'W9-2022', 26);

Select * FROM #DataTab 

Insert into #DataTabNew
Select 111 ,'W51-2021',13 ,0 
UNION Select 111 ,'W52-2021',14 ,13 
UNION Select 111 ,'W1-2022',12 ,14
UNION Select 111,'W2-2022',10,12
UNION Select 111,'W3-2022',0,10
UNION Select 111,'W4-2022',17,0
UNION Select 210,'W1-2022',22,0
UNION Select 210,'W2-2022',0,22
UNION Select 210,'W3-2022',0,0
UNION Select 210,'W4-2022',55,0
UNION Select 210,'W5-2022',0,55
UNION Select 210,'W6-2022',0,0
UNION Select 210,'W7-2022',0,0
UNION Select 210,'W8-2022',15,0
UNION Select 210,'W9-2022',26,15

Select * from #DataTabNew order by 1

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

You could fill in the missing weeks by using the LEAD(WeekNo) function to determine start/end points of the ranges. To expand rows across the ranges you could use the tally function described in this article. You might find it's a really convenient function to know about. Once the week ranges have been filled out you could SELECT the NewRnge column using the LAG function. Maybe like this

with lead_cte as (
    select *, lead(wk_num) over (partition by empno order by WeekNo) lead_wk
    from #DataTab d
         cross apply (values (cast(right(d.WeekNo, 2) as int))) calc(wk_num))
select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,
       lag(expanded.CalcRnge, 1, 0) over (partition by empno order by WeekNo) NewRnge
from lead_cte l
     cross apply dbo.fnTally(0, isnull(l.lead_wk, l.wk_num+1)-l.wk_num-1) fn
     cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

dbo.fnTally

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/        (@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS 
 RETURN WITH
  H2(N) AS ( SELECT 1 
               FROM (VALUES
                     (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
                    )V(N))            --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
            SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
            SELECT TOP(@MaxN)
                   N = ROW_NUMBER() OVER (ORDER BY N)
              FROM H8
;

This reply was modified 2 years, 5 months ago by Steve Collins.

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

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

Getting the below error.

Msg 127, Level 15, State 1, Line 62

A TOP N or FETCH rowcount value may not be negative.

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

Ok I updated the query so the ORDER BY is using the numeric 'wk_num' column instead of 'WeekNo' which is CHAR(3) and other slight changes too

with lead_cte as (
    select *, lead(calc.wk_num, 1, 0) over (partition by d.empno order by calc.wk_num) lead_wk
    from #DataTab d
         cross apply (values (cast(right(d.WeekNo, 2) as int))) calc(wk_num))
select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,
       lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by l.wk_num) NewRnge
from lead_cte l
     cross apply dbo.fnTally(0, iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1)) fn
     cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

This reply was modified 2 years, 5 months ago by Steve Collins.

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

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

Thanks for your correct and suggestion!

With the following i was able to get data for non missing rows, here for Emp 111 there is a missing week for w3-2022 and same missing for emp 210

with lead_cte as (

select *, lead(wk_num, 1, 0) over (partition by empno order by wk_num) lead_wk

from #DataTab d

cross apply (values (cast(right(d.WeekNo, 2) as int))) calc(wk_num))

select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,

lag(expanded.CalcRnge, 1, 0) over (partition by empno order by wk_num) NewRnge

from lead_cte l

cross apply dbo.fnTally(0, iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1)) fn

cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

Expected result for 210:

Select 210,'W1-2022',22,0

UNION Select 210,'W2-2022',0,22

UNION Select 210,'W3-2022',0,0

UNION Select 210,'W4-2022',55,0

UNION Select 210,'W5-2022',0,55

UNION Select 210,'W6-2022',0,0

UNION Select 210,'W7-2022',0,0

UNION Select 210,'W8-2022',15,0

UNION Select 210,'W9-2022',26,15

Thanks!

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

Hi Steve Collins, The below code again gives the "A TOP N or FETCH rowcount value may not be negative. " error, since the weekno column was not trimmed properly as it has char(30).

;with lead_cte as (
    select cast(right(RTRIM(LTRIM(d.WeekNo)), 2) as int)aa,*, lead(calc.wk_num, 1, 0) over (partition by d.empno order by calc.wk_num) lead_wk
    from #DataTab d
         cross apply (values (cast(right(RTRIM(LTRIM(d.WeekNo)), 2) as int))) calc(wk_num))
select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,
       lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by l.wk_num) NewRnge
   ,iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1),l.lead_wk,l.lead_wk-l.wk_num-1
from lead_cte l
     cross apply dbo.fnTally(0, iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1)) fn
     cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

Is there any possible way to achieve this ?? I am struck with my real time scenario.

Much appreciated if any one could provide possible way and approaches, Thanks in advance !

Steve Collins SSCrazy Points: 2157 More actions · Answer 8

Suppose your 'WeekNo' column contains a 'W' and maybe/(maybe not) a '-' and the leading 0's and spaces are all over the place. The key calculation is the numeric wk_num column derived from 'WeekNo'.

drop table if exists #DataTab;
go
create table #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(30) NOT NULL
   ,Rnge SMALLINT NOT NULL
);

insert #DataTab
(
    empno
   ,weekno
   ,rnge
)
values
(111, '     W0000051-2021', 13)
,(111, '  W052-2021', 14)
,(111, '     W1-2022', 12)
,(111, 'W2', 10)
,(111, 'W4', 17)
,(210, '000W00001-2022', 22)
,(210, '     W00004', 55)
,(210, '    W8-2022', 15)
,(210, 'W9-2022', 26);


with lead_cte as (
    select *, lead(calc.wk_num, 1, 0) over (partition by d.empno order by calc.wk_num) lead_wk
    from #DataTab d
         cross apply (values (len(d.WeekNo),
                          charindex('W', d.WeekNo),
                          charindex('-', d.WeekNo))) ndx(wk_len, w_loc, dash_loc)
         cross apply (values (cast(substring(d.WeekNo, 
                                         ndx.w_loc+1,
                                         iif(ndx.dash_loc=0, ndx.wk_len-ndx.w_loc, ndx.dash_loc-ndx.w_loc-1))
                                   as numeric(29, 0)))) calc(wk_num))
select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,
       lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by l.wk_num) NewRnge
from lead_cte l
     cross apply dbo.fnTally(0, iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1)) fn
     cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

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

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

Please be informed that there will not be any raw data or any inclusion of data for weekno column it will be in same format W52-2021 , W1-2022 etc..

I guess now i missed the total path now, in the first sample provided by you worked well expect the missing week's data as it has expected sequence FY2021 followed by 2022 W52-2021 followed by W1-2022 and so on for W4-2022 it was suppose to 0 for NewRnge as it has missing data for W3-2022 and W3-2022 should have NewRnge as 10.

;with lead_cte as (
    select cast(right(RTRIM(LTRIM(d.WeekNo)), 2) as int)aa,*, lead(calc.wk_num, 1, 0) over (partition by d.empno order by calc.wk_num) lead_wk
    from #DataTab d
         cross apply (values (cast(right((d.WeekNo), 2) as int))) calc(wk_num))
select l.*, concat('W', l.wk_num+fn.N) WeekNo, fn.N,
       lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by l.wk_num) NewRnge
   ,iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1),l.lead_wk,l.lead_wk-l.wk_num-1
from lead_cte l
     cross apply dbo.fnTally(0, iif(l.lead_wk=0, 0, l.lead_wk-l.wk_num-1)) fn
     cross apply (values (iif(fn.N>0, 0, l.Rnge))) expanded(CalcRnge);

Range

But where as in the latest sample provided by you it now not following the sequence for FY 2021 and 2022 it misleads the entire requirement.

Range

Should i need to use first sample to achieve and try add zero before coming to CTE ? Will that work ?

Thanks!

Steve Collins SSCrazy Points: 2157 More actions · Answer 10

In the original question above the WeekNo column is CHAR(3). In your response to Phil I did not read past the word "Yes" (which is the 1st word) because it confirmed what I thought I already knew. Your reply to Phil switched the column type to CHAR(30) and added the year-over-year part. Really changes to the question belong in the lead topic and not in the responses. How to make the numeric value extracted from WeekNo ordinal across years? You could SUBSTRING and CAST the year value (yr) and week value (wk_num) to calculate ordinal_wk = yr*100 + wk_num. If 'WeekNo' is 'W48-2021' the calculated ordinal_wk = 202148. If 'WeekNo' is 'W47-2021' the calculated ordinal_wk = 202147

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

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

Hi ,

The below code was unable to take correct chars which is required for the calculation.

DROP TABLE IF EXISTS #DataTab;
DROP TABLE IF EXISTS #DataTabNew;

CREATE TABLE #DataTab
(
    EmpNo INT NOT NULL
   ,WeekNo CHAR(8) NOT NULL
   ,Rnge SMALLINT NOT NULL
);
INSERT #DataTab
(
    EmpNo
   ,WeekNo
   ,Rnge
)
VALUES
(111, 'W52-2021', 12)
,(111, 'W2-2022', 10)
,(111, 'W4-2022', 17)
,(210, 'W1-2022', 22)
,(210, 'W4-2022', 55)
,(210, 'W8-2022', 15)
,(210, 'W9-2022', 26);

Select Replace(WeekNo,'-','') WeekNo, EmpNo,  Rnge into #DataTabNew from #DataTab
Select WeekNo ,LEFT(SUBSTRING(((Weekno)),2,7),2),
Right(SUBSTRING(LTRIM(RTRIM(Weekno)),2,7),4),SUBSTRING(LTRIM(RTRIM(Weekno)),2,7),
 len(Weekno), EmpNo,  Rnge from #DataTabNew

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

I have tried different ways and now able to get some what close.

What has happened is that after 202152 it is going till 202200 not to 202201. how to achieve here ?

Range

Steve Collins SSCrazy Points: 2157 More actions · Answer 13

These are ISO weeks? If so filling in gaps using a tally function is probably not a good approach imo. Needs a calendar table

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

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

Steve ,

As you have created the dbo.CalendarByDateFirstRefactored function in other thread, will you be able to resolve my issue.

Thanks a lot for your effort and time.