Challenging Query for a Newb

[gspeedtech]

I need to write a query that will get the value of a previous records and add it to the current record.

In detail:
I have a table (Table1) that contains PolicyNum, RecordID, and SequenceID

PolicyNum RecordID SequenceID
123456 456 0
123456 456 0
123456 789 1
123456 789 1
123456 123 2
123456 123 2
I have another table (Table2) with RecordID, CompanyID, Total

RecordID CompanyID Total
456 4321 500
789 4321 700
456 8765 600
789 8765 800
123 4321 900
123 8765 1000

I created a query on a inner join of the two tables on RecordID to get this.

PolicyID RecordID CompanyID SequenceID Total
12345 456 4321 0 500
12345 456 8765 0 600
12345 789 4321 1 700
12345 789 8765 1 800
12345 123 4321 2 900
12345 123 8765 2 1000

Now the big question:

If all I have is a RecordID, how can I get the Total's of the Record's with the previous SequenceID

ie

I have RecordID 123

I need Total's from the records with SequenceID = (SequenceID) -1 (700, 800)

Then I need to add the previous amount to the current amounts (900, 1000).

RecordID CompanyID Total
123 4321 1600
123 8765 1800

Any suggestions will be helpful.

Thanks

[darkagn]

There are a couple of ways to approach this kind of problem but I prefer to use a subquery in this case. Your query will be the subquery for the larger query, and we will select totals from that based on the SequenceID.

I suspect your query looks something like this:

SELECT A.PolicyID, A.RecordID, A.SequenceID, B.CompanyID, B.Total
FROM Table1 A
INNER JOIN Table2 B ON A.RecordID = B.RecordID

Here is how we can use that as a subquery:

SELECT C.RecordID, C.CompanyID, C.Total + D.Total AS GrandTotal
FROM
(
  -- subquery to get the first total
  SELECT A.PolicyID, A.RecordID, A.SequenceID, B.CompanyID, B.Total
  FROM Table1 A
  INNER JOIN Table2 B ON A.RecordID = B.RecordID
) C
INNER JOIN
(
  -- subquery to get the second total
  SELECT A.PolicyID, A.RecordID, A.SequenceID, B.CompanyID, B.Total
  FROM Table1 A
  INNER JOIN Table2 B ON A.RecordID = B.RecordID
) D ON C.RecordID = D.RecordID AND C.SequenceID = D.SequenceID - 1

This should at least give you something to go on. Good luck :)

[gspeedtech]

Awesome this is exactly what I needed.

Works great!

[gspeedtech]

Ok, how can I tweak this query to add all previous totals

IE

If I have RecordID 123

PolicyID CompanyID Total
12345 4321 2100
12345 8765 2400

If I have RecordID 789

PolicyID CompanyID Total
12345 4321 1200
12345 8765 1400

[darkagn]

All totals is much simpler. MSSQL provides a SUM function which can be used to sum groups of totals like so:

SELECT A.PolicyID, B.CompanyID, SUM(B.Total) AS GrandTotal
FROM Table1 A
INNER JOIN Table2 B ON A.RecordID = B.RecordID
GROUP BY PolicyID, CompanyID

Note that this will give you totals by policy id and company id, sequence number is not referenced but I don't think it needs to be?

[gspeedtech]

Once again, you have given me exactly the answer I needed.

With your help, I have created a query that adds the results of SubQuery1 with the SUM(SubQuery2) to produce a GrandTotal.

It works when SubQuery2 is succesful. But if SubQuery2 is not successfull (ie there are no records with a sequenceID = sequenceID -1) the entire query offers no results.

How can i get the results of SubQuery1 even if SubQuery2 offers no results?

I hate to keep going to the well, but I hope my acquired knowledge will be helpful to someone else in the future.

Thanks again for the Sage advice!

[gspeedtech]

This is my query:

Declare @RecordID

Set RecordID = 4321

