June 20, 2022 at 10:47 am
This was removed by the editor as SPAM
June 20, 2022 at 12:00 pm
Getting the below error.
Msg 127, Level 15, State 1, Line 62
If you are looking for sympathy. poor you!
If you want help resolving the error, please post the problematic query and the text of the error message.
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.
June 20, 2022 at 12:32 pm
As a single statement something similar to the query posted earlier. As has been mentioned more than once the WeekNo column in the example data is not sortable and it's a pita to reformat in code. To make easier I retyped your data with sortable WeekNo to match the YYYwWk column in the function
drop table if exists #datatab;
go
create table #datatab(
EmpNo INT NOT NULL
,WeekNo CHAR(8) NOT NULL
,Rnge SMALLINT NOT NULL);
insert #datatab(EmpNo, WeekNo, Rnge) values
(111, '2021W52', 12)
,(111, '2022W02', 10)
,(111, '2022W04', 17)
,(210, '2022W01', 22)
,(210, '2022W04', 55)
,(210, '2022W08', 15)
,(210, '2022W09', 26);
with
lead_cte as (
select *, lead(WeekNo) over(partition by EmpNo order by WeekNo) lead_wk
from #DataTab d)
select l.*, nxt.nxt_wk, cal.YYYYwWk, expanded.CalcRnge,
lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by cal.YYYYwWk) NewRnge
from lead_cte l
cross apply (select top(1) cd.YYYYwWk
from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK>l.WeekNo
order by cd.YYYYwWK) nxt(nxt_wk)
cross apply (select distinct YYYYwWK
from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK >= l.WeekNo
and cd.YYYYwWK<coalesce(l.lead_wk, nxt.nxt_wk)) cal(YYYYwWk)
cross apply (values (iif(l.WeekNo=cal.YYYYwWk, l.Rnge, 0))) expanded(CalcRnge)
order by l.EmpNo, cal.YYYYwWk;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 13, 2022 at 7:07 am
Hi ,
First would like to Thank Steve Collins for his time and effort on achieving this.
How to achieve the same with Archive records as well along with the current table records.
Below is the sample data i have in archive #datatab_arch, how can i join with the current table. Also attached the sample records of 111 record which i am suppose to get.
Why Archive is need is to take last 4 weeks of data from older record and find its last value and sum them up and do the other logic.
drop table if exists #datatab;
drop table if exists #datatab_arch;
go
create table #datatab_arch(
EmpNo INT NOT NULL
,WeekNo CHAR(8) NOT NULL
,Rnge SMALLINT NOT NULL);
insert #datatab_arch(EmpNo, WeekNo, Rnge) values
(111, '2021W46', 46)
,(111, '2021W47', 47)
,(111, '2021W48', 48)
,(210, '2021W51', 44)
create table #datatab(
EmpNo INT NOT NULL
,WeekNo CHAR(8) NOT NULL
,Rnge SMALLINT NOT NULL);
insert #datatab(EmpNo, WeekNo, Rnge) values
(111, '2021W50', 50)
,(111, '2021W51', 51)
,(111, '2021W52', 52)
,(111, '2022W02', 10)
,(111, '2022W04', 17)
,(210, '2022W01', 22)
,(210, '2022W04', 55)
,(210, '2022W08', 15)
,(210, '2022W09', 26);
Select * from #datatab_arch
--- Expected sample output for 111 record. This has Archive along with current records.
Select 111,'2021W45', 45,'2021W46', '2021W46', '2021W45', 45,0
UNION Select 111,'2021W46', 46,'2021W47', '2021W47', '2021W46', 46,45
UNION Select 111,'2021W47', 47,'2021W48', '2021W48', '2021W47', 47,46
UNION Select 111,'2021W48', 48,'2021W49', '2021W49', '2021W48', 48,47
UNION Select 111,'2021W49', 49,'2021W50', '2021W50', '2021W49', 49,48
UNION Select 111,'2021W50', 50,'2021W51', '2021W51', '2021W50', 50,0
UNION Select 111,'2021W51', 51,'2021W52', '2021W52', '2021W51', 51,50
UNION Select 111,'2021W52', 52,'2022W02', '2022W01', '2021W52', 52,51
;with
lead_cte as (
select *, lead(WeekNo) over(partition by EmpNo order by WeekNo) lead_wk
from #DataTab d)
select l.*, nxt.nxt_wk, cal.YYYYwWk, expanded.CalcRnge,
lag(expanded.CalcRnge, 1, 0) over (partition by l.empno order by cal.YYYYwWk) NewRnge
from lead_cte l
cross apply (select top(1) cd.YYYYwWk
from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK>l.WeekNo
order by cd.YYYYwWK) nxt(nxt_wk)
cross apply (select distinct YYYYwWK
from dbo.CalendarByDateFirst('20211201', '20221231', 7) cd
where cd.YYYYwWK >= l.WeekNo
and cd.YYYYwWK<coalesce(l.lead_wk, nxt.nxt_wk)) cal(YYYYwWk)
cross apply (values (iif(l.WeekNo=cal.YYYYwWk, l.Rnge, 0))) expanded(CalcRnge)
order by l.EmpNo, cal.YYYYwWk;
July 18, 2022 at 8:08 am
Any suggestions or reference please ?
July 18, 2022 at 2:49 pm
Thanks to Jeff Moden in this thread for the function used here
In row 1 of the expected output from which input table does the Rnge value 45 come from? Why not combine the tables using UNION ALL or in a temp table?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply