SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Select Null Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rashednadeem
Starting Member

5 Posts

Posted - 01/21/2012 :  01:21:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/21/2012 :  07:09:29  Show Profile  Reply with Quote
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
Go to Top of Page

Rashednadeem
Starting Member

5 Posts

Posted - 01/25/2012 :  04:00:06  Show Profile  Reply with Quote
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

Edited by - Rashednadeem on 01/25/2012 04:23:16
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000