SQL Query Help

setaside · Post by **setaside** » Wed Jan 05, 2005 4:45 pm

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.

Kasey Chang · Post by **Kasey Chang** » Wed Jan 05, 2005 8:59 pm

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.

ChrisGwinn · Post by **ChrisGwinn** » Wed Jan 05, 2005 11:01 pm

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?

Koz · Post by **Koz** » Thu Jan 06, 2005 3:00 am

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.

setaside · Post by **setaside** » Thu Jan 06, 2005 12:58 pm

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