Problem with the stored procedure

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

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with the stored procedure

Author

Topic

karagikoy
Starting Member

4 Posts

Posted - 2006-03-30 : 01:08:06

CREATE Procedure EmployeeAttendanceReport

@DateFrom nvarchar(15),@DateTo nvarchar(15),@IdNumber nvarchar(15)
As

-- First create a temporary table
DECLARE @temp TABLE
(
dt datetime
)
DECLARE @cDate datetime
SET @cDate = @DateFrom
WHILE @cDate <= @DateTo
BEGIN
INSERT INTO @temp VALUES (@cDate)
SET @cDate = DATEADD(day, 1, @cDate)
END

SELECT Employee.em_number,Employee.L_name,Employee.F_name,Employee.Mi,Employee.e_dept,
convert(nvarchar(5),Log_file.shift_from,108) as ShiftFrom,convert(nvarchar(5),Log_file.shift_to,108) as ShiftTo,
-- Log_file.in_logdate, Log_file.lg_datein,convert(nvarchar(5) ,
t.dt, Log_file.lg_datein,convert(nvarchar(5) ,
Log_file.em_timein,108) as TimeIn,Log_file.lg_dateout,
convert(nvarchar(5), Log_file.em_timeout,108)as TimeOut,Log_file.note
FROM Employee INNER JOIN Log_File on Employee.em_number=Log_file.em_Number
RIGHT JOIN @temp t ON t.dt = Log_File.In_LogDate
-- WHERE Log_file.in_logdate>=@DateFrom AND Log_file.in_logdate<=@DateTo
WHERE
Employee.em_number=@IdNumber
-- ORDER BY Employee.em_number, Log_file.in_logdate ASC
ORDER BY Employee.em_number, t.dt ASC
GO

The output is suppose to be the input date From up to date To but it seems that if the temp table did not find any records on Log file (cmd. RIGHT JOIN @temp t ON t.dt=Log_File.In_LogDate) it will just display the data tha matches the condition.

ex. 01/01/2005 - 01/05/2005

dt in_logdate

01/03/2005 01/03/2005

01/04/2005 01/04/2005

01/05/2005 01/05/2005

the correct must be:

dt in_logdate

01/01/2005 null

01/02/2005 null

01/03/2005 01/03/2005

01/04/2005 01/04/2005

01/05/2005 01/05/2005

whats wrong with the code or what additional code do I have to add.Please Help!

karagikoy
Starting Member

4 Posts

Posted - 2006-03-30 : 01:32:29

Ok to be more clear

CREATE Procedure EmployeeAttendanceReport

@DateFrom nvarchar(15),@DateTo nvarchar(15),@IdNumber nvarchar(15)
As

-- First create a temporary table
DECLARE @temp TABLE
(
dt datetime
)
DECLARE @cDate datetime
SET @cDate = @DateFrom
WHILE @cDate <= @DateTo
BEGIN
INSERT INTO @temp VALUES (@cDate)
SET @cDate = DATEADD(day, 1, @cDate)
END

SELECT dt FROM @temp
GO

If I will run this on the Query anlyzerL
@DateFrom=01/01/2005
@DateTo=01/05/2005

it will produce the result:
dt
01/01/2005
01/02/2005
01/03/2005
01/04/2005
01/05/2005

By joining the temporary table the code will be like this
CREATE Procedure EmployeeAttendanceReport

@DateFrom nvarchar(15),@DateTo nvarchar(15),@IdNumber nvarchar(15)
As

-- First create a temporary table
DECLARE @temp TABLE
(
dt datetime
)
DECLARE @cDate datetime
SET @cDate = @DateFrom
WHILE @cDate <= @DateTo
BEGIN
INSERT INTO @temp VALUES (@cDate)
SET @cDate = DATEADD(day, 1, @cDate)
END

SELECT Employee.em_number,Employee.L_name,Employee.F_name,Employee.Mi,Employee.e_dept,
convert(nvarchar(5),Log_file.shift_from,108) as ShiftFrom,convert(nvarchar(5),Log_file.shift_to,108) as ShiftTo,
t.dt, Log_file.lg_datein,convert(nvarchar(5) ,
Log_file.em_timein,108) as TimeIn,Log_file.lg_dateout,
convert(nvarchar(5), Log_file.em_timeout,108)as TimeOut,Log_file.note
FROM Employee INNER JOIN Log_File on Employee.em_number=Log_file.em_Number
RIGHT OUTER JOIN @temp t ON t.dt= Log_File.In_LogDate
WHERE
Employee.em_number=@IdNumber
-- ORDER BY Employee.em_number, Log_file.in_logdate ASC
ORDER BY Employee.em_number, t.dt ASC
GO

The problem is it seems the joining it(RIGHT OUTER JOIN @temp t ON t.dt= Log_File.In_LogDate) reflect the data. If no data found in In_Logdate or in Log_file table the data on the temporary table will also not be shown.
[B]This is the example output[B]

if I input

@DateFrom=01/01/2005
@DateTo=01/05/2005
@em_number=1

the result will be:

dt in_logdate
01/01/2005 01/01/2005
01/03/2005 01/03/2005
01/05/2005 01/05/2005

see that dt date value is reflected by in_logdate

what I want to show is like this:

dt in_logdate
01/01/2005 01/01/2005
01/02/2005 null
01/03/2005 01/03/2005
01/04/2005 null
01/05/2005 01/05/2005

see that even if the in_logdate is null the dt value still display the date. How can I do that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-30 : 01:41:39

can you post some sample data ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

karagikoy
Starting Member

4 Posts

Posted - 2006-03-30 : 02:01:08

If you will notice dt varied on lg_datein 01/01/2005 to 01/04/2005 was not been displayed because theres no such data as like that on lg_datein.What I want is to display the complete set of date in dteven log_datein is null

karagikoy
Starting Member

4 Posts

Posted - 2006-03-30 : 03:02:15

Using this command the output will be

but after I right join this table using this code

I got this following output

Value on the first example is not the same with the value after joining the table the data from 01/01/2005 to 01/04/2005 was gone.What I want is to display that data even if the lg_datein is null. If you want to clear something please dont hesitate to tell.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources