May 13, 2013 at 3:10 am
Hello,
I have two tables ProspectLead and LeadfrontierData this two tables and linked by the ID
ProspectLead.ID and LeadFrontierData.LeadID
I need three columns from the LeadFrontierData which is Price, ContractEndDate and PricePlan, all other columns come from the ProspectLead table
LeadfrontierData can have more then 1 row linked to the ID which is pulled from prospectlead
My problem is this when i return the results to a grid i get multiple results for the 1 ID if i join these tables by ID i get multiple records which is correct because the one ID from Prospectlead could exists more than once in LeadFrontierData
How can i only return 1 record from LeadfrontierData
so it wont duplicate in the table? iv tried using a subquery select statement to return the three columns but i get an error
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
This is my SQL Query
Create Procedure [dbo].sp_GetTodaysCallBackCamberley
@UserAbbr varchar(3),
@Responsibility int,
@Location int,
@Department int
As
Begin
declare @TodaysDate date
set @TodaysDate = (SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())))
Select Distinct pl.ID, usr.FirstName +' ' + usr.Surname as Createdby, pl.ID, pl.ProspectGuid, pl.MobileNumber as CTN, pl.Firstname + ' ' + pl.Surname CustomerName,
Convert(varchar(10),pl.CallBackDate, 103) as CallBackDate, pl.CallBackTime as CallBackTime,
(Select lfd.PricePlan as Tariff, lfd.Price, Convert(varchar(10),lfd.ContractEndDate) as ContractEndDate
From LeadFrontierData lfd join ProspectLead pll on lfd.LeadID = pll.ID)
from dbo.ProspectLead pl(nolock)
join dbo.UserAccount USR (nolock) on USR.ShortAbbr = pl.CallBackSetBy
where((@Responsibility = 14) or (pl.CallBackSetBy = @UserAbbr))
and CallBackDate is not null
and CallBackTime is not null
and CallBackDate = @TodaysDate
Order by pl.ID
End
GO
May 13, 2013 at 3:48 am
...
How can i only return 1 record from LeadfrontierData
...
Which 1 record you want to see in result in case if multiple are found for the same ID?
May 13, 2013 at 3:53 am
I need to return all the columns from the ProspectLead (pl) table which are already in the main select statement but i also need to retrieve the additional three columns from LeadfrontierData which are
lfd.PricePlan as Tariff, lfd.Price, Convert(varchar(10),lfd.ContractEndDate) as ContractEndDate
but my problem is LeadFrontierData could have more then 1 record linked to the same ID which is correct but when i display it i should only have one row for the ID 1
May 13, 2013 at 4:07 am
Managed to get it to work
select * from
(
SELECT pl.ID, usr.FirstName +' ' + usr.Surname as Createdby, pl.ProspectGuid, pl.MobileNumber as CTN, pl.Firstname + ' ' + pl.Surname CustomerName,
Convert(varchar(10),pl.CallBackDate, 103) as CallBackDate, pl.CallBackTime as CallBackTime, lfd.PricePlan as Tariff, lfd.Price, Convert(varchar(10),lfd.ContractEndDate) as ContractEndDate
, row_number() over
(
partition by lfd.LeadID
order by lfd.PricePlan, lfd.Price, Convert(varchar(10),lfd.ContractEndDate)
) rownumber
FROM ProspectLead pl
inner join dbo.UserAccount USR (nolock) on USR.ShortAbbr = pl.CallBackSetBy
inner join dbo.LeadFrontierData lfd (nolock) on lfd.LeadID = pl.ID
where((@Responsibility = 14) or (pl.CallBackSetBy = @UserAbbr))
and CallBackDate is not null
and CallBackTime is not null
and CallBackDate = @TodaysDate
) x
where x.rownumber = 1
May 13, 2013 at 4:15 am
another way of doing it without windowed function:
SELECT pl.ID, usr.FirstName +' ' + usr.Surname as Createdby, pl.ProspectGuid, pl.MobileNumber as CTN, pl.Firstname + ' ' + pl.Surname CustomerName,
Convert(varchar(10),pl.CallBackDate, 103) as CallBackDate, pl.CallBackTime as CallBackTime
, lfd.PricePlan as Tariff
, lfd.Price
,Convert(varchar(10),lfd.ContractEndDate) as ContractEndDate
FROM ProspectLead pl
inner join dbo.UserAccount USR (nolock) on USR.ShortAbbr = pl.CallBackSetBy
cross apply (select top 1 l.PricePlan, l.Price, l.ContractEndDate from dbo.LeadFrontierData l where l.LeadID = pl.id
order by l.PricePlan, l.Price, l.ContractEndDate) lfd
where((@Responsibility = 14) or (pl.CallBackSetBy = @UserAbbr))
and CallBackDate is not null
and CallBackTime is not null
and CallBackDate = @TodaysDate
Just curious, is any particular reason why you're using NOLOCK?
May 13, 2013 at 6:20 am
Hi, thanks for the example,
Reason im using nolock is purely because my line manage has said i have to use it, I understand what it does etc iv tried explaining this to her but i always hit a brick wall, so iv given up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply