By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

SELECT and compare last two cost entries

code green
Expert 100+
P: 1,726
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-
Expand|Select|Wrap|Line Numbers
  1. SELECT lastprice.part, lastprice.cost, lastprice.last_updated, 
  2. thisprice.cost, thisprice.last_updated 
  3. FROM trade  lastprice
  4. JOIN trade  thisprice ON thisprice.part = lastprice.part 
  5. WHERE lastprice.last_updated < thisprice.last_updated
  6. AND lastprice.cost != thisprice.cost
  7. 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
Expand|Select|Wrap|Line Numbers
  1. AND lastprice.cost < thisprice.cost-(thisprice.cost*0.05)
Any suggestions?
Apr 30 '08 #1
Share this Question
Share on Google+
12 Replies


ck9663
Expert 2.5K+
P: 2,878
will this work?

Expand|Select|Wrap|Line Numbers
  1.       select lastprice.part, lastprice.cost, lastprice.last_updated, thisprice_cost
  2.          (select top 1  thisprice.cost
  3.             from trade thisprice where lastprice.part = thisprice.part
  4.                and lastprice.last_updated > thisprice.last_updated)
  5.       from trade lastprice
  6.  
-- CK
Apr 30 '08 #2

code green
Expert 100+
P: 1,726
Thanks for your reply.
I am afraid this is invalid syntax.
Had another go using your idea
Expand|Select|Wrap|Line Numbers
  1. SELECT lastprice.part, lastprice.cost,lastprice.cost  
  2. FROM trade lastprice
  3. WHERE lastprice.last_updated <
  4.    (SELECT TOP 1 thisprice.last_updated
  5. FROM trade thisprice 
  6. WHERE lastprice.part = thisprice.part
  7.   AND lastprice.last_updated < thisprice.last_updated
  8.   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.
May 1 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Could you post some sample data?

-- CK
May 1 '08 #4

code green
Expert 100+
P: 1,726
Could you post some sample data?
Sure. This is a snippet of the table 'trade'
Expand|Select|Wrap|Line Numbers
  1. part         cost        last_updated
  2. 3M01417     7.02    12/10/200709:45:30
  3. 3M06263     15.24    28/03/200810:21:31
  4. 3M06263     14.04    15/08/200611:13:52
  5. 3M15706      8.89    12/03/200809:56:43
  6. 3M15706      9.05    09/10/200709:15:24
  7. 3M15706     10.55    15/08/200611:13:52
  8. 3M15706     10.55    11/08/200611:12:08
  9. 3M18645      1.88    04/03/200810:37:03
  10. 3M18645      2.16    15/08/200611:13:52
  11. 3M18645      2.16    11/08/200611:12:08
  12. 3M18647      4.65    24/04/200808:33:07
  13. 3M18647      5.62    15/08/200611:13:52
  14. 3M20190      3.77    14/07/200614:15:43
I am thinking about using variables to solve this.
But no success so far
May 2 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Option 1:
There's always cursor.

Option2:
Expand|Select|Wrap|Line Numbers
  1. Try this:
  2.  
  3. declare @x table (part varchar(7), cost  money, last_updated smalldatetime)
  4.  
  5. set nocount on
  6.  
  7. insert into @x values ('3M01417', 7.02, '10/12/2007 09:45:30')
  8. insert into @x values ('3M06263',     15.24   ,'03/28/2008 10:21:31')
  9. insert into @x values ('3M06263',     14.04   ,'08/15/2006 11:13:52')
  10. insert into @x values ('3M15706',      8.89   ,'03/12/2008 09:56:43')
  11. insert into @x values ('3M15706',      9.05   ,'10/09/2007 09:15:24')
  12. insert into @x values ('3M15706',     10.55   ,'08/15/2006 11:13:52')
  13. insert into @x values ('3M15706',     10.55   ,'08/11/2006 11:12:08')
  14. insert into @x values ('3M18645',      1.88   ,'03/04/2008 10:37:03')
  15. insert into @x values ('3M18645',      2.16   ,'08/15/2006 11:13:52')
  16. insert into @x values ('3M18645',      2.16   ,'08/11/2006 11:12:08')
  17. insert into @x values ('3M18647',      4.65   ,'04/24/2008 08:33:07')
  18. insert into @x values ('3M18647',      5.62   ,'08/15/2006 11:13:52')
  19. insert into @x values ('3M20190',      3.77   ,'07/14/2006 14:15:43')
  20.  
  21.  
  22.  
  23. select summary.part, 
  24.    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,
  25.    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,
  26.    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,    
  27.    last_update_date = detail1.last_updated   
  28. from
  29. (select part, count(*) as cnt, max(last_updated) as last_updated
  30. from @x firstcost group by part) summary
  31.    inner join @x detail1 on summary.last_updated = detail1.last_updated
  32.  
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
May 2 '08 #6

code green
Expert 100+
P: 1,726
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.
Expand|Select|Wrap|Line Numbers
  1. WHERE summary.last_updated > '2008-01-01'
  2. AND summary.part LIKE '3M%'
  3. 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
Expand|Select|Wrap|Line Numbers
  1. 3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
  2. 3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
  3. 3M06263       14.0400    2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
  4. 3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
  5. 3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
  6. 3M15706       9.0500    2007-10-09 09:15:24.000        9.0500    2008-03-12 09:56:43.000
  7. 3M18645       2.1600    2006-08-15 11:13:52.000        2.1600    2008-03-04 10:37:03.000
  8. 3M18645       2.1600    2006-08-15 11:13:52.000        2.1600    2008-03-04 10:37:03.000
  9. 3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
  10. 3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
  11. 3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
  12. 3M18646       2.9200    2006-08-15 11:13:52.000        2.9200    2008-01-22 09:54:23.000
  13. 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
May 6 '08 #7

code green
Expert 100+
P: 1,726
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
Expand|Select|Wrap|Line Numbers
  1. SELECT C.part,newer,latest,older,previous FROM(
  2.   SELECT A.part,latest,MAX(B.previous)previous FROM(
  3.     SELECT part,MAX(last_updated) latest FROM trade
  4.       GROUP BY part) AS A
  5.     INNER JOIN(
  6.       SELECT part,last_updated previous FROM trade) AS B
  7.     ON (A.part = B.part AND A.latest > B.previous)
  8.   GROUP BY A.part,A.latest) AS C
  9. INNER JOIN(
  10.   SELECT cost newer, last_updated,part
  11.   FROM trade) AS D
  12. ON (C.part = D.part AND C.latest = D.last_updated)
  13. INNER JOIN(
  14.   SELECT cost older, last_updated,part
  15.   FROM trade) AS E
  16. ON (C.part = E.part AND C.previous = E.last_updated)
  17. WHERE(older < newer-(newer*0.05)
  18. OR older > newer+(newer*0.05))
Still some testing to do but data looks good
May 6 '08 #8

ck9663
Expert 2.5K+
P: 2,878
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
May 7 '08 #9

code green
Expert 100+
P: 1,726
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
Expand|Select|Wrap|Line Numbers
  1. 3M06263     15.24   28/03/200810:21:31
  2. 3M06263     14.04   15/08/200611:13:52
But the following was returned
Expand|Select|Wrap|Line Numbers
  1. 3M06263       14.0400   2006-08-15 11:13:52.000 14.0400 2008-03-28 10:21:31.000
  2. 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
May 7 '08 #10

ck9663
Expert 2.5K+
P: 2,878
I might've missed something, my bad.

Is your query working now?

-- CK
May 7 '08 #11

code green
Expert 100+
P: 1,726
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT C.part,newer,CONVERT(VARCHAR,latest,103) latest,MAX(older)older, 
  2.     CONVERT(VARCHAR,previous,103) previous
  3.     FROM(
  4.         SELECT B.part,MAX(A.latest)latest,MAX(B.previous) previous,older,newer 
  5.         FROM(    
  6.             SELECT part,MAX(last_updated) latest
  7.             FROM trade
  8.             GROUP BY part
  9.         ) AS A
  10.         INNER JOIN
  11.         (
  12.             SELECT part,last_updated previous
  13.                     FROM trade
  14.         ) AS B
  15.         ON (A.part = B.part AND latest > previous)
  16.         INNER JOIN
  17.         (    SELECT cost newer,last_updated,part
  18.             FROM trade
  19.         ) AS D
  20.         ON (A.part = D.part AND latest = D.last_updated)
  21.         INNER JOIN
  22.         (    SELECT MAX(cost) older,last_updated,part
  23.             FROM trade
  24.             GROUP BY last_updated,part
  25.         ) AS E
  26.         ON (A.part = E.part AND (older < newer-(newer*0.05)
  27.         OR older > newer+(newer*0.05)))
  28.         GROUP BY B.part,newer,older
  29.     )AS C
  30.     WHERE latest > (GETDATE()-180)
  31.     GROUP BY C.part,newer,latest,previous
  32.     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
May 8 '08 #12

ck9663
Expert 2.5K+
P: 2,878
Tidy is not the issue, performance is. You did great man.

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

-- CK
May 8 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.