SQLTeam.com | Weblogs | Forums

How to get one result record in stored procedure

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.

You can use anything
You want to filter to one row

Top 1

Max

Min

AVG

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.

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

Hi

Another idea

Is to use
Lead or Lag

Hi

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

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)