none
Finding the minimum of prices across specific dates and price ranges RRS feed

  • Question

  • Hi Team,

    I am trying to do the promotional analysis by taking the minimum of the price changes across two different time frames for items and tie it to the vendor level ...Following are the tables..Can you please help here...

    Table 1

    create table #table1

    (sinid varchar(30),

    ts timestamp,

    from_price float,

    to_price float)

    Insert into #table1 values

    ('b1234','2017-12-10 22:49:56:007','160.2','158.2'),
    ('b1234','2017-12-10 23:49:56:007','158','154'),
    ('b1234','2017-12-10 20:49:56:007','168','164'),
    ('c3412','2017-12-10 20:49:56:007','123.7','120.5'),
    ('c3412','2017-12-10 20:45:56:007','126','119'),
    ('c3412','2017-12-10 20:40:56:007','122','124'),
    ('b1234','2017-12-05 20:42:56:007','156.1','152.4'),
    ('b1234','2017-12-05 20:35:56:007','150','148.5'),
    ('b1234','2017-12-05 20:30:56:007','156','152'),
    ('c3412','2017-12-05 19:35:56:007','122.5','120'),
    ('c3412','2017-12-05 19:30:56:007','125.6','123.4'),

    ('c3412','2017-12-05 18:30:56:007','123.4','122')

    Table 2

    create table #table2

    (itemid int,

    lastsinid varchar(30),

    flag int,

    quantity int)

    insert into #table2 values

    ('3456','b1234','1','4'),
    ('4532','c3412','0','5'),

    ('6734','y6754','0','3')

    Table 3

    create table #table3

    (itemid int,

    vdrid varchar(30)

    )

    insert into #table3 values

    ('3456','yx67'),
    ('4532','yx67'),

    ('6734','yu56')

    Minimum price calculation for item level at a day 
    for example :item b1234 the minimum price for the day 10th Dec is calculated as min (min(from_price),min(to_price))=min(158,154)=154
    Similarly for 5th dec it is calculated as  min (min(from_price),min(to_price))=min(150,148.5)=148.5
    So the percentage diff is= (154-148.5)/148.5*100=3.7 

    Similarly for C3412 for 10th Dec it is min (122,119)=119

    Similarly for C3412 for 5th Dec it is min (122.5,120)=120

    so the percentage diff is =(119-120)/120*100 =-0.833333333

    Since c3412 and B1234 belong to the same vendor  yx67 the vendor level mean is  =(3.7-0.8333)/2=1.4335

    

    
    The expected output is :
    VdrID SinID Item ID  Min Price-10th Dec  Min Price -5th Dec  Percentage difference  Vendor Level Mean 
    yx67 b1234 3456 154 148.5 3.7 1.4335
    yx67 c3412 4532 119 120 -0.833 1.4335

    

    Thanks,

    Arun


    Arun

    Thursday, June 13, 2019 2:47 PM

