Home » SQL & PL/SQL » SQL & PL/SQL » SQL to compare current and previous record values and display them (merged) (Oracle 9i release 2, 32-bit, Windows 2003 server.)
SQL to compare current and previous record values and display them (merged) [message #429016] Sun, 01 November 2009 01:58 Go to next message
yasar2002
Messages: 4
Registered: November 2009
Location: Dubai
Junior Member

Hi All,

I need to compare current record having item with its unit cost with previous record's unit cost of the same item. There are different items in a table but in a date range I need to see how much price variance has occurred in unit cost of each item. If there is variance, then display the current item.

ItemNum ||PONumber ||UnitCost || DateEntered
75006292 ||50041433 || 9.37 || 02/06/2009 08:07:06
75006292 ||50041913 ||9.37 || 14/06/2009 09:28:43
75006292 ||50043048 ||9.37 || 07/07/2009 15:12:38
75006292 ||50043531 ||9.37 || 16/07/2009 07:45:32
75006292 ||50043590 ||9.37 || 20/07/2009 06:11:38
75006292 ||50044311 ||8.8 || 03/08/2009 12:41:31

75006292 ||50044712 ||8.8 || 11/08/2009 13:45:53
75006292 ||50044850 ||8.8 || 16/08/2009 07:02:26
75006292 ||50045170 ||8.8 || 23/08/2009 08:13:37

I have been struggling with SQL statement for this.

Please help.

Re: SQL to compare current and previous record values and display them (merged) [message #429022 is a reply to message #429016] Sun, 01 November 2009 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG function.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

If you want an example of query, post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: SQL to compare current and previous record values and display them (merged) [message #429023 is a reply to message #429022] Sun, 01 November 2009 01:20 Go to previous message
yasar2002
Messages: 4
Registered: November 2009
Location: Dubai
Junior Member

Thanks appreciate your guidance.
Previous Topic: storing images in oracle 10g
Next Topic: different on upate in (oracle and sqlserver)
Goto Forum:
  


Current Time: Sun Sep 25 07:51:14 CDT 2016

Total time taken to generate the page: 0.11309 seconds