multiple records returned - 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 2005 Forums

Transact-SQL (2005)

multiple records returned

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-08-25 : 12:14:28

I am receiving multiple records from a query for each record and can't figure out why.

cancel_code is coming back as NULL for some of the records returned and as 'NS' for the same record, so it is coming back as two seperate records.

SELECT CASE a.coverage
WHEN 'BI' THEN 1
when 'CL' THEN 3
END AS coverage,
a.driver_class,
CASE
WHEN a.Driver_Class LIKE '[34]0[01234]' THEN '99200'
WHEN a.Driver_Class IN ('100', '101', '102') THEN '99250'
WHEN a.Driver_Class LIKE '[1234]1[456]' THEN '99600'
WHEN c.Business_Use = 'Y' AND a.Driver_Class NOT IN ('100', '101', '102', '300', '301', '302', '303', '304', '400', '401', '402', '403', '404') THEN '99300'
ELSE '99100'
END AS rate_zip,
CASE
WHEN d.cancel_code IN ('CR', 'UD', 'UI', 'UL', 'UV', 'UW', 'RW') THEN 1
ELSE 0
END AS insurer_vehicles,
CASE
WHEN d.cancel_code IN ('AD', 'AP', 'AR', 'FN', 'NA', 'ND', 'NF', 'NI', 'NP', 'NS') THEN 1
ELSE 0
END AS nonpay_vehicles,
d.cancel_code as [cancel_code],
CASE
WHEN d.cancel_code IN ('II', 'IR') THEN 1
WHEN d.cancel_code IS NULL THEN 1
WHEN d.cancel_code LIKE '% %' THEN 1
ELSE 0
END AS insured_vehicles,
CASE
WHEN a.coverage IN ('BI', 'CL') THEN written_premium
ELSE 0
END AS Written,
CASE
WHEN a.coverage IN ('FE') THEN written_premium
ELSE 0
END AS Fees,
CASE
WHEN a.accounting_period >= @begin_period AND a.accounting_period <= @end_period THEN 1
ELSE 0
END AS [Current],
a.policy_base as [policy_base],
a.policy_suffix as [policy_suffix],
a.endorsement as [endorsement]
INTO #Texas
FROM History AS a
LEFT JOIN Vehicle AS b
ON b.policy_base = a.policy_base
AND b.policy_suffix = a.policy_suffix
AND b.vehicle_number = a.vehicle_number
AND (b.current_flag = 'Y' or b.endorse_status = 'D')
LEFT JOIN driver AS c
ON c.policy_base = a.policy_base
AND c.policy_suffix = a.policy_suffix
AND c.driver_number = b.driver_number
AND (c.current_flag = 'Y' or c.endorse_status = 'D')
LEFT JOIN policy AS d
ON d.policy_base = a.policy_base
AND d.policy_suffix = a.policy_suffix
WHERE a.policy_state = 'TX'
AND a.rate_zip IS NOT NULL
AND a.rate_table >= @start_rate_table
AND a.rate_table <= @end_rate_table

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-25 : 13:11:34

quote:

d.cancel_code as [cancel_code]

Why shouldn't it come back as two different records?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources