Page 1 of 1
SQL Query Help
Posted: Wed Jan 05, 2005 4:45 pm
by setaside
Ack! My first time asking for programming help here. I've got an SQL query that I'm dynamically building in VB.NET and I'm having trouble in 1 spot. Here's the query:
Code: Select all
SELECT DISTINCT
A.state_num,
A.section_code,
A.sequence_num,
B.claim_status,
A.incurred_date,
A.policy_date,
Year(A.incurred_date) as incYear,
Year(A.policy_date) as polYear,
B.line_item,
left(B.line_item,2) as Coverage
FROM
claim_header A INNER JOIN claim_detail B ON
A.state_num = B.state_num AND
A.section_code = B.section_code AND
A.sequence_num = B.sequence_num
WHERE
report_date <= '12/31/2004' AND
((B.claim_status <> 'C' AND Left(B.line_item,2) IN ('11', '12', '19', '80', '41', '42', '71', '72', '73', '51', '52', '82')) OR
(A.Claim_status <> 'C' AND ((A.section_code = 'WC' AND B.line_item = '990') OR (A.section_code = 'XW' AND B.line_item = '990'))))
ORDER BY
A.state_num,
A.section_code,
Coverage, incYear,
A.sequence_num
(A.section_code = 'XW' AND B.line_item = '990')
This is the actual part I'm having trouble with. Some background. All WC and XW records should have a 990 line_item. Some WC and XW records will have a 991 line_item. I don't want the 991 at all. Here's the problem, some rare XW records will not have a 990 line_item. In these cases, I want the 991. I can't use an OR statement there because it will return both 990 and 991 for those XW records that have them.
I've looked at both the CASE and COALESCE statements but can't really make heads or tails of them. Anybody here have any ideas on what I can do here? Thanks.
Posted: Wed Jan 05, 2005 8:59 pm
by Kasey Chang
It sounds like you just need to define your criteria better.
Basically, you're saying you want WC or XW with 990, OR XW with 991 ONLY.
The best way is to UNION two separate queries together, since what you're asking is kinda "either/or'.
So just construct a separate query with the XW/991, and union the result with the first query, and you got your answer.
Posted: Wed Jan 05, 2005 11:01 pm
by ChrisGwinn
Do an EXISTS subquery. I think Casey's idea will also work.
Dare I ask why all the line items are numbers stored as strings?
Posted: Thu Jan 06, 2005 3:00 am
by Koz
Using 'A' and 'B' for tables makes the programmer inside me cringe.
Also, be careful when unioning things. Sometimes the logic can get tricky, especially when unioning two large subsets together, and stuff can be added in or left out unintentionally.
Posted: Thu Jan 06, 2005 12:58 pm
by setaside
Well I got it working using a NOT EXISTS statement (Thanks ChrisGwinn). I really didn't want to use a UNION because of the sheer size of the query (you guys only got about half of it). And Koz ... I fixed the aliases just for you.
Thanks for the help.
Here is the final query in all its un-optomized, inefficient glory:
Code: Select all
SELECT DISTINCT
claimHeader.state_num,
claimHeader.section_code,
claimHeader.sequence_num,
claimDetail.claim_status,
claimHeader.incurred_date,
claimHeader.policy_date,
Year(claimHeader.incurred_date) as incYear,
Year(claimHeader.policy_date) as polYear,
claimDetail.line_item,
left(claimDetail.line_item,2) as Coverage,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = claimHeader.section_code AND payments.sequence_num = claimHeader.sequence_num AND
payments.line_item = claimDetail.line_item AND check_date <= '12/31/2004' AND payments.coverage_type <> 'LAE') AS lossPayments,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = 'WC' AND payments.sequence_num = claimHeader.sequence_num AND
check_date <= '12/31/2004' AND payments.coverage_type <> 'LAE') AS lossWCPayments,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = 'XW' AND payments.sequence_num = claimHeader.sequence_num AND
check_date <= '12/31/2004' AND payments.coverage_type <> 'LAE') AS lossXWPayments,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = claimHeader.section_code AND payments.sequence_num = claimHeader.sequence_num AND
payments.line_item = claimDetail.line_item AND check_date <= '12/31/2004' AND payments.coverage_type = 'LAE') AS laePayments,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = 'WC' AND payments.sequence_num = claimHeader.sequence_num AND
check_date <= '12/31/2004' AND payments.coverage_type = 'LAE') AS laeWCPayments,
(SELECT Sum(amount) FROM payments WHERE payments.state_num = claimHeader.state_num AND payments.section_code = 'XW' AND payments.sequence_num = claimHeader.sequence_num AND
check_date <= '12/31/2004' AND payments.coverage_type = 'LAE') AS laeXWPayments,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = claimHeader.section_code AND reserve_change.sequence_num = claimHeader.sequence_num AND
reserve_change.line_item = claimDetail.line_item AND trans_datetime <= '12/31/2004' AND reserve_change.coverage_type <> 'LAE') AS lossReserves,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = 'WC' AND reserve_change.sequence_num = claimHeader.sequence_num AND
trans_datetime <= '12/31/2004' AND reserve_change.coverage_type <> 'LAE') AS lossWCReserves,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = 'XW' AND reserve_change.sequence_num = claimHeader.sequence_num AND
trans_datetime <= '12/31/2004' AND reserve_change.coverage_type <> 'LAE') AS lossXWReserves,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = claimHeader.section_code AND reserve_change.sequence_num = claimHeader.sequence_num AND
reserve_change.line_item = claimDetail.line_item AND trans_datetime <= '12/31/2004' AND reserve_change.coverage_type = 'LAE') AS laeReserves,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = 'WC' AND reserve_change.sequence_num = claimHeader.sequence_num AND
trans_datetime <= '12/31/2004' AND reserve_change.coverage_type = 'LAE') AS laeWCReserves,
(SELECT Sum(trans_amt) FROM reserve_change WHERE reserve_change.state_num = claimHeader.state_num AND reserve_change.section_code = 'XW' AND reserve_change.sequence_num = claimHeader.sequence_num AND
trans_datetime <= '12/31/2004' AND reserve_change.coverage_type = 'LAE') AS laeXWReserves
FROM
claim_header claimHeader INNER JOIN claim_detail claimDetail ON
claimHeader.state_num = claimDetail.state_num AND
claimHeader.section_code = claimDetail.section_code AND
claimHeader.sequence_num = claimDetail.sequence_num
WHERE
claimDetail.report_date <= '12/31/2004' AND
((claimDetail.claim_status <> 'C' AND Left(claimDetail.line_item,2) IN ('11', '12', '19', '80', '41', '42', '71', '72', '73', '51', '52', '82')) OR
(claimHeader.claim_status <> 'C' AND ((claimHeader.section_code = 'WC' AND claimDetail.line_item = '990') OR (claimHeader.section_code = 'XW' AND (claimDetail.line_item = '990' OR
(claimDetail.line_item = '991' AND NOT EXISTS (
SELECT CD.line_item FROM
claim_header CH INNER JOIN claim_detail CD ON
CH.state_num = CD.state_num AND
CH.section_code = CD.section_code AND
CH.sequence_num = CD.sequence_num
WHERE
CD.state_num = claimHeader.state_num AND
CD.section_code = claimHeader.section_code AND
CD.sequence_num = claimHeader.sequence_num AND
CD.report_date <= '12/31/2004' AND
CH.Claim_status <> 'C' AND
(CH.section_code = 'XW' AND CD.line_item = '990')
)))))))
ORDER BY
claimHeader.state_num,
claimHeader.section_code,
Coverage,
incYear,
claimHeader.sequence_num