SQLTeam.com | Weblogs | Forums

I am trying to remove the duplicate xm.ID lines

#1

Hi There,

The below script creates more than one line for every xm.ID. I want only the first line per xm.ID.
DISTINCT does not work because the whole Select line is not DISTINCT.

Please help.
Thank you.

Declare @StartDate VarChar (10), @EndDate VarChar (10)
Set @StartDate = '2021/10/01'
Set @EndDate = '2021/12/01'

Select
xm.ID As 'Ex Code'
, xu.U_Name As 'Employee Name'
, Isnull(CONVERT(varchar, xh.H_DateTime, 103),'') As 'History Date'
, xh.H_Description As 'History Description'
, xc.C_ClientName
From Matter xm With (nolock)
Inner Join Client xc With (nolock) On xc.ID = xm.M_ClientID
Inner Join History xh With (nolock) On xh.H_IDX = xm.M_IDX
Inner Join Users xu With (nolock) On xu.ID = xh.H_UserID
Where xh.H_HA_ID = 15
And Isnull(CONVERT(varchar, xh.H_DateTime, 111),'') > @StartDate And Isnull(CONVERT(varchar, xh.H_DateTime, 111),'') < @EndDate
Order by xm.ID, xh.H_DateTime desc

#2

Declare @EndDate date
Declare @StartDate date

Set @StartDate = '20210110' --use YYYYMMDD, always!
Set @EndDate = '20210112'

Select
xm.ID As 'Ex Code'
, xu.U_Name As 'Employee Name'
, Isnull(CONVERT(varchar, xh.H_DateTime, 103),'') As 'History Date'
, xh.H_Description As 'History Description'
, xc.C_ClientName
From Matter xm With (nolock)
Inner Join Client xc With (nolock) On xc.ID = xm.M_ClientID
Cross Join ( --<<--
Select Top (1) xh.*
From History xh With (nolock)
Where xh.H_IDX = xm.M_IDX
and xh.H_HA_ID = 15
and xh.H_DateTime >= @StartDate
and xh.H_DateTime < Dateadd(Day, 1, @EndDate)
Order By xh.H_DateTime desc
) As xh
Inner Join Users xu With (nolock) On xu.ID = xh.H_UserID
Order by xm.ID --, xh.H_DateTime desc

#3

Hi Scott,

Thanks for the help. This looks like we are on the right track but I am now getting an error:
The multi-part identifier "xm.M_IDX" could not be bound.

It is the correct field in Matter file but it can't find it and I don't know how to fix it.

The script after your changes is now:

Declare @EndDate Date
Declare @StartDate Date

Set @StartDate = '20210101' --use YYYYMMDD, always!
Set @EndDate = '20210131'

Select
xm.ID As 'Ex Code'
, xu.U_Name As 'Employee Name'
, Isnull(CONVERT(varchar, xh.H_DateTime, 103),'') As 'History Date'
, xh.H_Description As 'History Description'
, xc.C_ClientName
-- , xm.M_IDX
From Matter xm With (nolock)
Inner Join Client xc With (nolock) On xc.ID = xm.M_ClientID
Cross Join
(
Select Top (1) xh.*
From History xh With (nolock)
Where xh.H_IDX = xm.M_IDX
and xh.H_HA_ID = 15
and xh.H_DateTime >= @StartDate
and xh.H_DateTime < Dateadd(Day, 1, @EndDate)
Order By xh.H_DateTime desc
) As xh
Inner Join Users xu With (nolock) On xu.ID = xh.H_UserID
Order by xm.ID

Please help.

#4

If you get an error telling you that the “The multi-part identifier could not be bound.”, it usually means that you're prefixing one or more columns with either a table that isn't included in your query, or an alias that you haven't actually assigned to a table .

#5

xm.M_IDX

this means xm ( table Matter ) does not have column M_IDX

#6

Hi There,

I commented it out of the select statement but it definitely does have xm.M_IDX

#7

Hi There,

I did not get any errors when I changed the Cross Join to Cross Apply.
I don't know if the data is correct but it was able to run as per the below script.

Select
xm.ID As 'Ex Code'
, xu.U_Name As 'Employee Name'
, Isnull(CONVERT(varchar, xh.H_DateTime, 103),'') As 'History Date'
, xh.H_Description As 'History Description'
, xc.C_ClientName
From ExcaliburV4_WH.dbo.Matter xm With (nolock)
Inner Join Client xc With (nolock) On xc.ID = xm.M_ClientID
Cross Apply
(
Select Top (1) xh.*
From History xh With (nolock)
Where xh.H_IDX = xm.M_IDX
and xh.H_HA_ID = 15
and xh.H_DateTime >= @StartDate
and xh.H_DateTime < Dateadd(Day, 1, @EndDate)
Order By xh.H_DateTime desc
) As xh
Inner Join Users xu With (nolock) On xu.ID = xh.H_UserID
Order by xm.ID

#8

Yes, that is correct. Sorry, I should have used CROSS APPLY to begin with rather than CROSS JOIN.

#9

Thanks all for your help, it now works perfectly.

What I have found is that the "Cross Apply" and the "Outer Apply" give me back exactly the same data but it seems as if the "Outer Apply" is faster.

Thanks again.