jeffwest21
IS-IT--Management
I have the following code in a query
It is supposed to bring back just one entry for each row in the database, however, it's fine if there is only something in the Call_Number_int column, otherwise it brings back multiple values, i have been hitting my head against a wall trying to work out what is wrong, any chance someone can spot it for me.
It reads the data from a series of linked SQL tables.
'Clever boy...'
Code:
SELECT Distinct p.ID,p.Forename, p.Surname,p.[Event Code],convert(varchar(10),[Event Date],103) AS Event_Date,convert(varchar(10),c.[Contact Date],103) as Contact_Date,
convert(varchar(10),c.[Callback date],103) AS Callback_Date,p.[Date of Birth],p.Future_Calling,p.Being_Updated,
CASE When Future_Calling IS NULL then Call_Number_Int
when Future_Calling = 'int' Then Call_Number_Int
when Future_Calling = '2A' then Call_Number_2A
when Future_Calling = '2B' then Call_Number_2B
when Future_Calling = '3A' then Call_Number_3A when Future_Calling = '3B' then Call_Number_3B end AS Calls,cc.Category AS [Careers Category],
CASE When Future_Calling IS NULL then O.outcome
when Future_Calling = 'int' Then O.outcome
when (Future_Calling = '2A' and Call_number_2A is NULL) then O.outcome
when (Future_Calling = '2A' and Call_number_2A is not NULL) then OFP.outcome
when (Future_Calling = '2B' and Call_number_2B is NULL) then O.outcome
when (Future_Calling = '2B' and Call_number_2B IS NOT NULL) then OFP.outcome
when (Future_Calling = '3A' and Call_number_3A is NULL) then O.outcome
when (Future_Calling = '3A' and Call_number_3A is not NULL) then OFP.outcome
when (Future_Calling = '3B' and Call_number_3B is NULL) then O.outcome
when (Future_Calling = '3B' and Call_number_3B is not NULL) then OFP.outcome
end as [Outcome]
FROM dbo.contact c,dbo.Prospect p,dbo.Career_Category cc,Outcome o,Outcome_FUP OFP
where p.id = c.[prospect id]
and [Closed Date] is null
and p.[Career Category] = cc.Id
and c.[ID] IN ( SELECT max(d.ID)
FROM dbo.contact d GROUP BY d.[Prospect ID])
and C.Outcome = case when Future_Calling = 'int' Then o.id else ofp.id end
order by surname
It is supposed to bring back just one entry for each row in the database, however, it's fine if there is only something in the Call_Number_int column, otherwise it brings back multiple values, i have been hitting my head against a wall trying to work out what is wrong, any chance someone can spot it for me.
It reads the data from a series of linked SQL tables.
'Clever boy...'