Getting a Distinct Record Using Max() On a Date and a Character field 1

[LeonelSanchezJr]

The following query does not give me a distinct DocNum
when the dates are all the same; plus I need to add the Time field (currently commented out) to my MAX function.

NOTE: The dDate field is a true DATETIME field, but the
cTime field is a character field such as 09:46:05.


Insert Into #Tmp3
Select Distinct A.cDocNum,
A.cStat
From Stat A
Inner Join (Select Distinct Stat.cDocNum,
Max(Stat.dDate + Stat.cTime) As MaxDateTime
From Stat Group By Stat.cDocNum) As B
On A.cDocNum = B.cDocNum
And A.dDate = B.MaxDateTime

 

[rajeevnandanmishra]

Hi there,
Sorry for very late reply.
Try the following solution
---------------------------
Insert Into #Tmp3
Select cDocNum,cStat,max(convert(char(10),dDate,102)+cTime)
From Stat
GROUP BY cDocNum,cStat
---------------------------