How to obtain previous charge

[dnguyen77]

All,
I have the following two tables:
billConDetail(Bill Consultant Detail) and billInvMaster (Bill Invoice Master)
Bill Consultant Detail have the following fields:
(InvoiceID, Project,Task,SubTask,Vendor,FromDate,ToDate)
Bill Consultant Detail have the following fields:
(InvoiceID,Project,Task,SubTask,Vendor,Amount)
I have want to produce a report that have the following columns:
Input Parameters(Project,FromDate,ToDate)
Task
[Vendor] [Current Charge] [Previous
Charge] [Charges to Date]
---------------------------------------------------------------------------------------------------------------------------
To get the Current charge, and Charges to Date was straight forward for me. However, my mind stop working when I try to obtain the previous Charge column.
Can someone please help me out with this.
Input parameters:
Project = 1, FromDate = 05/01/2006, ToDate = 05/31/2006
---------------------------------------------------------------------------------------------------------------------------

Given the above input, and the bellow Sample Data; My question is how do I know that my previous amount was 200, and not 300.
Also from my sample data bellow I only have one task, but in the real table I can have multiple tasks for a given project. For a selected period, if we happen didn’t invoice the client for a certain task(s), because there was no activity for that task(s) I need to also able to display previous charge as 0.
---------------------------------------------------------------------------------------------------------------------------

Sample Data:
Invoice Project Task SubTask FromDate ToDate
---------------------------------------------------------------------------------------------------------------------------

[AAAB1] [1] [1] [0] [05/01/2006] [05/31/2006]
[AACD2] [1] [1] [0] [04/01/2006] [04/30/2006]
[AAMZ3] [1] [1] [0] [03/01/2006] [03/31/2006]
---------------------------------------------------------------------------------------------------------------------------

Invoice Project Task SubTask Vendor Amount
---------------------------------------------------------------------------------------------------------------------------

[AAAB1] [1] [1] [0] [A] [100]
[AACD2] [1] [1] [0] [A] [200]
[AAMZ3] [1] [1] [0] [A] [300]

[iburyak]

Try using this structure:

Select top 1 …. List of columns here
From table1
Join table2 on table1.Invoice = table2.Invoice
Where ToDate < @FromeDate 
Order by ToDate Asc

Good Luck.

[dnguyen77]

Hi,

Yes your suggested method would work, if I only have one task in a record. In my case a project can have one or more tasks & sub tasks.

Thanks,
AN

[iburyak]

It would be a good idea to provide more Sample data and desired result.

In a meanwhile try this:

Select top 1 .....
from billConDetail a
join billInvMaster b on a.invoice = b.invoice and a.Project = b.Project and a.Task = b.Task and a.SubTask = b.SubTask
where a.Project = @Project 
ToDate < @FromeDate 
Order by ToDate Asc

Good Luck.