Missing Logic for Last Review date

[former_member188586]

hi experts

  from bellow query i'm not getting emp last hike date

SELECT T0.[empID], T0.[firstName],T0.[salary] ,T1.[remarks],

(SELECT max  (T1.[Date] ) FROM OHEM T0 full JOIN HEM3 T1 ON T0.empID = T1.empID )

  FROM OHEM T0 full JOIN HEM3 T1 ON T0.empID = T1.empID WHERE T0.[status] is null  AND  T0.[termReason] IS NULL

please help me

Thanks & Regards

  Andakondaramudu

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT T0.[empID], T0.[firstName],T0.[salary] ,T1.[remarks],

(SELECT max  (T1.[Date] ) FROM OHEM T0 left  JOIN HEM3 T1 ON T0.empID = T1.empID )

  FROM OHEM T0 left JOIN HEM3 T1 ON T0.empID = T1.empID WHERE T0.[status] is null  AND  T0.[termReason] IS NULL

Thanks & Regards,

Nagarajan

[former_member188586 · Accepted solution]

hi Nagarajan sir

  i want to display last hike date for each employ  ..please check bellow  image

+

Thanks & Regards

   AKR

[kothandaraman_nagarajan · Accepted solution]

Hi,

I don't have such data in my DB. Please wait for other member's reply.

Thanks & Regards,

Nagarajan

[former_member188586 · Accepted solution]

ok sir

[silambarasan_rajendran · Accepted solution]

Hi,

Try this,

SELECT T0.[empID], T0.[firstName],T0.[salary],Max(convert(nvarchar(max),T1.[remarks])) Remarks,MAX(T1.[Date]) HighDate
FROM OHEM T0 LEFT OUTER JOIN HEM3 T1 ON T0.empID = T1.empID
WHERE (T0.[status] IS NULL)  AND  (T0.[termReason] IS NULL)
group by T0.[empID], T0.[firstName],T0.[salary]

Thanks & Regards,

silambu

[former_member188586 · Accepted solution]

hi silambu

Thanks for your Replay but some entry are missing ( some emp are missing)

[former_member188586 · Accepted solution]

hi silambu

thanks for u r replay ,i did miner changer then it will work fine

SELECT T0.[empID], T0.[firstName],T0.[salary],Max(convert(nvarchar(max),T1.[remarks])) Remarks,MAX(T1.[Date]) HighDate

    FROM OHEM T0 left outer  JOIN HEM3 T1 ON T0.empID = T1.empID

    WHERE T0.[termDate] IS NULL

    group by T0.[empID], T0.[firstName],T0.[salary]

Thanks &regards

   Andakondaramudu