Average price change over a period of time SQL-CodePudding

Trying to get the average price change for several items. This is my first time using SQL server, coming from some light experience in Oracle SQL and MySQL. The table is set up like this:

ProductID	StartDate	EndDate	StandardCost
707	5/31/11	5/29/12	12.2
707	5/30/12	5/29/13	13
707	5/30/13	null	13.5
708	5/31/11	5/29/12	10
708	5/30/12	5/29/13	11
708	5/30/13	null	12

I would like it to return this:

ProductID	Difference
707	1.3
708	2

with a as
    (
        select 
            productID, standardcost, startDate, ISNULL(endDate, GETDATE())
        from
            production.ProductCostHistory 
        where (productID, endDate) in (select 
                              productID, max(endDate)
                          from 
                              Production.ProductCostHistory
                          group by
                              productID
                         )
    ), 
b as 
    (
        select 
            productID, standardcost, startDate, startDate
        from
            production.ProductCostHistory 
        where (productID, startDate) in (select 
                              productID, min(StartDate)
                          from 
                              Production.ProductCostHistory
                          group by
                              productID
                         )
    )

select 
    a.productID, (a.standardcost - b.standardcost) difference  
from
     a join b
    on a.ProductID = b.ProductID and 
       a.startDate = b.startDate and
       a.EndDate = b.EndDate

The idea is to first get the price with the most recent end date, then subtract the price from the oldest start date, to get the change in cost over that period of time. I understand that the where ... in does not work like in Oracle SQL, but I have not found a way to use exists the way that I need it to work. The averaging will be done in PowerBI, so I'm not worried about doing that in the Query. I would appreciate any help. Thanks.

CodePudding user response：

You can try calculating the Standardcosts corresponding to the first startdate, and latest enddate (for each product) using an analytic function FIRST_VALUE.

select 
    productid,
    max(fv) as value_at_first_startdate,
    max(lv) as value_at_latest_enddate,
    diff = max(lv) - max(fv)
from (
    select 
        productid,
        first_value(standardcost)
            over (partition by productid
                order by startdate) as fv,
        first_value(standardcost)
            over (partition by productid
                order by case when enddate is null then 0 else 1 end
                , enddate desc) as lv
    from cte
) t
group by productid

CodePudding user response：

Assuming your SQL is supposed to return correct result:

with minmax (productId, minStart, maxEnd) as 
( select 
    productID, min(startDate),max(endDate)
  from 
    Production.ProductCostHistory
    group by productID
),
a as
    (
        select 
            p.productID, standardcost, startDate, ISNULL(endDate, GETDATE())
        from production.ProductCostHistory p
        inner join minmax m on m.productId = p.productId AND 
                        m.maxEnd = p.endDate
    ), 
b as 
    (
        select 
            p.productID, standardcost, startdate, startdate
        from production.ProductCostHistory p
        inner join minmax m on m.productId = p.productId AND 
                        m.minStart = p.startDate
    )

select 
    a.productID, (a.standardcost - b.standardcost) difference  
from
     a join b
    on a.ProductID = b.ProductID and 
       a.startDate = b.startDate and
       a.EndDate = b.EndDate