如何用SQL编写计算查询？

Question

使用 SQL Server 2000

我的查询。

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

上面的查询是Access查询，我想在sql中编写相同的查询。

健康）状况。

我想计算 090000(HH:MM:SS) 之后 180000 进入工作时间之前的时间 180000后090000进入加时赛。

数据库中的 Intime、Outtime 数据类型为 varchar

我是 SQL Server 2000 的新手

如何从上面相同的内容编写 SQL 查询？

Answer 1

下面是 SQL Server 2000 的代码。2005+ 可以使用交叉连接在没有子查询的情况下完成此操作。

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

如果您的 inTime 和 outTime 列也有日期部分，则查询略有不同：

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

Answer 2

我错过了 inTime 和 outTime 的类型。
这是 varchar 的版本

错过了，但你只需要做 Cast(inTime as datetime) 。
使用前 2 个查询中的第一个：

  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

Answer 3

这里是对 TSQL 2000 的查询的字面翻译。
不过，可以重写以获得更好的性能：

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

稍后添加：

如果 InTime 和 OutTime 只有时间部分（日期部分是 1900 年 1 月 1 日），则可以直接使用 InTime 和 OutTime。否则，您必须从日期时间列中提取时间部分，如下所示：（

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

这是仅获取时间部分的最快方法）

而不是：

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

您可以使用

   Datepart(hh,OutTime)>17

P.S.因为您的时间存储为字符串，所以您不需要仅获取时间部分。您可以将它们转换为日期时间，或者您也可以编写

 cast(left(inTime,2) as int) < 9

Answer 4

作为初学者，请查看此网站，它向您展示了如何将 Access SQL 语句转换为 SQL Server 使用的 T-SQL。

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

Answer 5

我认为没有一种非常容易和简单的方法来做到这一点 - 最明显的是因为在 VARCHAR 中存储时间值 - 这确实使这变得棘手......

无论如何，我使用了一种具有两个函数的方法 - 一个 dbo.GetSeconds 将时间值的字符串表示形式（'103500' -> 10:35:00 小时）转换为秒数，然后转换为第二个 dbo.GetOvertime 检测是否有超时。

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

有了这两个函数，我就可以相当轻松地计算出您要查找的内容：

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

这有点复杂 - 正如我所说，如果您使用 SQL Server 2008 并使用 TIME 数据类型，事情就会变得容易多了！

马克

Answer 6

您可以使用 substring 和 dateadd 将“090000”转换为真正的日期时间字段。然后你可以使用CASE和DATEDIFF来分割工作和加班。最终的格式可以通过 CONVERT 完成。这是一个例子：

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

这将打印：

09:00:00  00:05:00