October 17, 2010 at 1:04 am
Dear friends,
I have a card time table in DB. it has cardID , TimeofCard and date.
I want to calculate presence time of each person when he asked.
A sample of this is when a person wants to see his time sheet.
- also we have a rule about moving between buildings, so the maximum of time allowed for each building stored in anouther table.
I have written a code with courser , but it is very slow:
CREATE PROCEDURE [dbo].[CalculatePresenceTimeMZO]
@CardCode varchar(10),
@KhorshidiDate char(10),
@LastTime datetime , -- Time of last row which we want to compute presence time based on that row in io_date=@KhorshidiDate for io_cardCode=@CardCode
@PresenceTime datetime output
AS
-- ***************************************************************************************************************************************************************************************************************************************
-- This procedure is to to compute the presence time for a person whom his card Number = @CardCode on Date=@KhorshidiDate from his first entrance till the time which IO_Time=@LastTime
-- Parameters:
-- @CardCode: input parameter, io_cardCode of the person whom we want to compute his presence time
-- @KhorshidiDate: input parameter, io_date of the date which we want to compute the presence time on that date
-- @LastTime: Time of last row which we want to compute presence time based on that row in io_date=@KhorshidiDate for io_cardCode=@CardCode. The year, month, day should be 1900-01-01 to
-- match the data in InOut table
-- @PresenceTime: output parameter, presencTime of the person whom his io_cardCode=@CardCode on io_date=@KhorshidiDate from his first entrance till IO_Time=@LastTime
-- The overalll alrorithm is like this
-- @SumTransporation = 0
-- Fetch first row
-- while more rows
-- if enter=1
-- calculate transportation time between different buildings
--if (transportation time between different buildings) is valid then
--@SumTransporation = @SumTransporation + (transportation time between different buildings)
--set @Enter=0
-- else
--The presence time between two MOTEVALI rows are computed
--set @Enter=1
-- end if
-- end while
-- ***************************************************************************************************************************************************************************************************************************************
declare @MyCursor as CURSOR
declare @TmpMainTime2 as dateTime-- IO_Time of next row
declare @TmpMainTime as datetime-- IO_Time of current row
declare @TempAddDelta as dateTime-- Is the presence time between two MOTEVALI rows in InOut
declare @Enter as bit -- if @Enter = 1 then this row is related to entrance time
declare @TmpWatchBld as integer-- Current building number
declare @TmpWatchBld2 as integer-- Next building number
declare @ValidTranportationTime as int-- Valid transportation time between 2 different buildings (@TmpWatchBld and @TmpWatchBld2)
declare @SumTransporation as int
declare @count as int-- count how many records are in Curser
set @count=0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Fetch the rows which are on date=@KhorshidiDate for Card Number=@CardCode and MainTime <= @LastTime
-- The data needed are MainTine and Building Number which employee has used card
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT inout.IO_Time, WatchBuilding.BuildingNo
FROM cardtime.dbo.inout inout with (nolock)
LEFT OUTER JOIN cardtime.dbo.WatchBuilding WatchBuilding
ON inout.IO_RdrCode = WatchBuilding.Rdr_Code
WHERE IO_CardCode = @CardCode
AND IO_Date = @KhorshidiDate and IO_Time <= @LastTime
order by IO_Time
---------------------------------------------------------------------
-- @TmpMainTime = IO_Time
-- @TmpWatchBld = BuildingNo
---------------------------------------------------------------------
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @TmpMainTime, @TmpWatchBld
-------------------------------------
-- initialize variables
-------------------------------------
set @TempAddDelta= '1900-01-01 12:00AM'-- @TempAddDelta is zero at first
set @Enter = 0
set @SumTransporation = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @count =@count+1
if @Enter = 1
---------------------------------------------------------------------------------------------------------------------------------------------
-- In <if> branch, we compute transpotation Time between two different buildings
---------------------------------------------------------------------------------------------------------------------------------------------
begin
FETCH NEXT FROM @MyCursor INTO @TmpMainTime, @TmpWatchBld
if @TmpWatchBld <> @TmpWatchBld2
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Employee has exited one buildung and entered some other building, so calculate the transportation time and
-- if this is a valid transportation time, then add it to @SumTransporation
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
--------------------------------------------------------------------------------------
--calculate transportaion time between two buildings
--------------------------------------------------------------------------------------
Select @ValidTranportationTime = TransportationTime from CardTime.dbo.TransportTime
where (FirstBuilding = @TmpWatchBld and SecondBuilding = @TmpWatchBld2) or
(FirstBuilding = @TmpWatchBld2 and SecondBuilding = @TmpWatchBld)
if not(@ValidTranportationTime is null)
if Datediff(mi,@TmpMainTime2, @TmpMainTime) <= @ValidTranportationTime
set @SumTransporation = @SumTransporation + Datediff(mi,@TmpMainTime2, @TmpMainTime)
end
set @Enter = 0
end
else
-- if @Enter = 0
begin
------------------------------------------------------------------------------------------------------------
-- The presence time between two MOTEVALI rows is computed
------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM @MyCursor INTO @TmpMainTime2, @TmpWatchBld2
if @@FETCH_STATUS <> 0
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- No more row are found, so the number of rows are odd, so presence time should be computed considering @LastTime as the
-- last row
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
if (@count%2) =1
begin
set @TmpMainTime2 = cast( cast( datepart(hh,getdate())as nvarchar(2)) +':'+cast(datepart(mi,getdate())as nvarchar(2)) as datetime)
end
else
begin
set @TmpMainTime2 = @LastTime
end
set @TempAddDelta = Dateadd(mi,Datediff(mi,@TmpMainTime, @TmpMainTime2) , @TempAddDelta)
set @Enter = 1
end
end
CLOSE @MyCursor
DEALLOCATE @MyCursor
--if @enter=1 -- we have to add delta agian becuase in the odd time (enter without exist) we subtract it twice so it should be added agian
-- set @SumTransporation = @SumTransporation + Datediff(mi,@TmpMainTime2, @TmpMainTime)
set @PresenceTime = Dateadd(mi,cast(@SumTransporation as integer), @TempAddDelta )
if @PresenceTime is null
set @PresenceTime= '1900-01-01 12:00AM'
GO
Best Regards,
Ashkan
October 17, 2010 at 3:22 am
I don't think there is a c.u.r.s.o.r *cough* needed...
But in order to show a set based solution I'd like to see table def for cardtime.dbo.inout and cardtime.dbo.WatchBuilding including some sample data and expected result based on the sample data.
See the first link in my signature for details.
October 17, 2010 at 4:19 am
Dear Lutz,
Thanks for reply.
I've attached 3 jpg files to this post.
If you need script of those or any thing else just tell me.
Best Regards,
Ashkan
October 17, 2010 at 4:30 am
Ashkan,
did you read the article I pointed you at?
The purpose of posting table def and sample data in a ready to use format is to help us help you.
We are all volunteers spending our spare time to help others. So it would save us some time if we just can copy and paste the sample data nd work on the solution immediately instead of creating the test scenario first. Some of us (including me) tend to move on to the next question... (especially if we have to type everything off a jpg...) 😉
It's not for doing me a favour. It's to increase the number of people willing to help you.
October 17, 2010 at 5:40 am
Dear Lutz,
I didn't mean that , and i know you help me and all people because of your kindness.
I will attache those immediately, and if i said something that you think is bad I apologize you. this is because of my English... not because of...
Best Regards,
Ashkan
October 17, 2010 at 6:31 am
ashkan siroos (10/17/2010)
Dear Lutz,I didn't mean that , and i know you help me and all people because of your kindness.
I will attache those immediately, and if i said something that you think is bad I apologize you. this is because of my English... not because of...
There's nothing you said that was offending in any kind. No problem at all.
The intention of my response was to explain why some of us prefer ready to use sample data. Nothing more. But nothing less either.
Regarding your attached sample data: since I don't have RAR installe,d I can't unpack it.
Maybe you should upload it as a zipped file or as a txt file.
Edit: please confirm that you use SQL2000 (since you posted on the SS2K forum). It might influence the possible solutions.
October 17, 2010 at 7:13 am
yes,I use Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) ;
and I attached those 4 files to this post without zipping or raring.
and because of forum attachment format , I had to change .sql file to .txt.
Best Regards,
Ashkan
October 17, 2010 at 8:16 am
Unfortunately, it seems like you didn't read the article yet.
I tried to import the xls data you provided but the time columns will remain empty even if it seems like there are data in the xls file.
Furthermore, your expected result set for a given scenario is missing.
So let's start all over again:
read and follow the instructions given in the first article in my signature and provide ready to use table def sample data. Also, please provide your expected result set based on the sample data and a description of the business rules if not self-explaining.
Don't expect us to reverse-engineer your 100+ line c.u.r.s.o.r. to figure out what you're trying to do.
Especially please describe how to determine if a timestamp will be used as IN vs. OUT.
If I understand the basic concept correctly there should always be two rows with the identical IO_RdrCode (except for the last row) since a person that enters a building has to leave a building before it can enter another building (or re-enter).
Your sample data do not show this pattern (see row 10 in your inout xls file).
Based on that I don't think we have enough information to help you.
October 18, 2010 at 1:55 am
Dear Lutz,
Please accept my apology ,
know I have read your article and I have done that.
Attachment has all 3 tables in it - temp creation ,and temp data
2 of them (#WatchBuilding , #TransportTime) are fully copied
and the #inout table created with about 100 rows of data for one of employees who use transportation time limit.
Some days he transport between buildings and some days not.
There is about 2 milion rows in my inout table.
The procedure should return all his presence time . if transport time is valid add it to time calculation .
example: 8 enter to 1st- 10 exit , 10:25 enter to 2nd 12 exit. when 30 min is valid ---> 4 hours presence time.
when 20 min is valid --------->3:30 hours presence time
Best Regards,
Ashkan
October 19, 2010 at 4:18 am
help please
Best Regards,
Ashkan
October 19, 2010 at 2:58 pm
I'm very sorry, but the main reason you didn't get an answer yet is still an incomplete requirement: We have the table and the data in a ready to use format. PERFECT!
We also have a sproc that will not run since it's not compliant with your sample tables (temp tables vs. tables in a cardtime database we don't have).
Assuming we'd fix those errors, we still wouldn't know the parameter values to run the sproc with nor do we have any results that sproc should return with thos parameter.
Most probably you didn't get any help by now is that we don't know what you're looking for.
Remember that we can't look over your shoulder nor do we know the business case you're dealing with. All we have is the information you posted so far. And that doesn't make sense yet.
Please help us help you!
October 20, 2010 at 6:11 am
...oh, ok I have change those...
I change my SP to a simple query which use temp tables as data.
You just need to change the "@KhorshidiDate" parameter to change the day witch you want to know The presence time of employee.
And output will show presence time in data time, For example if your employee worked 11 hours , it will show you 1/1/1900 11:00:00.00.
I have added 2 line in top of the file for input:
SET @KhorshidiDate='1389/01/15'-- unautorize building change
SET @KhorshidiDate='1389/01/16'-- Autorize Building change
to show 2 kind of building changes in data.
You can see in 1389/01/15 -2010/04/04- moving from 3:01PM till 4:14PM is unauthorized so it will not calculate in presence time.
Sorry for wasting your time,
Please help me if possible...
Best Regards,
Ashkan
October 20, 2010 at 12:26 pm
Here's my approach.
I took a while since I had to figure out how the calcualtion is done...
If my reverse-engineering failed you have to modify the aggregation to meet your requirement. At least I get the same result when comparing both solutions using your sample data...
Side note: I also recommend to change the primary key of your TransportTime table to use the two buildings. Not only it'll ensure unique combinations but also will allow an seek instead of a scan operation (don't know how large that table will be though....)
CREATE TABLE #TransportTime
(
[Id] [int] IDENTITY (1, 1) ,--Is an IDENTITY column on real table
[FirstBuilding] [int] NOT NULL ,
[SecondBuilding] [int] NOT NULL ,
[TransportationTime] [int] NOT NULL,
PRIMARY KEY CLUSTERED ([FirstBuilding],[SecondBuilding])
)
/*
--===== Create an intermediate table to assign a consecutive number to each row of the
list as per the original *cough* loop definition
*/
CREATE TABLE #Subtotal
(id INT IDENTITY(1,1),
IO_Time [DATETIME] ,
BuildingNo [INT] NOT NULL ,
CONSTRAINT PK_#Subtotal_id PRIMARY KEY CLUSTERED(id)
)
-- populate the table using exactly the same statement as for the loop.
INSERT INTO #Subtotal
SELECT
inout.IO_Time,
WatchBuilding.BuildingNo
FROM #INOUT INOUT
LEFT OUTER JOIN #WatchBuilding WatchBuilding
ON inout.IO_RdrCode = WatchBuilding.Rdr_Code
WHERE IO_CardCode = @CardCode
AND IO_Date = @KhorshidiDate
AND IO_Time <= @LastTime
ORDER BY IO_Time -- important, to get the id value in the same order as the IO_TIME
-- final query
SELECT
DATEADD(
mi,
-- the same calcualtion for each row as in originally used (at least I hope so...)
SUM(
CASE
WHEN (s1.buildingno<>s2.buildingno AND DATEDIFF(mi,s1.io_time,s2.io_time) < transportationtime)
OR s1.buildingno = s2.buildingno THEN 1
ELSE 0
END * DATEDIFF(mi,s1.io_time,s2.io_time)
)
,0)
FROM #Subtotal s1
INNER JOIN #Subtotal s2
ON s1.id=s2.id-1 -- self reference of the intermediate table to the next row
LEFT OUTER JOIN #TransportTime t
ON (t.firstbuilding=s1.buildingno AND t.secondbuilding=s2.buildingno)
OR (t.secondbuilding=s1.buildingno AND t.firstbuilding=s2.buildingno)
October 20, 2010 at 1:06 pm
I think he probably gave up because you still haven't filled the request for easily consumable create table statements and test data. When I looked at the file you sent, there were no line breaks in it and it all ran together in a mess. I've hung out here quite a bit and some of these people are VERY good, and very accomodating... but it's still an unpaid gig for them and in general they don't have the time to reformat files so that they are usable.
good luck!
edit - oops, I didn't see page 2 on this, sorry
October 20, 2010 at 2:05 pm
Uripedes Pants (10/20/2010)
I think he probably gave up because you still haven't filled the request for easily consumable create table statements and test data. When I looked at the file you sent, there were no line breaks in it and it all ran together in a mess. I've hung out here quite a bit and some of these people are VERY good, and very accomodating... but it's still an unpaid gig for them and in general they don't have the time to reformat files so that they are usable.good luck!
edit - oops, I didn't see page 2 on this, sorry
It actually takes a while before I give up without notice. But this time it was close, I have to admitt 😉
You're definitely right that the number of people willing to have a look at a problem posted will drop significantly when the data are not ready to use or there seems to be too much effort to setup the data before actually start working on it. Missing expected output and/or lack of code description will reduce it even further...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply