Get value of rows based on condition

  • 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

    • This topic was modified 3 weeks, 5 days ago by  IdealPR.
  • 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 2 (of 2 total)

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