SQL Query (Amendments Logic)

Hi Guys,

Anyone here knows how to create a query that will produce something like the tables below.

I have an Item A that costs 10 but in a few days the Item A was changed to 15. I need to know how much I need to add as an additional fee or if there's a need for reimbursement.

Code:

---

[U]Date[U]    TypeOfWork    UnitPrice  AccountsPayable
1/10/2009    Item A                    10                    10
1/11/2009    Item A                    15                    5

---

But what if the same Item was changed again for the third time.

Code:

---

[U]Date[U]    TypeOfWork    UnitPrice  AccountsPayable
1/10/2008    Item A                    10                    10
1/11/2008    Item A                    15                    5
1/20/2008    Item A                    13                    -2

---

I need to show everything in the query to determine how many times Item A changed in price. Negative values under AccountsPayable are the amounts subjected for reimbursement and the positive values are those I need to pay as an additional fee.

If I am going to code it in excel the formula is something like this;

Code:

---

[Col4, Row1] =[Col3,Row1]
[Col4, Row2] =[Col3,Row2]-[Col3,Row1]
[Col4, Row3] =[Col3,Row3]-[Col3,Row2]

---

Based on SQL Server 2005.

I have created a table using your example above. I have also created a PK (counter) field to help with working out the previous entry.

SQL for table below

Code:

---

CREATE TABLE [dbo].[tblCosts](
        [pk] [int] IDENTITY(1,1) NOT NULL,
        [Date] [datetime] NULL,
        [TypeOfWork] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [UnitPrice] [money] NULL,
        [AccountPayable] [int] NULL,
        [Difference] [int] NULL,
 CONSTRAINT [PK_tblCosts] PRIMARY KEY CLUSTERED
(
        [pk] ASC
) ON [PRIMARY]
) ON [PRIMARY]

---

Data stored within the created table
Attachment 68365

SQL to select what your after

Code:

---

SELECT t.[Date],
        t.TypeOfWork,
        t.UnitPrice,
        t.AccountPayable,
        ISNULL(t.AccountPayable - (SELECT t2.AccountPayable
                                        FROM dbo.tblCosts t2
                                        WHERE t2.pk = (t.pk - 1)
                                        AND t2.TypeOfWork = t.TypeOfWork), t.AccountPayable) AS 'Diff'
FROM dbo.tblCosts t

---

Results of that SELECT Statement
Attachment 68366

Cool... just as a side note however, the Difference column isn't necessary.... unless you want to store the result. The only problem I can see with storing it though, is knowing if it needs to be updated. But it'll work just fine w/o the field in the table in the first place.

-tg

Yeah i put that in the table but realised i didnt need it when it came to the select. Like you mentioned it might be useful it you want to store it. :thumb: