locked
Create pivot in sql where price multiply for last months RRS feed

  • Question

  • From last topic, the Rachel_Wang's solution works

    create table gr
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    supl int,
    rt int,
    suplrt int, --suplrt-rt
    sales int,
    rt1 int,
    salesrt1 int,--suplrt-rt1
    price int
    );
    insert into gr values
    (x,y,2,8,(2-8),4,2,(4-2),10)
    ;

    If Object_ID('gr','U') Is Not Null Drop Table gr
    create table gr
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    supl int,
    rt int,
    suplrt int, --supl-rt
    sales int,
    rt1 int,
    salesrt1 int,--sales-rt1
    price int
    );
    insert into gr values
    ('x','y',2,8,(2-8),4,2,(4-2),10)
    
    select gr1,gr2,supl*price as supl,
    rt*price as rt,
    price*suplrt as suplrt,
    sales*price as sales,
    rt1*price as rt1,
    price*salesrt1 as salesrt1
     
    from gr

    but what to do, if price for group has rows by some  months

    i.e. reproducible table:

    the first table gr

    create table gr
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    supl int,
    rt int,
    suplrt int, --suplrt-rt
    sales int,
    rt1 int,
    salesrt1 int,--suplrt-rt1

    );
    insert into gr values
    (x,y,2,8,-6,4,2,(4-2))
    ;

    must by joined  to price table

    create table price
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    date_begin datetime,
    date_end datetime,
    price int

    );
    insert into price values
    (x,y,'15.07.2014','31.01.2015',160),
    (x,y,'01.02.2015','31.03.2015',176),
    (x,y,'01.04.2015','28.02.2017',202.4),
    (x,y,'01.03.2017','31.01.2019',212.52),
    (x,y,'01.02.2019','NULL',225.27);

    but only on last date. Last date in price is where date_begin has date value,and date_end 

    wherein has NULL value (for concrete group gr1 and gr2)  in this case 01.02.2019-NULL

    after join must be table like 

    gr1 gr2 supl rt suplrt sales rt1 salesrt1 date_begin date_end price
    x y 2 8        (2-8) 4 2 (4-2) 01.02.2019 NULL 225.27

    225.27

    so then multiply must be only for price =225.27 , because it is  price for last day

    how to rework multiplication code?


    Tuesday, March 19, 2019 12:32 PM

Answers

  • Can you review you question to to clean up a few things?

    You are not pivot anything as you mentioned in your title.

    Please make sure your script is working before you post it. Check to choose right data type for your columns.

    create table gr
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    supl int,
    rt int,
    suplrt int, --suplrt-rt
    sales int,
    rt1 int,
    salesrt1 int,--suplrt-rt1
    
    );
    insert into gr values
    ('x','y',2,8,-6,4,2,(4-2))
    ;
    
    create table price
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    date_begin datetime,
    date_end datetime,
    price decimal(6,2)
    
    );
    insert into price values
    ('x','y','2014-07-15','2015-01-31',160),
    ('x','y','2015-02-01','2015-03-31',176),
    ('x','y','2015-04-01','2017-02-28',202.4),
    ('x','y','2017-03-01','2019-01-31',212.52),
    ('x','y','2019-02-01',NULL,225.27);
    ;with mycte as (
    select *, row_number() Over(partition by gr1,gr2  Order by date_begin desc )  rn  
    from price) 
    
    select m.gr1,m.gr2,supl*price as supl,
    rt*price as rt,
    price*suplrt as suplrt,
    sales*price as sales,
    rt1*price as rt1,
    price*salesrt1 as salesrt1
    ,date_begin	date_end,price
    
    from gr g join mycte m on g.gr1=m.gr1 and g.gr2=m.gr2 and m.rn=1
    
    
    
    drop table  gr , price


    Tuesday, March 19, 2019 2:19 PM
  • Hi Jury,

    You could also achieve it like this.

    create table gr
    (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     supl int,
     rt int,
     suplrt int, --suplrt-rt
     sales int,
     rt1 int,
     salesrt1 int,--suplrt-rt1
     );
     insert into gr values
     ('x','y',2,8,-6,4,2,(4-2));
    GO
    
    create table price
     (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     date_begin datetime,
     date_end datetime,
     price NUMERIC(18,2)
     );
     
     SET DATEFORMAT DMY
    
     insert into price values
     ('x','y','15.07.2014','31.01.2015',160),
     ('x','y','01.02.2015','31.03.2015',176),
     ('x','y','01.04.2015','28.02.2017',202.4),
     ('x','y','01.03.2017','31.01.2019',212.52),
     ('x','y','01.02.2019', NULL,225.27);
     GO
    
    --Query
    SELECT 
    T.gr1,T.gr2,T.supl,T.rt,T.suplrt,T.sales,T.rt1,T.salesrt1,
    T1.date_begin,
    T1.date_end,
    T1.price
    FROM gr T
    CROSS APPLY (SELECT TOP 1 date_begin,date_end,price FROM price WHERE gr1=T.gr1 AND gr2=T.gr2 ORDER BY date_begin DESC) T1

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 20, 2019 6:59 AM

