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]
2 Attachment(s)
Re: SQL Query (Amendments Logic)
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
Re: SQL Query (Amendments Logic)
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
Re: SQL Query (Amendments Logic)
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: