subquery help please - 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)

subquery help please

Author

Topic

karla0612
Starting Member

5 Posts

Posted - 2009-07-22 : 16:51:46

If you see in the query one of the columns for the file is the S.social_security_num_str, but I have need the social security of the mainholder. Let me explain the subscriber_identity_num_id have 13 positions (for example 9012345641201) the last two position identifies the type of member (for example mainholder, spouse of child)

I need for every row in the file look for the social security number of the mainholder for that contract and the unique form to identify the mainholder is where the subscriber_identity_num_id first 11 positions are the same.

Explaining.
I have this subscriber_identity_num_id 9012345641201
to identify the mainholder social security I have to look for this subscriber_identity_num_id 9012345641200 which is the same number but ending in 00.

SELECT
CASE WHEN S.subscriber_dep_num_str_id = '00' THEN
'0'
WHEN S.subscriber_dep_num_str_id BETWEEN '20' AND '49' THEN
'1'
Else
'2'
END,
S.social_security_num_str,
dbo.fn_MA_Pad_String(CL.first_name_str,25),
dbo.fn_MA_Pad_String(CL.second_name_str,1),
dbo.fn_MA_Pad_String(CL.first_last_name_str,25),
dbo.fn_MA_Pad_String(ltrim(rtrim(CA.address1_str)) + ' ' + ltrim(CA.address2_str),60),
dbo.fn_MA_Pad_String(CA.city_str,15),
dbo.fn_MA_Pad_String(CA.state_str_id,4),
dbo.fn_MA_Pad_String(CA.zip_code1_str_id,6),
dbo.fn_MA_Pad_String(S.policy_type_num_id,2),
dbo.fn_MA_Pad_String(CT.contract_type_str_id,3),
convert(varchar(10), S.premium_effective_date, 111),
convert(varchar(10), S.birth_date, 111),
CASE WHEN S.coverages_str like '%C%'THEN
'1'
Else
'0'
END AS Medicare
FROM Subscriber S
INNER JOIN Client CL ON
S.subscriber_identity_num_id = CL.subscriber_identity_num_id
INNER JOIN DependentCode DC ON
DC.subscriber_dep_num_str_id = S.dep_rel_code_str_id
INNER JOIN Contract C ON
C.contract_client_num_str_id = S.contract_client_num_str_id
INNER JOIN ContractType CT ON
C.contract_type_str_id = CT.contract_type_str_id
INNER JOIN SubscriberAddress SA ON
S.subscriber_identity_num_id = SA.subscriber_identity_num_id
INNER JOIN ClientAddress CA ON
CA.sequence_num = SA.sequence_num
WHERE S.group_code_str = '115090' AND S.status_code_str_id = '1' AND CA.address_location_num_id = '1'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-22 : 17:22:57

Answer given here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/070624f8-aa22-4cee-9d60-efdfb066b41e

N 56°04'39.26"
E 12°55'05.63"