SELECT and compare last two cost entries

[code green]

I need to monitor the change in the cost price of products.
The historic cost of every product is stored in a table 'trade'
So by comparing the latest two entries per product based on the field 'last_updated' I can measure the change in cost price.
But I am struggling with the query.
I think I need to JOIN the same table using aliases so-

SELECT lastprice.part, lastprice.cost, lastprice.last_updated, 
thisprice.cost, thisprice.last_updated 
FROM trade  lastprice
JOIN trade  thisprice ON thisprice.part = lastprice.part 
WHERE lastprice.last_updated < thisprice.last_updated
AND lastprice.cost != thisprice.cost
ORDER BY lastprice.part

No records are returned because I am not seperating the latest two dates properly
I am also looking for a shift greater than 5% so hoping this line will work

AND lastprice.cost < thisprice.cost-(thisprice.cost*0.05)

Any suggestions?

[ck9663]

will this work?

      select lastprice.part, lastprice.cost, lastprice.last_updated, thisprice_cost
         (select top 1  thisprice.cost
            from trade thisprice where lastprice.part = thisprice.part
               and lastprice.last_updated > thisprice.last_updated)
      from trade lastprice
 

-- CK

[code green]

Thanks for your reply.
I am afraid this is invalid syntax.
Had another go using your idea

SELECT lastprice.part, lastprice.cost,lastprice.cost  
FROM trade lastprice
WHERE lastprice.last_updated <
   (SELECT TOP 1 thisprice.last_updated
FROM trade thisprice 
WHERE lastprice.part = thisprice.part
  AND lastprice.last_updated < thisprice.last_updated
  AND lastprice.cost < thisprice.cost)

The query runs, and runs and runs then I stop the execution because I get scared.
Even inserting TOP 10 in the outer SELECT makes no difference.
This is a tricky one.

[ck9663]

Could you post some sample data?

-- CK

[code green]

Could you post some sample data?

Sure. This is a snippet of the table 'trade'

part         cost        last_updated
3M01417     7.02    12/10/200709:45:30
3M06263     15.24    28/03/200810:21:31
3M06263     14.04    15/08/200611:13:52
3M15706      8.89    12/03/200809:56:43
3M15706      9.05    09/10/200709:15:24
3M15706     10.55    15/08/200611:13:52
3M15706     10.55    11/08/200611:12:08
3M18645      1.88    04/03/200810:37:03
3M18645      2.16    15/08/200611:13:52
3M18645      2.16    11/08/200611:12:08
3M18647      4.65    24/04/200808:33:07
3M18647      5.62    15/08/200611:13:52
3M20190      3.77    14/07/200614:15:43

I am thinking about using variables to solve this.
But no success so far

[ck9663]

Option 1:
There's always cursor.

Option2:

Try this:
 
declare @x table (part varchar(7), cost  money, last_updated smalldatetime)
 
set nocount on
 
insert into @x values ('3M01417', 7.02, '10/12/2007 09:45:30')
insert into @x values ('3M06263',     15.24   ,'03/28/2008 10:21:31')
insert into @x values ('3M06263',     14.04   ,'08/15/2006 11:13:52')
insert into @x values ('3M15706',      8.89   ,'03/12/2008 09:56:43')
insert into @x values ('3M15706',      9.05   ,'10/09/2007 09:15:24')
insert into @x values ('3M15706',     10.55   ,'08/15/2006 11:13:52')
insert into @x values ('3M15706',     10.55   ,'08/11/2006 11:12:08')
insert into @x values ('3M18645',      1.88   ,'03/04/2008 10:37:03')
insert into @x values ('3M18645',      2.16   ,'08/15/2006 11:13:52')
insert into @x values ('3M18645',      2.16   ,'08/11/2006 11:12:08')
insert into @x values ('3M18647',      4.65   ,'04/24/2008 08:33:07')
insert into @x values ('3M18647',      5.62   ,'08/15/2006 11:13:52')
insert into @x values ('3M20190',      3.77   ,'07/14/2006 14:15:43')
 
 
 
select summary.part, 
   prev_cost = case when cnt = 1 then 0 else (select top 1 cost from @x detail2 where summary.part = detail2.part and summary.last_updated > detail2.last_updated) end,
   prev_update = case when cnt = 1 then NULL else (select top 1 last_updated from @x detail2 where summary.part = detail2.part and summary.last_updated > detail2.last_updated) end,
   last_cost = case when cnt = 1 then detail1.cost else (select top 1 cost from @x detail2 where summary.part = detail2.part and summary.last_updated > detail2.last_updated) end,    
   last_update_date = detail1.last_updated   
from
(select part, count(*) as cnt, max(last_updated) as last_updated
from @x firstcost group by part) summary
   inner join @x detail1 on summary.last_updated = detail1.last_updated
 

You must have an index on PART and LAST_UPDATED.
For the rate of change, either enclosed this entire query as subquery (slow) or follow my pattern.

Also, I changed your date format. You might want to change it back before you test it.

Option 3:
There's got to be someone out there who has a faster way of doing this.

But if you're table is small (less than a million) residing in a decent server, you should be fine.

Happy Coding.

-- CK

[code green]

I replaced your @x with trade (the name of the table) and ran the query,
with the following filters on the end of the query.

