views:

658

answers:

6

Using SQL Server 2000

My Query.

SELECT 
  (Format(IIf(CLng(OutTime) > 180000, CDate('18:00:00'),  CDate(Format(OutTime, '00:00:00'))) - IIf(CLng(InTime) < 90000,  CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss')) As WorkTime, 
  (Format(IIf(CLng(InTime) < 90000, CDate('09:00:00') -  CDate(Format(InTime, '00:00:00')), 0) + IIf(CLng(OutTime) > 180000,  CDate(Format(OutTime, '00:00:00')) - CDate('18:00:00'), 0), 'hh:nn:ss')) As OverTime 
FROM table

Above query is Access Query, I want to write a same query in sql.

Condition.

I want to Calculate the time after 090000(HH:MM:SS) before 180000 comes in worktime, before 090000 after 180000 comes in overtime.

Intime, Outime data type is varchar in the database

Am new to SQL Server 2000

How to write a SQL query from the above same?

A: 

As a starter take a look at this website which shows you how to convert Access SQL statements into T-SQL as used by SQL server.

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

pjp
A: 

I don't think there's a very easy and simple way to do this - most notably because of storing time values in VARCHAR - this is really making this tricky.....

Anyway, I used an approach with two functions - one dbo.GetSeconds that converts a string representation of a time value ('103500' -> 10:35:00 hours) to a number of seconds, and then a second one dbo.GetOvertime that detects if there is any overtime.

CREATE FUNCTION dbo.GetSeconds(@input varchar(20))
RETURNS int
AS BEGIN
  DECLARE @Hour INT
  DECLARE @Minute INT
  DECLARE @Second INT

  DECLARE @TotalSeconds INT

  SET @Hour = CAST(SUBSTRING(@input, 0, LEN(@input)-3) AS INT)
  SET @Minute = CAST(LEFT(RIGHT(@input, 4), 2) AS INT) 
  SET @Second = CAST(RIGHT(@input, 2) AS INT)

  SET @TotalSeconds = @Hour * 3600 + @Minute * 60 + @Second

  RETURN @TotalSeconds
END


CREATE FUNCTION dbo.GetOvertime(@fromSeconds INT, @toSeconds INT)
RETURNS int
AS BEGIN
  DECLARE @Overtime INT

  SET @Overtime = 0

  IF @fromSeconds < 32400  -- 32400 seconds = 09:00 hours 
     SET @Overtime = @OverTime + (32400 - @fromSeconds)

  IF @toSeconds > 64800    -- 64800 seconds = 18:00 hours 
     SET @Overtime = @OverTime + (@toSeconds - 64800)

  RETURN @Overtime
END

With those two functions in place, I can fairly easily calculate what you're looking for:

SELECT
    dbo.GetOvertime(dbo.GetSeconds(InTime), dbo.GetSeconds(OutTime)) 'Overtime',
    (dbo.GetSeconds(OutTime) - dbo.GetSeconds(InTime) - 
     dbo.GetOvertime(dbo.GetSeconds(InTime), dbo.GetSeconds(OutTime))) 'Worktime',  
FROM YourTable

It's a bit involved - as I said, if you'd be on SQL Server 2008 and using the TIME data type, things would be a whole lot easier!

Marc

marc_s
Heavy, as you are working with strings.Instead of Streing operations you can use: SET @Hour = DatePart(hh,@input) SET @Minute = DatePart(mi,@input) SET @Second = DatePart(ss,@input)Although, next expression will return seconds much faster: Datediff(ss,0,DateAdd(Day,-DateDiff(Day, 0, @input), @input))At the end, final query dos not follow original logic
Niikola
Sorry for unformatted comment. How to add line break in comments?
Niikola
Since the data appears to be in the format of "90000" for 09:00:00, this approach with DATEPART won't work: Msg 241, Level 16, State 1, Line 9Conversion failed when converting date and/or time from character string.
marc_s
and you cannot do any formatting in comments, so no need to be sorry for that :-) it's a system limitation
marc_s
as I already mentioned - it would be **A LOT EASIER** if the data was in DATETIME format (or even TIME format in SQL server 2008)
marc_s
A: 

Here is literally translated query to TSQL 2000. Although, it could be rewritten for better performance:

Select Convert(char(8),
           case when DateAdd(Day,-DateDiff(Day, 0, OutTime), OutTime)>'18:00:00' 
                Then Cast('18:00:00' as datetime) 
                Else DateAdd(Day,-DateDiff(Day, 0, OutTime), OutTime) 
           End 
           - 
           Case when DateAdd(Day,-DateDiff(Day, 0, InTime),  InTime) <'09:00:00' 
                Then Cast('09:00:00' as datetime) 
                Else DateAdd(Day,-DateDiff(Day, 0, InTime),  InTime) 
           End, 
           8
          ) as WorkTime,
   Convert(char(8),
           Case when DateAdd(Day,-DateDiff(Day, 0, InTime),  InTime) <'09:00:00' 
                Then Cast('09:00:00' as datetime) - 
                     DateAdd(Day,-DateDiff(Day, 0, InTime),  InTime)
                Else Cast('00:00:00' as datetime) 
           End 
           + 
           case when DateAdd(Day,-DateDiff(Day, 0, OutTime), OutTime)>'18:00:00' 
                Then DateAdd(Day,-DateDiff(Day, 0, OutTime), OutTime) - 
                     Cast('18:00:00' as datetime) 
                Else Cast('00:00:00' as datetime) 
           End, 
           8
          ) as OverTime
From Table

Added later:

If InTime and OutTime have time part only (Date part is Jan 1 1900) you can use directly InTime and OutTime. Otherwise you have to extract time part from datetime column, as:

  DateAdd(Day,-DateDiff(Day, 0, OutTime),  OutTime)

(this is the fastest way to get time part only)

Instead of:

   DateAdd(Day,-DateDiff(Day, 0, OutTime), OutTime)>'18:00:00'

You can use

   Datepart(hh,OutTime)>17

P.S. as your time is stored as string yoiu don't need to get time part only. You can cast them to datetime, or you can write also

 cast(left(inTime,2) as int) < 9
Niikola
@Niikola - Showing Error in Arithmetic Express overflow error, Converting Expression datatype to datetime
Gopal
What data type are your InTime and OutTime columns?
Niikola
and do they contain date part too, or just time part?
Niikola
you have to put Cast(inTime as datetime) instead of inTime and cast(outtime as datetime) as outtime as they are strings. I wrote the query wrongly assuming those two columns are already datetime.
Niikola
@Niikola - MY Intime and Outtime column data type is varchar, I wrote like this CAST(INTIME AS DATETIME) AS INTIME, CAST(OUTTIME AS DATETIME) AS OUTTIME. It showing Run Time Error - Arithmetic overflow error Converting Expression to datatype datetime
Gopal
How do you save time, as 'hh:mm:ss' or 'hhmmss'? Arithmetic overflow can happen only in case of 'hhmmss' where you have to insert semicolons. This further complicates the query, unfortunately.The fastest way should be: Print Stuff(Stuff('112233',3,0,':'),6,0,':')although you can use substring too, but it's a bit slower.
Niikola
A: 

You could use substring and dateadd to convert the '090000' to a real datetime field. Then you can use CASE and DATEDIFF to split work and overtime. The final formatting can be done with CONVERT. Here's an example:

select
  case when outtime-intime > '9:00:00' then '09:00:00'
    else convert(varchar(30), outtime-intime, 108)
  end as WorkTime,
  case when outtime-intime <= '9:00:00' then '00:00:00'
    else convert(varchar(30), outtime-intime-'9:00:00', 108) 
  end as OverTime
from (
  select 
    dateadd(hh,cast(substring('090000',1,2) as int),0) +
    dateadd(mi,cast(substring('090000',3,2) as int),0) +
    dateadd(ss,cast(substring('090000',5,2) as int),0) as InTime,
    dateadd(hh,cast(substring('180500',1,2) as int),0) +
    dateadd(mi,cast(substring('180500',3,2) as int),0) +
    dateadd(ss,cast(substring('180500',5,2) as int),0) as OutTime
) vw

This will print:

09:00:00  00:05:00
Andomar
It does not follow the requested logic. It works with hours only and in case of one hour late In and one hour late Out it will display 9 hrs WorkTime and 0 OverTime, while original will display 8 hours WT and 1 hour OT
Niikola
What do you mean, works with hours only? The example is actually in minutes. And the question counts the first 9 hours as work time, not just the first 8 (he must be working in Elbonia)
Andomar
I mean, it worked with hours only before you edited your post :pThis part of code is extremely unefficient: dateadd(hh,cast(substring('090000',1,2) as int),0) + dateadd(mi,cast(substring('090000',3,2) as int),0) + dateadd(ss,cast(substring('090000',5,2) as int),0) you can use cast('09:00' as datetime)
Niikola
A: 

Here is code for SQL server 2000. 2005+ could do it without subquery using cross join.

Select DateAdd(mi, (Case When bef<0 Then bef else 0 end + Case When aft<0 Then aft else 0 end), diff) as WorkTome,
       DateAdd(mi, (Case When bef>0 Then bef else 0 end + Case When aft>0 Then aft else 0 end), 0)    as OverTime
From (       
Select outTime-inTime as diff,
       DateDiff(mi,t.inTime,'09:00') as bef,
       DateDiff(mi,'18:00',t.outTime) as aft
  From Table t ) as a

If Your inTime and outTime columns hase date part too, the query is slightly different:

Select DateAdd(mi, (Case When bef<0 Then bef else 0 end + Case When aft<0 Then aft else 0 end), diff) as WorkTome,
       DateAdd(mi, (Case When bef>0 Then bef else 0 end + Case When aft>0 Then aft else 0 end), 0)    as OverTime
From (       
Select outTime-inTime as diff,
       DateDiff(mi, DateAdd(Day,-DateDiff(Day, 0, t.inTime), t.inTime),'09:00') as bef,
       DateDiff(mi,'18:00',DateAdd(Day,-DateDiff(Day, 0, t.OutTime), t.OutTime)) as aft
  From #t t ) as a
Niikola
You will get result as datetime. If you want to get it as formated string, just add Convert(char(5), <exp>, 8) for 'HH:mm'
Niikola
All I'm getting is: Msg 8117, Level 16, State 1, Line 4Operand data type varchar is invalid for subtract operator.
marc_s
The point is: the source data "InTime" and "OutTime" is *NOT* in DATETIME format - it's a VARCHAR string, as the OP mentioned
marc_s
you can't just do a `outTime-inTime as diff` on varchar columns......
marc_s
I missed sentence saying the type, sorry. But this does not change anything, except adding cast as datetime. See next answer
Niikola
A: 

I missed type of inTime and outTime. Here is the version for varchar

Missed that, but you just have to do Cast(inTime as datetime) . Use the first of previous 2 queries:

  Select DateAdd(mi, (Case When bef<0 Then bef else 0 end + Case When aft<0 Then aft else 0 end), diff) as WorkTome,
         DateAdd(mi, (Case When bef>0 Then bef else 0 end + Case When aft>0 Then aft else 0 end), 0)    as OverTime
  From (       
  Select Cast(t.outTime as datetime)-Cast(t.inTime as datetime) as diff,
         DateDiff(mi,Cast(t.outTime as datetime),'09:00') as bef,
         DateDiff(mi,'18:00',Cast(t.outTime as datetime)) as aft
    From Table t ) as a
Niikola