间隔时间

[david12359]

我想计算间隔时间，编号40代表开始的时间，37代表结束时间。我已经插了40的时间，但是我UPDATE结束时间时，没法一一对应。我想用order by stoptime，但UPDATE不支持。请帮忙。还有，我如何计算间隔时间，SQL语句里有没有转换AM， PM。

EventPerSystem
id SystemEventID DateTime
1 40 1/13/2006 9:22:00AM
2 40 1/13/2006 6:45:00PM
3 40 1/16/2006 9:21:00AM
4 40 1/19/2006 9:34:00AM
7 37 1/13/2006 10:22:00 AM
8 37 1/13/2006 8:45:00 PM
9 37 1/16/2006 12:21:00 AM
10 37 1/17/2006 10:22:00 AM

FunctionLog
caseid functionid starttime duration stoptime
1 1 1/13/2006 9:22:00 AM
2 1 1/13/2006 6:45:00 PM
3 1 1/16/2006 9:21:00 AM
4 1 1/19/2006 9:34:00AM

[Reeezak]

DECLARE @EventPerSystem TABLE(ID INT, SystemEventID INT, [DateTime] DATETIME)

INSERT INTO @EventPerSystem
SELECT 1, 40, '1/13/2006 9:22:00AM'
UNION
SELECT 2, 40, '1/13/2006 6:45:00PM'
UNION
SELECT 3, 40, '1/16/2006 9:21:00AM'
UNION
SELECT 4, 40, '1/19/2006 9:34:00AM'
UNION
SELECT 7, 37, '1/13/2006 10:22:00 AM'
UNION
SELECT 8, 37, '1/13/2006 8:45:00 PM'
UNION
SELECT 9, 37, '1/16/2006 12:21:00 AM'
UNION
SELECT 10, 37, '1/17/2006 10:22:00 AM'


DECLARE @FunctionLog TABLE(CaseID INT, FunctionID INT, StartTime DATETIME, Duration INT, StopTime DATETIME)


INSERT INTO @FunctionLog
SELECT ID, 1, [DateTime], NULL, NULL FROM @EventPerSystem WHERE SystemEventID = 40 ORDER BY ID


UPDATE @FunctionLog SET StopTime = A.[DateTime]
FROM (SELECT ID, [DateTime] FROM @EventPerSystem WHERE SystemEventID = 37) AS A, @FunctionLog AS B
WHERE B.CaseID = A.ID - 6


UPDATE @FunctionLog SET Duration = DATEDIFF(mi, StartTime, StopTime)

SELECT * FROM @FunctionLog

[Reeezak]

这段代码其实可以一句SQL写出来，就是自己关联自己就可以了
但是我不知道lz是不是主要要拿来更新用的，所以就拆开了写

另外，这里有一个bug，即时间为“12:21:00 AM”这样的东西，我建议全部改成“12:21:00 PM”，因为AM的12点就是24小时制的0点，并不是中午12点，中午12点的表示应该为12PM

[david12359]

caseid和eventpersystemid是没关系的 B.CaseID = A.ID - 6，对于这个例子可以，但是对别的就不适用了。

我用的是
UPDATE FunctionLog
SET StopTime = e.DateTime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
然后我想用ORDER BY DATATIME，因为时间能决定顺序。但我不知道如何把ORDERBY放在UPDATE语句里
我试过
select e.Datetime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
orderby e.Datetime
顺序是没问题。关键就是如何UPDATE语句里放ORDERBY
谢谢了

[Reeezak]

UPDATE FunctionLog
SET StopTime = e.DateTime
FROM FMCV.dbo.EventPerSystem e
WHERE e.SystemEventID = 37
无论如何是不对的，因为这样的where将返回很多结果，set语句只取第一条，所以你的更新将全部为一个值

[Reeezak]

DECLARE @EventPerSystem TABLE(ID INT, SystemEventID INT, [DateTime] DATETIME)

INSERT INTO @EventPerSystem
SELECT 1, 40, '1/13/2006 9:22:00AM'
UNION
SELECT 2, 40, '1/13/2006 6:45:00PM'
UNION
SELECT 3, 40, '1/16/2006 9:21:00AM'
UNION
SELECT 4, 40, '1/19/2006 9:34:00AM'
UNION
SELECT 7, 37, '1/13/2006 10:22:00 AM'
UNION
SELECT 8, 37, '1/13/2006 8:45:00 PM'
UNION
SELECT 9, 37, '1/16/2006 12:21:00 AM'
UNION
SELECT 10, 37, '1/17/2006 10:22:00 AM'


DECLARE @Temp37 TABLE(ID INT IDENTITY(1, 1), [DateTime] DATETIME)

INSERT INTO @Temp37
SELECT [DateTime] FROM @EventPerSystem WHERE SystemEventID = 37 ORDER BY [DateTime]


DECLARE @FunctionLog TABLE(CaseID INT, FunctionID INT, StartTime DATETIME, Duration INT, StopTime DATETIME)

INSERT INTO @FunctionLog
SELECT A.ID, 1, A.[DateTime], DATEDIFF(mi, A.[DateTime], B.[DateTime]), B.[DateTime] FROM
@EventPerSystem AS A
INNER JOIN
@Temp37 AS B
ON A.ID = B.ID



SELECT * FROM @FunctionLog

这样行不？

[david12359]

问题在于在EVENTPERSYSTEM 表里，对应的开始时间和结束时间是随机储存，所以我觉得要通过
A.ID = B.ID 只能对于有序排列的才行的通。
下面的例子： 40 和37 是一对，41 和38是一对。对于这种情况，我觉得选出START（40，41），再选出结束（37，38），比较加如COUNT（Select Datetime from EventPesystem where SystemEventID=40）==Count（Select Datetime from EventPesystem where SystemEventID=37）,那就一一对应，插入到FunctionLog
但具体SQL还请教。

EventPerSystem
id SystemEventID DateTime
1 40 1/13/2006 9:22:00AM
2 40 1/13/2006 6:45:00PM
3 40 1/16/2006 9:21:00AM
4 40 1/19/2006 9:34:00AM
5 41 1/19/2006 10:34:00AM
6 41 1/19/2006 14:34:00PM
7 37 1/13/2006 10:22:00 AM
8 37 1/13/2006 8:45:00 PM
9 37 1/16/2006 12:21:00 AM
10 38 1/17/2006 10:22:00 AM
11 38 1/18/2006 9:34:00AM
12 37 1/16/2006 11:21:00 AM

谢谢