WHERE summary.last_updated > '2008-01-01'
AND summary.part LIKE '3M%'
ORDER BY summary.part

This result was returned comparing the same snippet of data I supplied.
It has found every cost entry and date and returned this with the last date.
Both the prev_cost and last_cost are the same

3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
3M18645       2.1600    2006-08-15 11:13:52.000        2.1600    2008-03-04 10:37:03.000
3M18645       2.1600    2006-08-15 11:13:52.000        2.1600    2008-03-04 10:37:03.000
3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
3M18647       5.6200    2006-08-15 11:13:52.000        5.6200    2008-04-24 08:33:07.000

Not quite there i am afraid.

You must have an index on PART and LAST_UPDATED.

part yes, but not for last_updated. I can't really alter this.

But if you're table is small (less than a million)

123,000 today
A colleague has made a couple of attempts written for MySql
I will port them to T-SQLgive them a go, and post the results

[code green]

OK! Seem to have got it.
This uses a combination of sub-queries and JOINs to extract the data four times from the same table for each part.
Getting the latest date entry and price and the previous date entry and price.
The WHERE condition also filters a 5% difference

SELECT C.part,newer,latest,older,previous FROM(
  SELECT A.part,latest,MAX(B.previous)previous FROM(
    SELECT part,MAX(last_updated) latest FROM trade
      GROUP BY part) AS A
    INNER JOIN(
      SELECT part,last_updated previous FROM trade) AS B
    ON (A.part = B.part AND A.latest > B.previous)
  GROUP BY A.part,A.latest) AS C
INNER JOIN(
  SELECT cost newer, last_updated,part
  FROM trade) AS D
ON (C.part = D.part AND C.latest = D.last_updated)
INNER JOIN(
  SELECT cost older, last_updated,part
  FROM trade) AS E
ON (C.part = E.part AND C.previous = E.last_updated)
WHERE(older < newer-(newer*0.05)
OR older > newer+(newer*0.05))

Still some testing to do but data looks good

[ck9663]

The reason why the value is the same, it means there's only one record for that part#.

If you managed to get everything and it's performing the way you need it to, use your code. If performance is the issue, post your final code and we'll try to start from there.

-- CK

[code green]

Thanks for the help ck9663.
You helped to point me in the right direction
Discussing the query you supplied

The reason why the value is the same, it means there's only one record for that part

That doesn't seem to be the case.
take this example

3M06263     15.24   28/03/200810:21:31
3M06263     14.04   15/08/200611:13:52

But the following was returned

3M06263       14.0400   2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
3M06263       14.0400   2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000

This may be simply a field heading in the wrong place but the main problem was
millions of records were being returned from a table of 233000 entries
and 32200 unique parts. I tried to debug but failed

If you managed to get everything and it's performing the way you need it to, use your code. If performance is the issue, post your final code and we'll try to start from there.

My query actually takes only 3-4 seconds. I was very suprised considering all the sub-queries.
I got excited looking at ROW_NUMBER() OVER (PARTITION BY ORDER BY ) and RANK
but realised I am using SQL Server 2000 whereas these are SQL Server 2005 functions

[ck9663]

I might've missed something, my bad.

Is your query working now?

-- CK

[code green]

Actually the code posted does not work, because it still pulls out multiple records.
That is, if there is say 4 cost entries for one part,
The first record shown is what I am after: - the latest price, the date, the previous price and that date.
But this is followed by two more records with the latest date and price with the historic date and price.
Damn!
Worked on the query yesterday afternoon and this morning (BST) and I think I have got it now.

SELECT C.part,newer,CONVERT(VARCHAR,latest,103) latest,MAX(older)older, 
    CONVERT(VARCHAR,previous,103) previous
    FROM(
        SELECT B.part,MAX(A.latest)latest,MAX(B.previous) previous,older,newer 
        FROM(    
            SELECT part,MAX(last_updated) latest
            FROM trade
            GROUP BY part
        ) AS A
        INNER JOIN
        (
            SELECT part,last_updated previous
                    FROM trade
        ) AS B
        ON (A.part = B.part AND latest > previous)
        INNER JOIN
        (    SELECT cost newer,last_updated,part
            FROM trade
        ) AS D
        ON (A.part = D.part AND latest = D.last_updated)
        INNER JOIN
        (    SELECT MAX(cost) older,last_updated,part
            FROM trade
            GROUP BY last_updated,part
        ) AS E
        ON (A.part = E.part AND (older < newer-(newer*0.05)
        OR older > newer+(newer*0.05)))
        GROUP BY B.part,newer,older
    )AS C
    WHERE latest > (GETDATE()-180)
    GROUP BY C.part,newer,latest,previous
    ORDER BY C.part

It takes 22 seconds to return 3755 rows.
I am sure the query could be tidier but I am running with this format for now.
The biggest struggle was mssql refusing to allow columns in the SELECT
clause to remain ouside the GROUP BY when using aggregate functions or GROUP BY.
MySql allows single columns in a GROUP BY regardless of what remains in the SELECT clause.
Making it a powerful alternative to DISTINCT

[ck9663]

Tidy is not the issue, performance is. You did great man.

Thanks for posting your code. It'll help all of us.

-- CK