September 17, 2021 at 11:47 pm
Hello,
I have the table with data as bellow :
create table tbl_Fact(
IdCmd int,
DateDEP datetime,
DateLIV datetime,
Stat varchar(15),
CA int,
CUST int
)
insert into tbl_Fact
select 100,'2016-05-12',null,'C',100,1
union
select 101,null,'2016-05-14','A',50,1
union
select 104,'2016-05-23',null,'C',120,1
union
select 105,null,'2016-05-26','A',80,1
union
select 106,null,'2016-05-22','A',100,2
union
select 107,'2016-05-30',null,'C',200,1
union
select 108,'2016-05-27',null,'C',50,2
union
select 109,'2016-06-15',null,'C',300,1
union
select 110,null,'2016-06-29','A',55,1
union
select 200,null,'2016-06-04','A',50,1
union
select 250,'2016-05-13',null,'C',70,1
union
select 261,null,'2016-05-15','A',10,1
union
select 277,'2016-05-24',null,'C',100,1
I want to retrieve the value like as bellow :
IdCmd DateDEP DateLIV Stat CA CUST Val
100 2016-05-12 00:00:00.000 NULL C 100 1
250 2016-05-13 00:00:00.000 NULL C 70 1
101 NULL 2016-05-14 00:00:00.000 A 50 1
261 NULL 2016-05-15 00:00:00.000 A 10 1 40
104 2016-05-23 00:00:00.000 NULL C 120 1
277 2016-05-24 00:00:00.000 NULL C 100 1
105 NULL 2016-05-26 00:00:00.000 A 80 1 40
107 2016-05-30 00:00:00.000 NULL C 200 1
200 NULL 2016-06-04 00:00:00.000 A 50 1 150
109 2016-06-15 00:00:00.000 NULL C 300 1 300
110 NULL 2016-06-29 00:00:00.000 A 55 1
106 NULL 2016-05-22 00:00:00.000 A 100 2
108 2016-05-27 00:00:00.000 NULL C 50 2 50
As bellow an explanation of the output :
The output of that i want is based on DateDep , DateLiv , Stat and Cust and condition in date
in the example as bellow , i have the Order 100 and 250 is cancelled and just after i have 2 order 101 and 261 Approved then the lost val is 40 because Highest Order is 100 it was Cancelled and i shipped 2 Orders with value 50 and 10 then 100 - 50-10 = 40
IdCmd DateDEP DateLIV Stat CA CUST Val
100 2016-05-12 00:00:00.000 NULL C 100 1
250 2016-05-13 00:00:00.000 NULL C 70 1
101 NULL 2016-05-14 00:00:00.000 A 50 1
261 NULL 2016-05-15 00:00:00.000 A 10 1 40
As bellow another example :
The order 107 was cancelled and after that i have Order 200 Approved the Lost value is 200 - 50 = 150
IdCmd DateDEP DateLIV Stat CA CUST Val
107 2016-05-30 00:00:00.000 NULL C 200 1
200 NULL 2016-06-04 00:00:00.000 A 50 1 150
The example as bellow is different :
The Order 109 is cancelled and just after i have an approved ORder but the ORder 109 is not is not shipped within 10 days (column Date Liv) then i have a lost of 300 in order 109
IdCmd DateDEP DateLIV Stat CA CUST Val
109 2016-06-15 00:00:00.000 NULL C 300 1 300
110 NULL 2016-06-29 00:00:00.000 A 55 1
How can i do that
Thanks
September 18, 2021 at 11:39 am
It's a lot going on. The query splits the rows into "gap and island" groupings labelled 'grp'. Within each grouping the CTE 'top_c_cte' selects the largest CA value when Stat='C'. Within each grouping the CTE 'bot_a_cte' selects the least recent CA value when Stat='A'. The groupings are used to calculate the 'lost' column. Maybe something like this
with
gaps_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap) as (
select *, case when Stat='C' and lag(Stat) over (order by cust,coalesce(DateDep,DateLiv))='A'
then 1 else 0 end
from #tbl_Fact),
grp_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp) as (
select *, sum(gap) over (order by cust,coalesce(DateDep,DateLiv)) grp
from gaps_cte),
top_c_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp) as (
select top 1 with ties *
from grp_cte
where Stat='C'
order by row_number() over (partition by grp order by ca desc)),
no_ship_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp, a_count, lost) as (
select tc.*, isnull(oa.a_count, 0) a_count,
case when isnull(oa.a_count, 0)>0 then tc.CA
when xa.a_count=0 then tc.CA
else 0 end lost
from top_c_cte tc
outer apply (select count(*)
from grp_cte g
where tc.grp=g.grp
and g.Stat='A') xa(a_count)
outer apply (select top 1 1
from grp_cte g
where tc.grp=g.grp
and g.Stat='A'
and datediff(day, tc.DateDEP, g.DateLIV)>10
order by g.DateLIV) oa(a_count)),
bot_a_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp) as (
select top 1 with ties *
from grp_cte
where Stat='A'
order by row_number() over (partition by grp order by DateLIV desc)),
lost_cte(IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp, ns_ca, sum_ca, lost) as (
select ba.*, isnull(ns.CA, 0), isnull(oa.sum_ca, 0),
case when isnull(ns.CA, 0)>0
then isnull(ns.CA, 0)-ba.CA-isnull(oa.sum_ca, 0)
else 0 end lost
from bot_a_cte ba
left join no_ship_cte ns on ba.grp=ns.grp
and ns.a_count=0
and ns.ca>0
outer apply (select sum(g.CA)
from grp_cte g
where g.grp=ba.grp
and g.IdCmd<>ba.IdCmd
and g.Stat='A'
and datediff(day, ns.DateDEP, g.DateLIV)<=10) oa(sum_ca)),
all_cte as (
select IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp, lost
from no_ship_cte
union all
select IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp, lost
from lost_cte
union all
select IdCmd, DateDEP, DateLIV, Stat, CA, CUST, gap, grp, 0
from grp_cte g
where not exists (select 1 from no_ship_cte ns where g.IdCmd=ns.IdCmd)
and not exists (select 1 from lost_cte l where g.IdCmd=l.IdCmd))
select *
from all_cte
order by CUST, coalesce(DateDep,DateLiv);
IdCmd DateDEP DateLIV Stat CA CUST gap grp lost
100 2016-05-12 00:00:00.000 NULL C 100 1 0 0 0
250 2016-05-13 00:00:00.000 NULL C 70 1 0 0 0
101 NULL 2016-05-14 00:00:00.000 A 50 1 0 0 0
261 NULL 2016-05-15 00:00:00.000 A 10 1 0 0 40
104 2016-05-23 00:00:00.000 NULL C 120 1 1 1 0
277 2016-05-24 00:00:00.000 NULL C 100 1 0 1 0
105 NULL 2016-05-26 00:00:00.000 A 80 1 0 1 40
107 2016-05-30 00:00:00.000 NULL C 200 1 1 2 0
200 NULL 2016-06-04 00:00:00.000 A 50 1 0 2 150
109 2016-06-15 00:00:00.000 NULL C 300 1 1 3 300
110 NULL 2016-06-29 00:00:00.000 A 55 1 0 3 0
106 NULL 2016-05-22 00:00:00.000 A 100 2 0 3 0
108 2016-05-27 00:00:00.000 NULL C 50 2 1 4 50
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply