Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query question

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-10-02 : 14:46:25

I have a query like below

SELECT CH.CLAIM_NUM, CH.UNIT_NUM,

CH.PERSONAL_NUM, CH.INSURANCE_NUM_P,

FROM ra.CLAIM_HEADER CH

WHERE (CH.CLAIM_STATUS IN ('M', 'C'))
AND (CH.CLAIM_POSTING_DATE BETWEEN @startdate AND @enddate)

-----------
and have a table called Unit ( join by unit_num with Claim_haeder table)
unit_num region_num
2058 1
2060 1
2713 1
2563 1
2726 1
2728 1
2755 1
2756 1
2754 1
2727 1
2720 1
2715 2
2716 2
2719 2
2780 2
2779 2
2778 2
2777 2
2776 2
2775 2
2774 2
2749 2
2748 2
2745 2
2739 2
2738 2
2737 2
1933 3
2751 3
2752 3
2750 3
1827 3
2714 3
2622 3
1822 3
1593 3

---------------------------------

I want to be able to run the query for either the whole region( region 1,2, and 3) or only one region ( i.e region 1 - under region 1 there are 2058,2060 ,2713, 2563, 2726 ,2728 ,2755,
2756 ,2754 , 2727 ,2720 unit) or just one unit ( i.e 1593) by passing the parameters( @region_num and @unit_num).

I was trying to do somethin like belwo ,but i am not quite sure how I can do this in one sql statment.

SELECT CH.CLAIM_NUM, CH.UNIT_NUM, CH.PERSONAL_NUM, CH.INSURANCE_NUM_P,

FROM ra.CLAIM_HEADER CH
join ra.unit u on i.unit_num = u.unit_num
WHERE (CH.CLAIM_STATUS IN ('M', 'C'))
and ( u.region_num = 2region_num or @region_num = 9999 ) or ?????
AND (CH.CLAIM_POSTING_DATE BETWEEN @startdate AND @enddate)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-03 : 00:49:56

SELECT CH.CLAIM_NUM, CH.UNIT_NUM, CH.PERSONAL_NUM, CH.INSURANCE_NUM_P,
FROM ra.CLAIM_HEADER CH
join ra.unit u on i.unit_num = u.unit_num
WHERE (CH.CLAIM_STATUS IN ('M', 'C'))
and ((case when @region_num <> 9999 then
           case when u.region_num = @region_num then 1 else 0 end
          else 1 
     end) or
     (case when @unit_num <> 9999 then
           case when u.unit_num = @unit_num then 1 else 0 end
          else 1 
     end))
AND (CH.CLAIM_POSTING_DATE BETWEEN @startdate AND @enddate)

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources