DECLARE @BeginDate Datetime
DECLARE @EndDate Datetime
Set @BeginDate = '06-01-2015'
Set @EndDate = '06-30-2015'

DECLARE @tblData table 
( 
  RECORDTYPE varchar(25),
  AgentName varchar(100), 
  Balance_before numeric(10,2), 
  EscrowAmountApplied numeric(10,2), 
  TotalEscrowAmountApplied numeric(10,2), 
  Balance_After numeric(10,2),
  TransactionDate datetime,
  Address1 varchar(50),
  Address2 varchar(50),
  City varchar(50),
  State varchar(50),
  Zip varchar(50)
 )

SELECT 
 A.*, 
 B.Balance_After 

 FROM

 (SELECT t1.RECORDTYPE, T1.AgentName, T1.Balance_before, t1.EscrowAmountApplied, t1.TotalEscrowAmountApplied, t1.Address1, t1.Address2, t1.City, t1.State, t1.Zip 
  FROM @tblData T1 INNER JOIN 
 -- Take the Beginning Escrow Balance for an agent (Escrow Balance Before) within the date range for an agent
 (SELECT AgentName, TotalEscrowAmountApplied, MIN( TransactionDate) minDT from @tblData GROUP BY AgentName, TotalEscrowAmountApplied) T2
  ON T1.AgentName = T2.AgentName AND T1.TotalEscrowAmountApplied = T2.TotalEscrowAmountApplied AND T1.TransactionDate = T2.minDT ) A
 
 INNER JOIN 
 -- Take the Ending Escrow Balance for an agent (Escrow Balance After) within the date range for an agent
 (SELECT t1.RECORDTYPE, T1.AgentName, T1.Balance_After FROM @tblData T1 INNER JOIN 
 (SELECT AgentName, MAX( TransactionDate) maxDT from @tblData GROUP BY AgentName) T2

 ON T1.AgentName = T2.AgentName AND T1.TransactionDate = T2.maxDT ) B
 ON A.AgentName = B.AgentName

I have the following result with the help from our forum experts here.

I need modify the query to produce the following result set:

Attached is my current SQL query:

Thank you all for your help.

Can't really see what you're trying to change to what but at a guess, a sum?

I am sorry my image is not very clear.

Basically, I would like to do a SUM on TotalEscrowAmountApplied which is -2200.00 and 3000.00 which equates to 800.00.

Sorry for unclear message.

And what is your logic for selecting the Balance_before and EscrowAmountApplied columns?

I think the issue is that you are performing an INNER JOIN across all the data sets.  You shouldn't join your MIN and MAX subsets to the base table on the same join conditions.  If you do, it will only bring data that exists across all three sets of data (i.e. parent, min, and max).  Since the min and max subsets only contain the opposites, the query will return no data.  If by chance the min and max subsets contain the same data (i.e. only a single transaction), then that row is returned.

It looks like what you are trying to do is create three distinct sets of data and combine them into a single row.  If that is the case, your MIN and MAX subsets are fine.  However, you need to filter your base data (i.e. @tblData) to only include distinct rows based on AgentName PRIOR to joining it to the other subsets.  You can call it T0.  Then just INNER JOIN T0 (Base Data) with T1 (MIN Data) and T2 (MAX Data).

You also may want to add your date filters to your subqueries.  You declared them but never used them.

• Edited by J I M B O 9 hours 14 minutes ago

I don't think looks right at all. What if there are ten rows for the same agent? Should you still only have the difference between the first and last amount? And why sum only TotalEscrowAmountApplied and not EscrowAmountApplied?

Could you supply some sample data for your table and the desired result? It would also help if you could explain the business rules for us poor souls who don't knwo what an escrow amount is.