SELECT C.RecordID, C.CompanyID, C.Total + Sum(D.Total) AS GrandTotal
FROM
(
  ---SubQuery1 to get policy info where RecordID = @RecordID
  SELECT A.PolicyID, A.RecordID, A.SequenceID, B.CompanyID, B.Total
  FROM Table1 A
  INNER JOIN Table2 B ON A.RecordID = B.RecordID
  Where A.RecordID = @RecordID
) C
INNER JOIN
(
  ---SubQuery2 to get Totals from policies where PolicyID = PolicyID of @RecordID and 
  ---SequenceID less than SequenceID of @RecordID
  SELECT B.CompanyID, B.Total
  FROM Table1 A
  INNER JOIN Table2 B ON A.RecordID = B.RecordID
  Where A.RecordID <> @RecordID and Policy ID = (Select PolicyID from Table2 where RecordId = @recordID)and  
  SequenceID < (Select SequenceID from Table2 where RecordID = @RecordID)
) D 
ON C.CompanyID = D.CompanyID 
Group By C.RecordID, C.CompanyID, C.Total

Works great if SequenceID of @RecordID is > 0 but if SequenceID is Null or = 0 SubQuery2 doesnt return any results, thus the entire query doesnt return any results.

I am trying to figure out how to set SubQuery2 Total = 0 if query offers no results.

Unless there is better approach.

[darkagn]

Use a left outer join instead of an inner join. Inner joins only return results if both sides return values, left join works so long as the first part returns results, a right join works so long as the second part returns results.

[gspeedtech]

As you suggested, I used a Left Outer Join.
It does produce results , but only the results of SubQuery1.
I did not mention I have added another total to C.Total + D.Total from a udf.

I needed to add in the OriginalTotal if it is not Null or get the OriginalTotal from another field if it was Null. So I created a udf that selects between two other udf's based on if the result was null or not null.

So the actual GrandTotal calculation is dbo.udf_OriginalTotal(@RecordID, C.CompanyID) + C.Total + Sum(D.Total) as GrandTotal.

So, with the Left Outer Join I get results from SubQuery1 only.
Inner Join I get no results.

So, what I did was create another udf to get the results produced by SubQuery2 or Set the total to 0 if Null. And I eliminated SubQuery2.

I now have a select statement for GrandTotal that is:

dbo.udf_OriginalTotal(@RecordID, C.CompanyID) + C.Total + dbo.udf_ChildTotal(@RecordID, C.CompanyID) = GrandTotal

It seems to work ok, although with my limited experience I dont know if there are drawbacks to my approach.

[darkagn]

I think we might be over-complicating things. From your original post I thought you were after just the total of the record plus the one that came before it in sequence. If you are after a sum total of all sequences for the record, you can just use the SUM function as I have written in my second post of this thread. There is no need to use subqueries or other functions, the SUM function will give you totals for each record, and you can exclude specific sequenceID's in your where clause if you need to.

[gspeedtech]

Sorry about the scope creep.
It started out as a requirement to find the previous Total and add it to the current total.

Then it changed to Get all previous Totals and add to current Total.

Now its: Get all previous totals, Get OriginalTotal and add to current Total. With the complication that not all Records have an OriginalTotal, some have a total in another table that = OriginalTotal.

All worked well whenI ran the query you helped me create, until I ran the query on a record with SequenceID = 1. Since there wasnt a record with a SequenceID less than 1, the query offered no results. Thus the entire query offered no results.

To solve this problem, I created a udf to return 0 if subquery2 = Null or no results.

So, the requirement is to Add OriginalTotal, @RecordID.Total and all PreviousTotals.
ie
If @RecordID.SequenceID = 5, I need to Add OriginalTotal, @RecordID.Total and 4 PreviuosTotals.
If @RecordID.SequenceID = 1, I need to Add OriginalTotal and @RecordID.Total

Hopefully it will not get more complicated after testing.