Calc working hours (TSQL Problem)

• sqlbaby2

  Ten Centuries

  Points: 1351

  More actions

  January 13, 2011 at 3:46 am

  #232945

  Hi All,

  I have a table with Date, Time, CardNumber, Name, StaffNumber, Transaction and TranCode

  Now i have to find the total amount of times which users have spent at office.

  Following is the table structure

  Date Time CardNumber Name StaffNumber

  -----------------------------------------------------------------------------------------------------

  2011-01-05 00:00:00.000 1900-01-01 13:00:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 13:23:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 13:34:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 13:39:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 13:53:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 16:25:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 17:22:00.000 20505 Alex 017

  2011-01-05 00:00:00.000 1900-01-01 19:08:00.000 20505 Alex 017

  Transaction TranCode

  -------------------------

  0 Valid Entry 0

  9 Valid Exit 9

  0 Valid Entry 0

  9 Valid Exit 9

  0 Valid Entry 0

  9 Valid Exit 9

  0 Valid Entry 0

  9 Valid Exit 9

  Expected result should be

  Date Time CardNumber Name StaffNumber Duration

  -----------------------------------------------------------------------------------------------------

  2011-01-05 00:00:00.000 13:00 - 13:23 20505 Alex 017 0:23

  2011-01-05 00:00:00.000 13:34 - 13:39 20505 Alex 017 0:05

  2011-01-05 00:00:00.000 13:53 - 16:25 20505 Alex 017 0:32

  2011-01-05 00:00:00.000 17:22 - 19:08 20505 Alex 017 1:46

  ---------

  2:46

  ---------

  Here is the sample script

  Declare @Attendence table

  ( [Date] [datetime] NULL,

  [Time] [datetime] NULL,

  [CardNumber] [int] NULL,

  [Name] [nvarchar](100) NULL,

  [StaffNumber] [nvarchar](50) NULL,

  [Transaction] [nvarchar](50) NULL,

  [TranCode] [nvarchar](50) NULL

  )

  INSERT INTO @Attendence

  select '2011-01-05 00:00:00.000', '1900-01-01 13:00:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:23:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:34:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:39:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:53:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 16:25:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 17:22:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 19:08:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9'

  Anyone can gv me a hand :p

• WayneS

  SSC Guru

  Points: 95461

  More actions

  January 13, 2011 at 6:58 am

  #1273001

  You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  January 13, 2011 at 8:01 am

  #1273064

  Kind of hard and fast but might give you a path forward.

  Declare @Attendence table

  ( [Date] [datetime] NULL,

  [Time] [datetime] NULL,

  [CardNumber] [int] NULL,

  [Name] [nvarchar](100) NULL,

  [StaffNumber] [nvarchar](50) NULL,

  [Transaction] [nvarchar](50) NULL,

  [TranCode] [nvarchar](50) NULL

  )

  INSERT INTO @Attendence

  select '2011-01-05 00:00:00.000', '1900-01-01 13:00:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:23:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:34:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:39:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 13:53:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 16:25:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9' union

  select'2011-01-05 00:00:00.000', '1900-01-01 17:22:00.000', 20505, 'Alex', '017', '0 Valid Entry', '0' union

  select'2011-01-05 00:00:00.000', '1900-01-01 19:08:00.000', 20505, 'Alex', '017', '9 Valid Exit', '9'

  SELECTx.[Date]

  , x.[CardNumber]

  , x.[Name]

  , x.[StaffNumber]

  , x.[RowId]

  , (MAX(CASE WHEN x.[TranCode] = 9 THEN x.[Time] END)

  - MAX(CASE WHEN x.[TranCode] = 0 THEN x.[Time] END))

  FROM(

  SELECT[Date]

  ,[Time]

  ,[CardNumber]

  ,[Name]

  ,[StaffNumber]

  ,[Transaction]

  ,[TranCode]

  , ROW_NUMBER() OVER (PARTITION BY TranCode ORDER BY [Date], [Time]) AS [RowId]

  FROM@Attendence

  ) x

  GROUP BY x.[Date]

  , x.[CardNumber]

  , x.[Name]

  , x.[StaffNumber]

  , x.[RowId]

  _____________________________________________________________________
  - Nate

  @nate_hughes