Answers

  • Hi Arun,

    here is the query that I have come-up with.

    WITH item_price
    AS (
    	SELECT sinid,
    		cast(ts AS DATE) ts_date,
    		iif(min(from_price) < min(to_price), min(from_price), min(to_price)) min_price,
    		lead(iif(min(from_price) < min(to_price), min(from_price), min(to_price))) OVER (
    			PARTITION BY sinid ORDER BY cast(ts AS DATE) DESC
    			) prev_min_price
    	FROM #table1
    	GROUP BY sinid,
    		cast(ts AS DATE)
    	),
    item_per_diff
    AS (
    	SELECT sinid,
    		min_price,
    		prev_min_price,
    		100.0 * (min_price - prev_min_price) / prev_min_price per_diff
    	FROM item_price
    	WHERE prev_min_price IS NOT NULL
    	)
    SELECT vdrid AS 'VdrId',
    	sinid AS 'SinId',
    	t2.itemid AS 'Item Id',
    	min_price AS 'Min Price-10th Dec',
    	prev_min_price AS 'Min Price -5th Dec',
    	format(per_diff, '#0.##') AS 'Percentage difference',
    	format(sum(per_diff) OVER (PARTITION BY vdrid) / count(*) OVER (PARTITION BY vdrid),'##.##') AS 'Vendor Level Mean'
    FROM item_per_diff ipd
    INNER JOIN #table2 t2 ON t2.lastsinid = ipd.sinid
    INNER JOIN #table3 t3 ON t3.itemid = t2.itemid;

    Output:

    Thursday, June 13, 2019 4:28 PM
  • Hi Hellothere8028,

     

    Thank you for your issue.

     

    In your table structure, you use 'timestamp'.  Timestamp is a deprecated synonym for rowversion. Rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

     

    So I change 'timestamp' into 'datetime2', please try following script.

     
    ---drop table #table1
    ---drop table #table2
    ---drop table #table3
    go
    create table #table1
    (sinid varchar(30),
    ts datetime2 ,
    from_price float,
    to_price float)
    Insert into #table1 values
    ('b1234','2017-12-10 22:49:56:007','160.2','158.2'),
    ('b1234','2017-12-10 23:49:56:007','158','154'),
    ('b1234','2017-12-10 20:49:56:007','168','164'),
    ('c3412','2017-12-10 20:49:56:007','123.7','120.5'),
    ('c3412','2017-12-10 20:45:56:007','126','119'),
    ('c3412','2017-12-10 20:40:56:007','122','124'),
    ('b1234','2017-12-05 20:42:56:007','156.1','152.4'),
    ('b1234','2017-12-05 20:35:56:007','150','148.5'),
    ('b1234','2017-12-05 20:30:56:007','156','152'),
    ('c3412','2017-12-05 19:35:56:007','122.5','120'),
    ('c3412','2017-12-05 19:30:56:007','125.6','123.4'),
    ('c3412','2017-12-05 18:30:56:007','123.4','122')
    
    create table #table2
    (itemid int,
    lastsinid varchar(30),
    flag int,
    quantity int)
    insert into #table2 values
    ('3456','b1234','1','4'),
    ('4532','c3412','0','5'),
    ('6734','y6754','0','3')
    
    create table #table3
    (itemid int,
    vdrid varchar(30)
    )
    insert into #table3 values
    ('3456','yx67'),
    ('4532','yx67'),
    ('6734','yu56')
    
    select * from #table1
    select * from #table2
    select * from #table3
    
    ;with cte as(
    select  distinct sinid,convert(varchar(6),ts,113) as ts,
    case when  min(from_price)over(partition by sinid,cast(ts as date) ) <=min(to_price)over(partition by sinid,cast(ts as date) ) 
    then min(from_price)over(partition by sinid,cast(ts as date) ) else min(to_price)over(partition by sinid,cast(ts as date) )  end [min Price] 
    from #table1),
    cte1 as (
    select sinid, [10 Dec], [05 Dec], 
    cast(([10 Dec]-[05 Dec]) /[05 Dec]*100 as decimal(10,3))  [Percentage difference]
    from cte 
    PIVOT (
    	MAX([min Price])
    	FOR [ts] IN ([05 Dec], [10 Dec])
    ) AS pvt)
    
    select vdrid,a.SinID,b.itemid,[10 Dec]  [Min Price-10th Dec], 
    [05 Dec] [Min Price -5th Dec], [Percentage difference],
    cast(avg([Percentage difference])over(partition by vdrid) as decimal(10,4)) [Vendor Level Mean]
     from cte1 a join #table2 b on a.sinid=b.lastsinid
    join #table3 c on b.itemid=c.itemid
    /*
    vdrid                          SinID                          itemid      Min Price-10th Dec     Min Price -5th Dec     Percentage difference                   Vendor Level Mean
    ------------------------------ ------------------------------ ----------- ---------------------- ---------------------- --------------------------------------- ---------------------------------------
    yx67                           b1234                          3456        154                    148.5                  3.704                                   1.4355
    yx67                           c3412                          4532        119                    120                    -0.833                                  1.4355
    */
    
    
    

    Best Regards,

    Rachel


    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.

    • Proposed as answer by Ashin_c Friday, June 14, 2019 7:50 AM
    • Marked as answer by Hellothere8028 Monday, June 17, 2019 11:38 AM
    Friday, June 14, 2019 6:31 AM

All replies

  • Hi Arun,

    here is the query that I have come-up with.

    WITH item_price
    AS (
    	SELECT sinid,
    		cast(ts AS DATE) ts_date,
    		iif(min(from_price) < min(to_price), min(from_price), min(to_price)) min_price,
    		lead(iif(min(from_price) < min(to_price), min(from_price), min(to_price))) OVER (
    			PARTITION BY sinid ORDER BY cast(ts AS DATE) DESC
    			) prev_min_price
    	FROM #table1
    	GROUP BY sinid,
    		cast(ts AS DATE)
    	),
    item_per_diff
    AS (
    	SELECT sinid,
    		min_price,
    		prev_min_price,
    		100.0 * (min_price - prev_min_price) / prev_min_price per_diff
    	FROM item_price
    	WHERE prev_min_price IS NOT NULL
    	)
    SELECT vdrid AS 'VdrId',
    	sinid AS 'SinId',
    	t2.itemid AS 'Item Id',
    	min_price AS 'Min Price-10th Dec',
    	prev_min_price AS 'Min Price -5th Dec',
    	format(per_diff, '#0.##') AS 'Percentage difference',
    	format(sum(per_diff) OVER (PARTITION BY vdrid) / count(*) OVER (PARTITION BY vdrid),'##.##') AS 'Vendor Level Mean'
    FROM item_per_diff ipd
    INNER JOIN #table2 t2 ON t2.lastsinid = ipd.sinid
    INNER JOIN #table3 t3 ON t3.itemid = t2.itemid;

    Output:

    Thursday, June 13, 2019 4:28 PM
  • Hi Hellothere8028,

     

    Thank you for your issue.

     

    In your table structure, you use 'timestamp'.  Timestamp is a deprecated synonym for rowversion. Rowversion is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

     

    So I change 'timestamp' into 'datetime2', please try following script.

     
    ---drop table #table1
    ---drop table #table2
    ---drop table #table3
    go
    create table #table1
    (sinid varchar(30),
    ts datetime2 ,
    from_price float,
    to_price float)
    Insert into #table1 values
    ('b1234','2017-12-10 22:49:56:007','160.2','158.2'),
    ('b1234','2017-12-10 23:49:56:007','158','154'),
    ('b1234','2017-12-10 20:49:56:007','168','164'),
    ('c3412','2017-12-10 20:49:56:007','123.7','120.5'),
    ('c3412','2017-12-10 20:45:56:007','126','119'),
    ('c3412','2017-12-10 20:40:56:007','122','124'),
    ('b1234','2017-12-05 20:42:56:007','156.1','152.4'),
    ('b1234','2017-12-05 20:35:56:007','150','148.5'),
    ('b1234','2017-12-05 20:30:56:007','156','152'),
    ('c3412','2017-12-05 19:35:56:007','122.5','120'),
    ('c3412','2017-12-05 19:30:56:007','125.6','123.4'),
    ('c3412','2017-12-05 18:30:56:007','123.4','122')
    
    create table #table2
    (itemid int,
    lastsinid varchar(30),
    flag int,
    quantity int)
    insert into #table2 values
    ('3456','b1234','1','4'),
    ('4532','c3412','0','5'),
    ('6734','y6754','0','3')
    
    create table #table3
    (itemid int,
    vdrid varchar(30)
    )
    insert into #table3 values
    ('3456','yx67'),
    ('4532','yx67'),
    ('6734','yu56')
    
    select * from #table1
    select * from #table2
    select * from #table3
    
    ;with cte as(
    select  distinct sinid,convert(varchar(6),ts,113) as ts,
    case when  min(from_price)over(partition by sinid,cast(ts as date) ) <=min(to_price)over(partition by sinid,cast(ts as date) ) 
    then min(from_price)over(partition by sinid,cast(ts as date) ) else min(to_price)over(partition by sinid,cast(ts as date) )  end [min Price] 
    from #table1),
    cte1 as (
    select sinid, [10 Dec], [05 Dec], 
    cast(([10 Dec]-[05 Dec]) /[05 Dec]*100 as decimal(10,3))  [Percentage difference]
    from cte 
    PIVOT (
    	MAX([min Price])
    	FOR [ts] IN ([05 Dec], [10 Dec])
    ) AS pvt)
    
    select vdrid,a.SinID,b.itemid,[10 Dec]  [Min Price-10th Dec], 
    [05 Dec] [Min Price -5th Dec], [Percentage difference],
    cast(avg([Percentage difference])over(partition by vdrid) as decimal(10,4)) [Vendor Level Mean]
     from cte1 a join #table2 b on a.sinid=b.lastsinid
    join #table3 c on b.itemid=c.itemid
    /*
    vdrid                          SinID                          itemid      Min Price-10th Dec     Min Price -5th Dec     Percentage difference                   Vendor Level Mean
    ------------------------------ ------------------------------ ----------- ---------------------- ---------------------- --------------------------------------- ---------------------------------------
    yx67                           b1234                          3456        154                    148.5                  3.704                                   1.4355
    yx67                           c3412                          4532        119                    120                    -0.833                                  1.4355
    */
    
    
    

    Best Regards,

    Rachel


    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.

    • Proposed as answer by Ashin_c Friday, June 14, 2019 7:50 AM
    • Marked as answer by Hellothere8028 Monday, June 17, 2019 11:38 AM
    Friday, June 14, 2019 6:31 AM
  • Thanks Lokesh!...This helps...Just wanted to understand as to how to make it dynamic -Like Comparison of a single day over the average of an entire week and the comparison of the minimum over a week versus another week..Any insights will be helpful!..

    Thanks,

    Arun


    Arun

    Friday, June 14, 2019 12:18 PM
  • Thanks Rachel!!..This works..Appreciate your help!!..

    Arun

    Friday, June 14, 2019 12:19 PM
  • Thanks Lokesh!...This helps...Just wanted to understand as to how to make it dynamic -Like Comparison of a single day over the average of an entire week and the comparison of the minimum over a week versus another week..Any insights will be helpful!..

    Thanks,

    Arun


    Arun

    Hi Arun, You can create a stored procedure with parameters and then form the query based on input. You will have to play around with logic. Thanks!
    Friday, June 14, 2019 12:38 PM