Inserting a new row with totals and Maxdate 1

[TiltingCode]

The below query snippet works. It uses an existing temporary table and saves the maximum date for each claim in another table variable. Then, using another temporary all the TotalPaymentAmount values are summed from a table where the date is greater than the maximum date for each claim (from the table variable). Finally, we use joins to get the desired results.

I tried several ways to do all this without having to create those two extra table variables but I kept getting bad results. Can anyone please tell me a way to make this more efficient?

Thanks for your help!

DECLARE @PendingMedTable Table (
	ClaimID		VARCHAR(20)   NULL, 
	MaxDate		DATE, 
	PendMed		MONEY)

INSERT INTO @PendingMedTable
Select ClaimID, Max(DateRequested), NULL
From @SelectedClientTable
Group By ClaimID
Order By ClaimID

DECLARE @PendingMedTable2 Table (
	ClaimID		VARCHAR(20)   NULL, 
	MaxDate		DATE, 
	PendMed		MONEY)

INSERT INTO @PendingMedTable2
Select P.ClaimID, PMT.MaxDate, Sum(P.TotalPaymentAmount) AS 'Total'
From @PendingMedTable PMT
JOIN Payments P
ON P.Claimid = PMT.ClaimID
Where (P.PaidDate > PMT.MaxDate OR P.PaidDate IS NULL)
Group By P.ClaimID, PMT.MaxDate

INSERT INTO @SelectedClientTable(ClientID, 																 ClientName, 																 ClaimID, 																 ClaimantName, 																 DA_InjuryDate, 																 InitialReport, 																 PendingMed,																 DateRequested)
Select SCT.ClientID, 
	SCT.ClientName,
	SCT.ClaimID, 
	SCT.ClaimantName,
	SCT.DA_InjuryDate,
	SCT.InitialReport,
	PMT.PendMed,
	PMT.MaxDate

From @SelectedClientTable SCT
JOIN Payments P
ON P.ClaimID = SCT.ClaimID
JOIN @PendingMedTable2 PMT
ON PMT.ClaimID = P.ClaimID
AND PMT.ClaimID = SCT.ClaimID
Group BY SCT.ClientID, 
	SCT.ClientName,
	SCT.ClaimID, 
	SCT.ClaimantName,
	SCT.DA_InjuryDate,
	SCT.InitialReport,
	PMT.PendMed,
	PMT.MaxDate

 

[TamarGranor]

Looks like you could do this with derived tables, that is, by putting your preparatory queries into the FROM clause:

INSERT INTO @SelectedClientTable(ClientID, ClientName, ClaimID,ClaimantName,DA_InjuryDate,InitialReport,PendingMed,DateRequested)
Select SCT.ClientID, 
	SCT.ClientName,
	SCT.ClaimID, 
	SCT.ClaimantName,
	SCT.DA_InjuryDate,
	SCT.InitialReport,
	PMT2.PendMed,
	PMT2.MaxDate
From @SelectedClientTable SCT
JOIN Payments P
ON P.ClaimID = SCT.ClaimID
JOIN (Select P.ClaimID, PMT.MaxDate, Sum(P.TotalPaymentAmount) AS 'Total'
From (Select ClaimID, Max(DateRequested), NULL
From @SelectedClientTable
Group By ClaimID
Order By ClaimID) PMT
JOIN Payments P
ON P.Claimid = PMT.ClaimID
Where (P.PaidDate > PMT.MaxDate OR P.PaidDate IS NULL)
Group By P.ClaimID, PMT.MaxDate) PMT2
ON PMT2.ClaimID = P.ClaimID
AND PMT2.ClaimID = SCT.ClaimID
Group BY SCT.ClientID, 
	SCT.ClientName,
	SCT.ClaimID, 
	SCT.ClaimantName,
	SCT.DA_InjuryDate,
	SCT.InitialReport,
	PMT2.PendMed,
	PMT2.MaxDate

Not sure I got all the aliases right because you used the same alias in two different queries, but this should give you the idea.

Tamar

 

[TiltingCode]

Thanks TamarGranor, your code didn't solve my problem but you turned me on to derived tables and I agree with you that my query can use them. I am currently looking further into incorporating them because of your suggestion. Thanks again.