Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Unanswered: Urgent-Time -calculation-query

    I am having entry and exit tables which contains many entries for one person.
    I am having one table which contain entry times for 1 person like
    2011-11-18 09:10:07.000
    2011-11-18 09:55:30.000
    2011-11-18 12:18:13.000
    2011-11-18 14:28:26.000
    2011-11-18 15:42:26.000

    n second table contain exit time like
    2011-11-18 09:52:06.000
    2011-11-18 12:14:19.000
    2011-11-18 14:25:24.000
    2011-11-18 15:40:10.000
    2011-11-18 15:49:28.000

    I need to calculate time diff between all tht entries and want sum of all time diff..
    Can u please suggest me any solution??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by manisha21 View Post
    Can u please suggest me any solution??
    start by writing a query that returns both the entry time and the exit time for each person

    once you have that working, show that query here and we can help you sum the differences

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    20

    Urgent-Time-calculation-query

    select a.nUserID,a.startdate,b.startdate,datediff(hh,a.st artdate,b.startdate)
    from user_mainentry1 a
    inner join user_backexit1 b
    on a.nUserID=b.nUserID

    But userID is not unique as it contains many entries for each ID...
    Table 1 attributes-nuserID,startdate,eventidn
    Table2 attributes-nuserID,startdate,eventidn

    These r nrml tables no primary key is thre

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by manisha21 View Post
    These r nrml tables no primary key is thre
    okay, first of all, may i suggest you wait until you are near an actual keyboard, as you seem to be using thumbs on a phone or something, and you are dropping a lot of letters

    i challenge the contention that these are normal tables (if i understood your remark), because normal tables ~do~ have a primary key, it's only abnormal ones that do not



    now as to the problem...

    your difficulty in matching the correct rows is understood

    i want you to keep trying for a while, though

    the only option you have is to join the rows based on more than just the userid, and the only available "more" column is the datetime

    i'll give you a start -- for an entry row, the exit row that you want to match it with is the exit row that has the earliest datetime of all the exit rows that have a later datetime than the entry row

    yes, the join will involve a subquery

    you will also want to think about what happens if there is an entry without a matching exit, or vice versa, including when there are two entries in a row and then two exits after that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    20
    Hi,

    See that tables extracted from SSIS package so i have nt assigned any PRIMARY KEY for that...they are just having extracted rows from main table...


    and i tried to join on more attributes but getting huge data that is not needed..

    SELECT
    a.[nUserID]
    ,a.[startdate]
    ,b.startdate

    FROM [BioStar].[dbo].[user_mainentry1] a
    join BioStar.dbo.user_backexit1 b
    on a.nUserID=b.nUserID and day(a.startdate)=day(b.startdate)
    where a.startdate<b.startdate

  6. #6
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    for each user, you have to find out the exit corresponding to given entry time.. and then compute difference between exit and entry times and compute the sum of all "differences" calculated.

    question here is how would u find out the exit time corresponding to an entry for a person?
    Cheers....

    baburajv

  7. #7
    Join Date
    Nov 2011
    Posts
    20
    select distinct nUserID,ss,startdate,hours
    from (select a.nUserID,a.startdate,b.startdate as ss ,((DATEDIFF("second",a.startdate,b.startdate)) % 3600) / 60 as minutes
    ,CONVERT(varchar(5), DATEDIFF("minute",a.startdate,b.startdate) / 60) + ':'
    + CONVERT(varchar(5), ((DATEDIFF("second",a.startdate,b.startdate)) % 3600) / 60) as hours

    from BioStar.dbo.user_mainentry1 a,BioStar.dbo.user_backexit1 b
    where a.nUserID=b.nUserID and datediff(day,a.startdate,b.startdate)=0
    and a.startdate>'1900-01-01 00:00:00.000'
    and b.startdate<'9999-01-01 00:00:00.000'
    ) tmp
    where nUserID=8
    group by startdate,nUserID,ss,hours



    I tried ths..but not getting exact actual time of all emplyees..
    Can any one please help me?? its really urgent :-|

  8. #8
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    /*
    create table mainentry
    (
    userid int,
    entrytime datetime
    )
    go
    create table backexit
    (
    userid int,
    exittime datetime
    )
    go

    insert into mainentry
    (
    userid ,
    entrytime
    )
    select 1, '2011-11-22 09:00:00.000'
    union
    select 1, '2011-11-22 10:00:00.000'
    union
    select 1, '2011-11-22 11:00:00.000'
    union
    select 1, '2011-11-22 12:00:00.000'
    union
    select 1, '2011-11-22 13:00:00.000'
    union
    select 2, '2011-11-22 08:00:00.000'
    union
    select 2, '2011-11-22 09:00:00.000'
    union
    select 2, '2011-11-22 10:00:00.000'
    union
    select 2, '2011-11-22 11:00:00.000'
    union
    select 2, '2011-11-22 12:00:00.000'

    insert into backexit
    (
    userid ,
    exittime
    )
    select 1, '2011-11-22 09:30:00.000'
    union
    select 1, '2011-11-22 10:30:00.000'
    union
    select 1, '2011-11-22 11:30:00.000'
    union
    select 1, '2011-11-22 12:30:00.000'
    union
    select 1, '2011-11-22 13:30:00.000'
    union
    select 2, '2011-11-22 08:30:00.000'
    union
    select 2, '2011-11-22 09:30:00.000'
    union
    select 2, '2011-11-22 10:30:00.000'
    union
    select 2, '2011-11-22 11:30:00.000'
    union
    select 2, '2011-11-22 12:30:00.000'

    select * from mainentry

    select * from backexit

    */

    Select TimeSpent.UserID, Sum(TimeSpent.Duration) As TotalTimeInOffice_In_Minutes
    From
    (
    Select UserEntry.UserID,UserEntry.EntryTime, UserExit.exitTime , DateDiff(mi,UserEntry.EntryTime,UserExit.exitTime) As Duration
    From
    (
    Select Row_Number() Over (Partition By UserId Order By EntryTime Asc) As EntryRow#, UserID, EntryTime
    From MainEntry
    )UserEntry
    Inner Join
    (
    Select Row_Number() Over (Partition By UserId Order By exitTime Asc) As ExitRow#, UserID, exitTime
    From backexit
    )UserExit
    On UserEntry.UserID = UserExit.UserID
    And UserEntry.EntryRow# = UserExit.ExitRow#
    )TimeSpent
    group by TimeSpent.UserID


    Please refer post#4 and make the required changes in the query to handle the scenarios mentioned
    Last edited by baburajv; 11-22-11 at 04:16.
    Cheers....

    baburajv

  9. #9
    Join Date
    Nov 2011
    Posts
    20
    Thanks for help but i want output like:

    userid startdate enddate hours
    1 2011-11-12 2011-11-12 9

    as im having many in and out time for one person...and i need to calculate actual in time....

    .but according to query i m getting ths

    bb ss TotalTimeInOffice_In_Minutes
    2011-11-18 09:10:07.000 2011-11-16 12:01:53.000 45
    2011-11-18 09:55:30.000 2011-11-16 13:22:22.000 44
    2011-11-18 12:18:13.000 2011-11-16 16:37:25.000 43
    2011-11-18 14:28:26.000 2011-11-16 17:31:41.000 44
    2011-11-18 15:42:26.000 2011-11-16 20:40:28.000 43
    2011-11-18 15:52:48.000 2011-11-17 10:12:40.000 29
    2011-11-18 16:51:05.000 2011-11-17 11:51:34.000 29
    2011-11-18 19:02:52.000 2011-11-17 12:45:13.000 30
    Attached Thumbnails Attached Thumbnails ss.png  
    Last edited by manisha21; 11-22-11 at 04:47.

  10. #10
    Join Date
    Nov 2011
    Posts
    20
    SELECT nUserID,CONVERT( varchar, startdate , 101)
    ,sum(datepart(hh,startdate)*(1-2*in_out)),sum(datepart(mi,startdate)*(1-2*in_out))
    FROM biostar.dbo.user_info6
    group by nUserID,CONVERT( varchar, startdate , 101)
    order by nUserID,CONVERT( varchar, startdate , 101)

    user_info6 table has an attributes:\
    nUSerID, startdate ,in_out

    I have try like this...like in_out value is in 1 and 0 manner..for entry=1 and exit=0...
    But, when total entry = total exit... then only it gives correct result.. if it is not...it is showing incorrect result......

    Can any1 please help me.....??
    plzzzzzzzzzzzzz its urgent....
    Last edited by manisha21; 11-23-11 at 01:41.

  11. #11
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    in this query you are referring to a table "user_info6", which was not present earlier.

    are these (user_mainentry1, user_backexit1 , user_info6) existing tables? or new ones?

    If you created these tables, make sure that the user entry/exit info along with relevant details are captured in a single table.
    Cheers....

    baburajv

  12. #12
    Join Date
    Nov 2011
    Posts
    20
    no no
    actually user_mainentry and backexit tables i hv created from user_info6...

    "user_info6 table has all entry and exit time of all employees and i hv given in_out(1 and 0) value for entry and exit"

    so im querying on one table nly tht is... user_info6

  13. #13
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    ok fine,

    If we have data in User_Info6 as follows,
    nUSerID startdate in_out
    1 2011-11-23 09:00:00.000 1
    1 2011-11-23 09:00:02.000 1
    1 2011-11-23 10:00:00.000 0

    which (entry) record do you consider for computing the time? Similarly, if you have more than one exit records for a given entry, which one will be considered. pls explain the logic.
    Cheers....

    baburajv

  14. #14
    Join Date
    Nov 2011
    Posts
    20
    See first entry should get subtract from first exit... but if we have another entry it shoud nt get add in previous entry...wht my logic is doing...it is adding all entry and subtract it from sum of all exit....

    i want the total time between same entry and same exit... if there is entry without exit it should nt consider.....

  15. #15
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Quote Originally Posted by manisha21 View Post
    See first entry should get subtract from first exit... but if we have another entry it shoud nt get add in previous entry...wht my logic is doing...it is adding all entry and subtract it from sum of all exit....

    i want the total time between same entry and same exit... if there is entry without exit it should nt consider.....
    try this query..

    NOTE: i didn't test this query completely. not sure whether this handles all possible scenarios..


    Create Table User_Info6
    (userid int, usertime datetime, in_out bit)


    insert into User_Info6 Values(1, '2011-11-19 17:30:30.000', 0)
    insert into User_Info6 Values(1, '2011-11-20 09:00:00.000', 1)
    insert into User_Info6 Values(1, '2011-11-20 09:00:01.000', 1)
    insert into User_Info6 Values(1, '2011-11-20 10:30:10.000', 0)
    insert into User_Info6 Values(1, '2011-11-20 10:50:20.000', 0)
    insert into User_Info6 Values(1, '2011-11-20 12:00:00.000', 1)
    insert into User_Info6 Values(1, '2011-11-20 12:01:30.000', 1)
    insert into User_Info6 Values(1, '2011-11-20 14:30:30.000', 0)
    insert into User_Info6 Values(1, '2011-11-20 15:00:00.000', 1)
    insert into User_Info6 Values(1, '2011-11-20 17:00:00.000', 0)
    insert into User_Info6 Values(2, '2011-11-20 09:30:30.000', 1)
    insert into User_Info6 Values(2, '2011-11-20 10:30:30.000', 1)
    insert into User_Info6 Values(2, '2011-11-20 16:00:30.000', 0)


    declare @start datetime,
    @end datetime

    set @start = '2011-11-20 00:00:00.000'
    Set @end = '2011-11-20 23:59:59.997'

    Select U.UserID,
    Convert(Varchar(10), @start,120)As StartDate,
    Convert(Varchar(10), @end,120) As EndDate,
    Sum(TimeSpentInOffice_InHours) As TotalTimeInHours
    From
    (
    Select Row_Number() Over (Partition By U.UserID Order by U.Usertime) As Row#,
    U.userid,
    U.usertime,
    U.in_out,
    Case When
    U.In_Out = 1 -- The current row must be an entry
    And
    ((Select Top 1 In_Out From User_Info6 Where USerID = U.UserID And UserTime >U.UserTime Order By UserTime) = 0 )-- the next row must be an exit
    Then
    ((DateDiff(ss, U.UserTime, (Select Top 1 UserTime From User_Info6 Where USerID = U.UserID And UserTime >U.UserTime Order By UserTime)) / 60.0)/60.0)
    Else
    0
    End As TimeSpentInOffice_InHours -- time spent in hours
    from User_Info6 U
    Where (U.UserTime between @start and @end)
    )U
    Group By U.UserID, Convert(Varchar(10), U.UserTime,120)


    --drop table User_Info6
    Cheers....

    baburajv

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •