Thankyou so much for your support.
But let me ask for one more favour, the query above gave me the correct sequence number. Now i need to insert this data into a table named "AttendanceDetails" , which has following structure.
here attendance_id, is auto generated, employee id is last 4 digit of tktno in the previous query, and Is present is the duration.
Can you also suggest the mechanism for this?
i can also show you the current mechanism followed , which fails in this task for night shift
ALTER PROCEDURE [dbo].[sp_attendetails]
(
@Attendance_Date DATE
)
AS
BEGIN
SELECT tktno,t.date into #temp1 from PunchTimeDetails t
where date>@Attendance_Date
declare @curDate Date
set @curDate = GETDATE()
--set @curDate='2015/3/12'
declare @maxDate Date
SET @maxDate =@Attendance_Date
--SET @maxDate ='2015/3/11'
;with GetDates As
(
select 1 as counter, @maxDate as Date
UNION ALL
select counter + 1, DATEADD(day,counter,@maxDate)
from GetDates
where DATEADD(day, counter, @maxDate) <= @curDate
)
select Date into #tempdate from GetDates
SELECT A.tktno,B.Date,MIN(A.date)'INTime',MAX(A.date)'OUTTime'
INTO #Resulttemp
FROM #temp1 A
INNER JOIN #tempdate B
ON CONVERT(CHAR(10),A.date,103)=CONVERT(CHAR(10),B.Date,103)
GROUP BY B.Date,A.tktno
SELECT
--REPLACE(REPLACE(REPLACE(tktno,'001','1'),'010','10'),'011','11') AS 'tktno'
RIGHT(tktno, 4) as 'tktno'
,(SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), B.InTime, 100), 7)) )AS 'INTime',
CASE WHEN INTime=OUTTime THEN NULL
ELSE
(SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), B.OUTTIME, 100), 7)) )
END AS 'OUTTime',
Date,
convert(varchar(5), sum(datediff(minute, INTime, isnull(OUTTIME, dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, INTime), 0))))) / 60)
+ ':' +
convert(varchar(5),sum(datediff(minute, INTime, isnull(OUTTIME, dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, INTime), 0))))) % 60)
as 'TTime',
' ' AS 'Comments',
'101' AS 'CreateBy',
getdate() AS 'CreateDate',
'101' AS 'UpdatedBy',
getdate() AS 'UpdatedDate',
CASE WHEN B.INTime IS NULL THEN 559
ELSE 56
END AS 'PresentType'
FROM #Resulttemp b
where tktno > 999
GROUP BY tktno,INTime,OUTTIME,date
drop table #temp1
drop table #tempdate
drop table #Resulttemp
END
