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