Select Null Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Select Null Records

Author

Topic

Rashednadeem
Starting Member

5 Posts

Posted - 2012-01-21 : 01:21:30

HI
I have a stored Procedure listed below
I want to select records from attendance Table joined on employee if record for some employee is missing then select them as absant it works fine for single date but when i change Where clause to between dates it select only few records that are present in AttendanceRecords table
Please Help me


alter proc att_AttendanceReport
@Date1  datetime =NULL,
@Date2  datetime =NULL
AS
if @Date1 IS NULL
Select @Date1=GETDATE()
If @Date2 IS NULL
Set @Date2=Getdate()
 
Select CONVERT(VARCHAR(11),(isnull(ar.RecordDate,GETDATE())),120) AS 'Record Date',E.EmployeeNumber,e.EmployeeName,ar.Arrival,ar.Departure, s.StatusName from Employees e 
left outer join EmployeeAttendanceRecords ar on ar.employeeid=e.EmployeeID
inner join AttendanceStatus s on isnull(ar.AttendanceStatusID,2)=s.StatusID
where  RecordDate between @Date1 AND @Date2 OR RecordDate IS NULL

Rashed

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-21 : 07:09:29

Try if this works for you?

SELECT CONVERT(VARCHAR(11), (ISNULL(ar.RecordDate, GETDATE())), 120) AS 
       'Record Date',
       E.EmployeeNumber,
       e.EmployeeName,
       ar.Arrival,
       ar.Departure,
       ISNULL(s.StatusName,'Absent') AS StatusName
FROM   Employees e
       LEFT OUTER JOIN EmployeeAttendanceRecords ar
            ON  ar.employeeid = e.EmployeeID
             AND ( RecordDate BETWEEN @Date1 AND @Date2
				OR  RecordDate IS NULL )
       LEFT JOIN AttendanceStatus s
            ON  ISNULL(ar.AttendanceStatusID, 2) = s.StatusID

Rashednadeem
Starting Member

5 Posts

Posted - 2012-01-25 : 04:00:06

Thanks But this Returns Absant Always and donot show the Arrival time or departure Time
Thanks again as it solves my problem for on date but please help me for Date range
Rashed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources