Stored Procdure Issue!!! - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procdure Issue!!!

Author

Topic

Bengallion
Starting Member

1 Post

Posted - 2012-04-01 : 07:11:53

I have one master table Access. The secondary table is AccessTemp. And the third table is DailyInOut. I've written a SP_DailyInOut which will read data from AccessTemp and insert the data into tmpDailyinout which I declare in my sp. Then read all the rows group by Date and EmpID. And find the Min(PunchTime) and Max(PunchTime) and Finally insert the data into DailyInOut. Here is my DDL.
Table Access
USE [PgaSQL]
GO

/****** Object: Table [dbo].[Access] Script Date: 04/01/2012 16:19:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Access](
[Serial] [int] IDENTITY(1,1) NOT NULL,
[ID] [nvarchar](20) NULL,
[EmpID] [nvarchar](20) NULL,
[Name] [nvarchar](30) NULL,
[DoorNo] [nvarchar](10) NULL,
[DoorDsc] [nvarchar](30) NULL,
[Date] [datetime] NULL,
[Status] [nvarchar](20) NULL,
[in_out] [nvarchar](50) NULL,
[CarNo] [nvarchar](20) NULL,
[CarPosition] [nvarchar](20) NULL,
[AnT] [int] NULL
) ON [PRIMARY]

Table AccessTemp
USE PgaSQL
GO
SELECT Serial, EmpID, Date AS PunchTime, Date
INTO AccessTemp
FROM Access

Go
ALTER TABLE AccessTemp
ALTER COLUMN PunchTime Time
Table DailyInOut
USE [PgaSQL]
GO

/****** Object: Table [dbo].[DailyInOut] Script Date: 04/01/2012 14:43:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DailyInOut](
[EmpID] [nvarchar](20) NULL,
[Date] [datetime] NULL,
[InTime] [time](7) NULL,
[OutTime] [time](7) NULL
) ON [PRIMARY]

GO

SPDailyInOut
USE [PgaSQL]
GO
/****** Object: StoredProcedure [dbo].[Sp_DailyInOutDetails] Script Date: 04/01/2012 15:27:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MD.MITHUN
-- Create date: 01/04/2012
-- Description: Fill Daily In Out Data
-- =============================================
CREATE PROCEDURE [dbo].[Sp_DailyInOutDetails]

@strDate datetime
AS
DECLARE @tmpAccessTemp table
(
Serial int IDENTITY (1,1),
EmpID nvarchar (20),
Date datetime,
InTime time,
OutTime time
)

INSERT INTO @tmpAccessTemp (EmpID,Date,InTime,OutTime)
SELECT EmpID, Date, MIN(PunchTime) AS InTime, MAX(PunchTime) AS OutTime FROM AccessTemp
WHERE Date=@strDate
GROUP BY EmpID, Date

DECLARE @vIndex int, @EmpID nvarchar, @Date datetime, @InTime time, @OutTime time, @tRec int

SET @vIndex = 1
SET @tRec = (SELECT COUNT(*) FROM @tmpAccessTemp)

WHILE (@vIndex <= @tRec)

BEGIN

SELECT @EmpID = EmpID, @Date = Date, @InTime = InTime, @OutTime = OutTime FROM @tmpAccessTemp WHERE Serial=@vIndex
IF NOT EXISTS(SELECT * FROM DailyInOut WHERE EmpID=@EmpID and Date=@Date)
INSERT INTO DailyInOut (EmpID, Date, InTime, OutTime) VALUES (@EmpID, @Date, @InTime, @OutTime)
set @vIndex = @vIndex + 1

END

My Sp is executing but the data isn't inserted.........Please help me.....

Bengallion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-01 : 08:15:22

It is hard to tell without some sample data, but browsing through the code, it seems to me that you are setting the data type of the PunchTime in AccessTemp table to TIME. When you do that, the DATE information is lost. That may be the root cause of the problem. See if and why you need to do that, and if you do, make sure that when you use that column you are comparing against other TIME data types rather than DATETIME data types.

ALTER TABLE AccessTemp
ALTER COLUMN PunchTime Time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources