How to get one result record in stored procedure

Transact-SQL
1

Hi all.
I created stored procedure that calculate absence and lateness time for according employee

SELECT
e.FName + ' ' + e.LName EmpName
, datepart(year,CallDate) as call_year
, datepart(month, CallDate) as call_month
, datename(MM, CallDate) as namemonth
, convert(varchar(10),cast(@DateFrom as datetime),101) DateFrom
, convert(varchar(10),cast(@DateTo as datetime),101) DateTo
, CASE WHEN a.Code <> '00' THEN
CASE WHEN AbsHours = 0 AND AbsMin = 0 THEN ''
ELSE (sum(AbsHours) * 60) + sum(AbsMin) END END SumAbsMin
, CASE WHEN a.Code = '00' THEN
CASE WHEN AbsHours = 0 AND AbsMin = 0 THEN ''
ELSE (sum(AbsHours) * 60) + sum(AbsMin) END END SumLateMins
FROM dbo.CallInLog l
LEFT OUTER JOIN [dbo].[v_EmployeeList] e
ON l.Employee_Id = e.Employee_Id
LEFT OUTER JOIN dbo.v_AbsenceCode a
ON l.AbsenceCode_Id = a.AbsenceCode_Id
WHERE convert(varchar(10),CallDate,111) between convert(varchar(10),@DateFrom,111) and convert(varchar(10),@DateTo,111)
and e.FName + ' ' + e.LName = @EmpName
GROUP BY e.FName, e.LName, datepart(year,CallDate), datepart(month, CallDate), datename(MM, CallDate), a.Code, AbsHours, AbsMin
ORDER BY datepart(year,CallDate), datepart(month, CallDate)

as the result I got two records for same employee.
How to modify that procedure to get only one record?

Thanks.

2

You can use anything
You want to filter to one row

Top 1

Max

Min

AVG

3

Hi harishhgg1. Thanks for replay.
When I filtered by MAX(CallDate) I'm loosing record that display lateness time. My purpose to get absence and lateness time in one record.

Thanks.

4

Hi

You can

Join table alias ABC
To same table alias Def

And join on
row number 1 of ABC
To row number 2 of DEF

Get both absence lateness in 1 row

5

Hi

Another idea

Is to use
Lead or Lag

Screenshot_2019-08-01-21-31-54-34
Screenshot_2019-08-01-21-31-54-34.png720×1520 114 KB

6

Hi

Another idea is
min() over()
max() over()

7

You provided no sample data so I can't test, but this should at least be very close:

SELECT
e.FName + ' ' + e.LName EmpName
, datepart(year,CallDate) as call_year
, datepart(month, CallDate) as call_month
, datename(MM, CallDate) as namemonth
, convert(varchar(10),cast(@DateFrom as datetime),101) DateFrom
, convert(varchar(10),cast(@DateTo as datetime),101) DateTo
, sum(CASE WHEN a.Code <> '00' THEN AbsHours * 60 + AbsMin else 0 end) AS SumAbsMin
, sum(CASE WHEN a.Code = '00' THEN AbsHours * 60 + AbsMin else 0 end) AS SumLateMins
FROM dbo.CallInLog l
LEFT OUTER JOIN [dbo].[v_EmployeeList] e
ON l.Employee_Id = e.Employee_Id
LEFT OUTER JOIN dbo.v_AbsenceCode a
ON l.AbsenceCode_Id = a.AbsenceCode_Id
WHERE CallDate >= CAST(@DateFrom AS date) AND 
      CallDate < DATEADD(DAY, 1, CAST(@DateTo AS date))
and e.FName + ' ' + e.LName = @EmpName
GROUP BY e.FName, e.LName, datepart(year,CallDate), datepart(month, CallDate), datename(MM, CallDate)
ORDER BY datepart(year,CallDate), datepart(month, CallDate)