All replies

  • Can you review you question to to clean up a few things?

    You are not pivot anything as you mentioned in your title.

    Please make sure your script is working before you post it. Check to choose right data type for your columns.

    create table gr
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    supl int,
    rt int,
    suplrt int, --suplrt-rt
    sales int,
    rt1 int,
    salesrt1 int,--suplrt-rt1
    
    );
    insert into gr values
    ('x','y',2,8,-6,4,2,(4-2))
    ;
    
    create table price
    (
    gr1 nvarchar(20),
    gr2 nvarchar(20),
    date_begin datetime,
    date_end datetime,
    price decimal(6,2)
    
    );
    insert into price values
    ('x','y','2014-07-15','2015-01-31',160),
    ('x','y','2015-02-01','2015-03-31',176),
    ('x','y','2015-04-01','2017-02-28',202.4),
    ('x','y','2017-03-01','2019-01-31',212.52),
    ('x','y','2019-02-01',NULL,225.27);
    ;with mycte as (
    select *, row_number() Over(partition by gr1,gr2  Order by date_begin desc )  rn  
    from price) 
    
    select m.gr1,m.gr2,supl*price as supl,
    rt*price as rt,
    price*suplrt as suplrt,
    sales*price as sales,
    rt1*price as rt1,
    price*salesrt1 as salesrt1
    ,date_begin	date_end,price
    
    from gr g join mycte m on g.gr1=m.gr1 and g.gr2=m.gr2 and m.rn=1
    
    
    
    drop table  gr , price


    Tuesday, March 19, 2019 2:19 PM
  • Hi Jury,

    You could also achieve it like this.

    create table gr
    (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     supl int,
     rt int,
     suplrt int, --suplrt-rt
     sales int,
     rt1 int,
     salesrt1 int,--suplrt-rt1
     );
     insert into gr values
     ('x','y',2,8,-6,4,2,(4-2));
    GO
    
    create table price
     (
     gr1 nvarchar(20),
     gr2 nvarchar(20),
     date_begin datetime,
     date_end datetime,
     price NUMERIC(18,2)
     );
     
     SET DATEFORMAT DMY
    
     insert into price values
     ('x','y','15.07.2014','31.01.2015',160),
     ('x','y','01.02.2015','31.03.2015',176),
     ('x','y','01.04.2015','28.02.2017',202.4),
     ('x','y','01.03.2017','31.01.2019',212.52),
     ('x','y','01.02.2019', NULL,225.27);
     GO
    
    --Query
    SELECT 
    T.gr1,T.gr2,T.supl,T.rt,T.suplrt,T.sales,T.rt1,T.salesrt1,
    T1.date_begin,
    T1.date_end,
    T1.price
    FROM gr T
    CROSS APPLY (SELECT TOP 1 date_begin,date_end,price FROM price WHERE gr1=T.gr1 AND gr2=T.gr2 ORDER BY date_begin DESC) T1

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 20, 2019 6:59 AM