Question on Serach using joins
-
Hi All,
Here is a problem which I am at present facing please solve it and send as early as possible
I have two tables in my application
Table 1 contains
EmployeeId InDate InTime OutDate OutTime as columns'
And Table2 contains
EmployeeId EditedInDate EditedInTime EditedOutDate EditedOutTime
as its columns
now the problem is i had select the records from two tables depending on the following cases
Case1
If there is record in table 1 and no record in table 2 for a particular employee and for a particular date then I shoul display data from table1
Case2
If there is record in table 1 and also in table 2 for a particular employee and for a particular date then I shoul display data from table2
CASE3
if there is intime table 1 and also in table 2 or out time in table1 and also in table 2 for aparticular employee for a particular date then i should display data in table2 in one word i can say that I should give preference to second table
Please send me queries which i can use in a sp by creating a temporary table in it and executing that so that i will get the all records into tem table
if you haVE Any other idea also encoraged
-
Try this out
SELECT T1.EmployeeId AS EmployeeId,
COALESCE(T2.EditedInDate,T1.InDate) AS InDate,
COALESCE(T2.EditedInTime,T1.InTime) AS InTime,
COALESCE(T2.EditedOutDate,T1.OutDate) AS OutDate,
COALESCE(T2.EditedOutTime,T1.OutTime) AS OutTime
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.EmployeeId = T2.EmployeeId
-
Hi Sriram moorthy
hope you are fine,thanks for the immediate response i had tried the query you sent to me but the problem is if i use taht i will get entire things from the left table only because it was a left join
NOW I am sending my sp to you please look at that and send me the reply
I am also sending
MY table
THE sp is
CREATE PROCEDURE [dbo].[sp_SelfReport]
(
@EmployeeId nvarchar(10)
)
AS
DECLARE @AutoSignInTime varchar(50)
DECLARE @AutoSignOutTime varchar(50)
DECLARE @EditedsignIntime varchar(50)
DECLARE @EditedSignOutTime varchar(50)
DECLARE @AutoSignInDate datetime
DECLARE @EditedSignedInDate datetime
DECLARE @NoOfHoursWorked varchar(50),@InMinutes varchar(50),@InHours varchar(50),@OutHours varchar(50),@OutMinutes varchar(50),@TotalHours varchar(50),@TotalMinutes varchar(50)
DECLARE @ConvertedOutTime datetime
DECLARE @ConvertedInTime datetime
SET @AutoSignInDate=(SELECT MAX(AutoSignInDate) FROM SignInSignOut WHERE EmployeeId=@EmployeeId)
PRINT @AutoSignInDate
SET @EditedSignedInDate=(SELECT MAX(EditedSignedInDate) FROM ModifiedDetails WHERE EmployeeId=@EmployeeId)
PRINT @EditedSignedInDate
IF (@AutoSignInDate>=@EditedSignedInDate OR @EditedSignedInDate IS NULL)
BEGIN
CREATE TABLE #Temp(EmployeeId nvarchar(10),Date varchar(50),SignInTime varchar(50),SignOutTime varchar(50),NoOfHoursWorked varchar(50), Mode varchar(50), SignInComments varchar(500), SignOutComments varchar(500))
--CREATE TABLE #Temp(EmployeeId nvarchar(10),SignInDate varchar(50),SignInTime varchar(50), SignOutDate varchar(50), SignOutTime varchar(50), NoOfHoursWorked varchar(50))
WHILE (@AutoSignInDate >'1 october 2006')
BEGIN
SET @AutoSignInTime=CONVERT(varchar,CAST((SELECT AutoSignInTime FROM SignInSignOut WHERE (AutoSignInDate=@AutoSignInDate OR AutoSignOutDate=@AutoSignInDate) AND EmployeeId=@EmployeeId) AS datetime),108)
SET @AutoSignOutTime=CONVERT(varchar,CAST((SELECT AutoSignOutTime FROM SignInSignOut WHERE (AutoSignInDate=@AutoSignInDate OR AutoSignOutDate=@AutoSignInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
SET @EditedsignIntime=CONVERT(varchar,CAST((SELECT EditedSignedInTime FROM ModifiedDetails WHERE (EditedSignedInDate=@AutoSignInDate OR EditedSignedOutDate=@AutoSignInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
SET @EditedSignOutTime=CONVERT(varchar,CAST((SELECT EditedSignedOutTime FROM ModifiedDetails WHERE (EditedSignedInDate=@AutoSignInDate OR EditedSignedOutDate=@AutoSignInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
IF ((@AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignOutTime,SignOutComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedOutDate,101) AS [Sign Out Date],CONVERT(Varchar,EditedSignedOutTime,101) AS [Sign Out Time],SignOutComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedOutDate=@AutoSignInDate
UPDATE #Temp SET
Mode = 'Modified'
WHERE
EmployeeId = @EmployeeId
AND
Date = @AutoSignInDate
END
ELSE IF @AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignOutTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignOutDate,101) AS [Sign Out Date],CONVERT(Varchar,AutoSignOutTime,101) AS [Sign Out Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignOutDate=@AutoSignInDate
UPDATE #Temp SET
Mode = 'Auto'
WHERE
EmployeeId = @EmployeeId
AND
Date = @AutoSignInDate
END
ELSE IF @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,AutoSignInTime,101) AS [Sign In Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignInDate=@AutoSignInDate
UPDATE #Temp SET
Mode = 'Auto'
WHERE
EmployeeId = @EmployeeId
AND
Date = @AutoSignInDate
END
ELSE IF @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime,signOutTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,AutoSignInTime,101) AS [Sign In Time],CONVERT(Varchar,AutoSignOutTime,101) AS [Sign Out Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignInDate=@AutoSignInDate
SET @ConvertedOutTime=CAST(@AutoSignOutTime as datetime)
SET @ConvertedInTime=CAST(@AutoSignInTime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Auto'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@AutoSignInDate">Date=@AutoSignInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL) OR ( @AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL))
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime,SignInComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,EditedSignedInTime,101) AS [Sign In Time],SignInComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedInDate=@AutoSignInDate
UPDATE #Temp SET
Mode = 'Modified'
WHERE
EmployeeId = @EmployeeId
AND
Date = @AutoSignInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL))
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime,SignOutTime,SignInComments)
SELECT S.EmployeeId,CONVERT(Varchar,M.EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,M.EditedSignedInTime,101) AS [Sign In Time],CONVERT(Varchar,S.AutoSignOutTime,101) AS [Sign Out Time],M.SignInComments FROM SignInSignOut S INNER JOIN ModifiedDetails M ON S.EmployeeId = M.EmployeeId AND S.AutoSignOutDate=M.EditedSignedInDate AND S.EmployeeId=@EmployeeId AND S.AutoSignOutDate=@AutoSignInDate
SET @ConvertedOutTime=CAST(@AutoSignOutTime as datetime)
SET @ConvertedInTime=CAST(@EditedsignIntime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@AutoSignInDate">Date=@AutoSignInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL) OR ( @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime,SignOutTime,SignOutComments)
SELECT S.EmployeeId,CONVERT(Varchar,S.AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,S.AutoSignInTime,101) AS [Sign In Time],CONVERT(Varchar,M.EditedSignedOutTime,101) AS [Sign Out Time],M.SignOutComments FROM SignInSignOut S INNER JOIN ModifiedDetails M ON S.EmployeeId = M.EmployeeId AND S.AutoSignOutDate=M.EditedSignedOutDate AND S.EmployeeId=@EmployeeId AND S.AutoSignOutDate=@AutoSignInDate
SET @ConvertedOutTime=CAST(@EditedSignOutTime as datetime)
SET @ConvertedInTime=CAST(@AutoSignInTime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@AutoSignInDate">Date=@AutoSignInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR(@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR( @AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp(EmployeeId,Date,SignInTime,SignOutTime,SignInComments,SignOutComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,EditedSignedInTime,101) AS [Sign In Time],CONVERT(Varchar,EditedSignedOutTime,101) AS [Sign Out Time],SignInComments,SignOutComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedInDate=@AutoSignInDate
SET @ConvertedOutTime=CAST(@EditedSignOutTime as datetime)
SET @ConvertedInTime=CAST(@EditedsignIntime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@AutoSignInDate">Date=@AutoSignInDate
END
SET @AutoSignInDate=@AutoSignInDate-1
PRINT @AutoSignInDate
IF (@AutoSignInDate = '1 october 2006')
BEGIN
SELECT Date,SignInTime,signOutTime,NoOfHoursWorked,Mode,SignInComments,SignOutComments FROM #Temp WHERE EmployeeId = @EmployeeId
END
END
END
ELSE IF (@AutoSignInDate<@EditedSignedInDate OR @AutoSignInDate IS NULL)
BEGIN
CREATE TABLE #Temp1(EmployeeId nvarchar(10),Date varchar(50),SignInTime varchar(50),SignOutTime varchar(50),NoOfHoursWorked varchar(50), Mode varchar(50), SignInComments varchar(500), SignOutComments varchar(500))
--CREATE TABLE #Temp(EmployeeId nvarchar(10),SignInDate varchar(50),SignInTime varchar(50), SignOutDate varchar(50), SignOutTime varchar(50), NoOfHoursWorked varchar(50))
WHILE (@EditedSignedInDate >'1 october 2006')
BEGIN
SET @AutoSignInTime=CONVERT(varchar,CAST((SELECT AutoSignInTime FROM SignInSignOut WHERE (AutoSignInDate=@EditedSignedInDate OR AutoSignOutDate=@EditedSignedInDate) AND EmployeeId=@EmployeeId) AS datetime),108)
SET @AutoSignOutTime=CONVERT(varchar,CAST((SELECT AutoSignOutTime FROM SignInSignOut WHERE (AutoSignInDate=@EditedSignedInDate OR AutoSignOutDate=@EditedSignedInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
SET @EditedsignIntime=CONVERT(varchar,CAST((SELECT EditedSignedInTime FROM ModifiedDetails WHERE (EditedSignedInDate=@EditedSignedInDate OR EditedSignedOutDate=@EditedSignedInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
SET @EditedSignOutTime=CONVERT(varchar,CAST((SELECT EditedSignedOutTime FROM ModifiedDetails WHERE (EditedSignedInDate=@EditedSignedInDate OR EditedSignedOutDate=@EditedSignedInDate) AND EmployeeId=@EmployeeId)AS datetime),108)
IF ((@AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignOutTime,SignOutComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedOutDate,101) AS [Sign Out Date],CONVERT(Varchar,EditedSignedOutTime,101) AS [Sign Out Time],SignOutComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedOutDate=@EditedSignedInDate
UPDATE #Temp1 SET
Mode = 'Modified'
WHERE
EmployeeId = @EmployeeId
AND
Date = @EditedSignedInDate
END
ELSE IF @AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignOutTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignOutDate,101) AS [Sign Out Date],CONVERT(Varchar,AutoSignOutTime,101) AS [Sign Out Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignOutDate=@EditedSignedInDate
UPDATE #Temp SET
Mode = 'Auto'
WHERE
EmployeeId = @EmployeeId
AND
Date = @EditedSignedInDate
END
ELSE IF @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,AutoSignInTime,101) AS [Sign In Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignInDate=@EditedSignedInDate
UPDATE #Temp1 SET
Mode = 'Auto'
WHERE
EmployeeId = @EmployeeId
AND
Date = @EditedSignedInDate
END
ELSE IF @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NULL
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime,signOutTime)
SELECT EmployeeId,CONVERT(Varchar,AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,AutoSignInTime,101) AS [Sign In Time],CONVERT(Varchar,AutoSignOutTime,101) AS [Sign Out Time] FROM SignInSignOut WHERE EmployeeId=@EmployeeId AND AutoSignInDate=@EditedSignedInDate
SET @ConvertedOutTime=CAST(@AutoSignOutTime as datetime)
SET @ConvertedInTime=CAST(@AutoSignInTime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp1
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Auto'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@EditedSignedInDate">Date=@EditedSignedInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL) OR ( @AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL))
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime,SignInComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,EditedSignedInTime,101) AS [Sign In Time],SignInComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedInDate=@EditedSignedInDate
UPDATE #Temp1 SET
Mode = 'Modified'
WHERE
EmployeeId = @EmployeeId
AND
Date = @EditedSignedInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NULL))
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime,SignOutTime,SignInComments)
SELECT S.EmployeeId,CONVERT(Varchar,M.EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,M.EditedSignedInTime,101) AS [Sign In Time],CONVERT(Varchar,S.AutoSignOutTime,101) AS [Sign Out Time],M.SignInComments FROM SignInSignOut S INNER JOIN ModifiedDetails M ON S.EmployeeId = M.EmployeeId AND S.AutoSignOutDate=M.EditedSignedInDate AND S.EmployeeId=@EmployeeId AND S.AutoSignOutDate=@EditedSignedInDate
SET @ConvertedOutTime=CAST(@AutoSignOutTime as datetime)
SET @ConvertedInTime=CAST(@EditedsignIntime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp1
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@EditedSignedInDate">Date=@EditedSignedInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL) OR ( @AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime,SignOutTime,SignOutComments)
SELECT S.EmployeeId,CONVERT(Varchar,S.AutoSignInDate,101) AS [Sign In Date],CONVERT(Varchar,S.AutoSignInTime,101) AS [Sign In Time],CONVERT(Varchar,M.EditedSignedOutTime,101) AS [Sign Out Time],M.SignOutComments FROM SignInSignOut S INNER JOIN ModifiedDetails M ON S.EmployeeId = M.EmployeeId AND S.AutoSignOutDate=M.EditedSignedOutDate AND S.EmployeeId=@EmployeeId AND S.AutoSignOutDate=@EditedSignedInDate
SET @ConvertedOutTime=CAST(@EditedSignOutTime as datetime)
SET @ConvertedInTime=CAST(@AutoSignInTime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp1
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@EditedSignedInDate">Date=@EditedSignedInDate
END
ELSE IF ((@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR(@AutoSignInTime IS NOT NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR (@AutoSignInTime IS NULL AND @AutoSignOutTime IS NOT NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL) OR( @AutoSignInTime IS NULL AND @AutoSignOutTime IS NULL AND @EditedsignIntime IS NOT NULL AND @EditedSignOutTime IS NOT NULL))
BEGIN
INSERT INTO #Temp1(EmployeeId,Date,SignInTime,SignOutTime,SignInComments,SignOutComments)
SELECT EmployeeId,CONVERT(Varchar,EditedSignedInDate,101) AS [Sign In Date],CONVERT(Varchar,EditedSignedInTime,101) AS [Sign In Time],CONVERT(Varchar,EditedSignedOutTime,101) AS [Sign Out Time],SignInComments,SignOutComments FROM ModifiedDetails WHERE EmployeeId=@EmployeeId AND EditedSignedInDate=@EditedSignedInDate
SET @ConvertedOutTime=CAST(@EditedSignOutTime as datetime)
SET @ConvertedInTime=CAST(@EditedsignIntime AS datetime)
SET @InMinutes=(SELECT DATEPART(MINUTE,@ConvertedInTime))
SET @InHours=(SELECT DATEPART(HOUR,@ConvertedInTime))
SET @OutHours=(SELECT DATEPART(HOUR,@ConvertedOutTime))
SET @OutMinutes=(SELECT DATEPART(MINUTE,@ConvertedOutTime))
SET @TotalHours=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))/60
SET @TotalMinutes=(((@OutHours*60)+@OutMinutes)-((@InHours*60)+@InMinutes))%60
SET @NoOfHoursWorked=SUBSTRING(CONVERT(varchar,CAST((@TotalHours + ':' +@TotalMinutes) AS datetime),108),1,5)
UPDATE #Temp1
SET
NoOfHoursWorked=@NoOfHoursWorked,
Mode = 'Modified'
WHERE
AND
<A href="http://qa.sqlservercentral.com/forums/mailto
ate=@EditedSignedInDate">Date=@EditedSignedInDate
END
SET @EditedSignedInDate=@EditedSignedInDate-1
PRINT @EditedSignedInDate
IF (@EditedSignedInDate = '1 october 2006')
BEGIN
SELECT Date,SignInTime,signOutTime,NoOfHoursWorked,Mode,SignInComments,SignOutComments FROM #Temp1 WHERE EmployeeId = @EmployeeId
END
END
END
GO
my tables are
table1 signinsignout
CREATE TABLE [SignInSignOut] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AutoSignInTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoSignInDate] [datetime] NULL ,
[AutoSignOutTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AutoSignOutDate] [datetime] NULL ,
[HasLoggedOut] [bit] NULL ,
[TotalHoursWorked] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SignInSignOut_1] PRIMARY KEY CLUSTERED
(
[ID]
ON [PRIMARY]) ON [PRIMARY]
GO
and table2
modified details
CREATE TABLE [ModifiedDetails] (
[EditedID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeId] [int] NOT NULL ,
[EditedSignedInDate] [datetime] NULL ,
[EditedSignedInTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EditedSignedInDateOn] [datetime] NULL ,
[EditedSignedOutDate] [datetime] NULL ,
[EditedSignedOutTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EditedSignedOutDateOn] [datetime] NULL ,
[SignInComments] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SignOutComments] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TotalHoursWorked] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ModifiedDetails] PRIMARY KEY CLUSTERED
(
[EditedID]
ON [PRIMARY]) ON [PRIMARY]
GO
I am using the above sp to in the fallowing sp to get the results for all employees but as i am using the loop i it is taking me more time so,it affects the performance of application
the other sp is
CREATE PROCEDURE [dbo].[sp_AdminReport]
(
@ResultType varchar(50)
)
AS
DECLARE @userid nvarchar(10)
DECLARE @Rowcount int
CREATE TABLE #TEMP_DEPT(UserId nvarchar(10))
CREATE TABLE #DEPREPORT(EmployeeId nvarchar(10),Date varchar(50),SignInTime varchar(50),SignOutTime varchar(50),NoOfHourWorked varchar(50),Mode varchar(50),SignInComments varchar(500),SignOutComments varchar(500))
INSERT INTO #TEMP_DEPT(UserId)
SELECT Emp_User_Name FROM PMS.DBO.Pmt_Employee_Master WHERE OnDelete = 0
SET @Rowcount = (SELECT COUNT(*) FROM #TEMP_DEPT)
WHILE(@Rowcount>0)
BEGIN
SET @userid = (SELECT E1.UserId FROM #TEMP_DEPT E1 WHERE(@Rowcount-1) = (SELECT COUNT(DISTINCT(E2.UserId)) From #TEMP_DEPT E2 Where E2.UserId > E1.UserId))
PRINT @userid
INSERT INTO #DEPREPORT(EmployeeId,Date,SignInTime,SignOutTime,NoOfHourWorked,Mode,SignInComments,SignOutComments)
EXEC sp_SelfReportAdmin @userid
SET @Rowcount = @Rowcount -1
IF (@Rowcount = 0)
BEGIN
SELECT * FROM #DEPREPORT
END
I think from the above you can understand my problem
The main thing is for each and evry employee and for every day i had to check from the two tables and get the result
please find some time and solve the problem
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply