SQL query referencing 2 tables in SQL Server and ASP
Posted on 2007-07-22
I have the followeing query which works fine, but there is another bit of data i need that is in another table. The other bit of data is a field called [Payment Method Code] and it is in the claim_header table, it relates to this query in that the [Claim No_] are the same in both tables, this code just tells the user how they were paid.
I am not sure how to achieve this because the [Claim No_] does not get returned until the query is run, therefore how would I get the data from the other table??
I do not know if i could use the results from this query to then query the other table either.
The results will be displayed as follows...
Claim No. | Treatment Date | Claimant Name | Payment Method | Amount Claimed
Any ideas, I can explain further if needed? Cheers
SELECT SUM(Amounttobepaid) AS Claim_amounts, [Amounttobepaid], [Claimant No_], [Claim No_], [Start_Date], [Benefit Code]
WHERE [Claimant No_] = MMColParam2 AND [Benefit Code] = MMColParam AND [Start_Date] >= MMColParam3 AND [Start_Date] <= MMColParam4 Group By [Amounttobepaid], [Claimant No_],[Claim No_], [Start_Date], [Benefit Code]
ORDER BY Start_Date ASC
the parameter values are as follows
MMColParam = Request.QueryString("claim_type")
MMColParam2 = Request.QueryString("member_id")
MMColParam3 = session("dtstartLastYear")
MMColParam4 = session("dtEndLastYear")