34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT YDANCODE, DPTNAME, DPTCODE, I_E_DATE, 审核开始时间, 接收开始时间,
审核结束时间, 接收最后时间, number, DATEDIFF(n, 审核结束时间, 接收最后时间)
AS date1, DATEDIFF(n, 审核开始时间, 接收开始时间) AS date2
FROM (SELECT IMEDITAB.YDANCODE, DPTTAB.DPTNAME, IMEDITAB.DPTCODE,
IMEDITAB.I_E_DATE, MAX(IMEDITAB.ETRTM) AS 接收最后时间,
MAX(IMEDITAB.CMDTM) AS 审核结束时间, MIN(IMEDITAB.ETRTM)
AS 接收开始时间, MIN(IMEDITAB.CMDTM) AS 审核开始时间,
COUNT(IMEDITAB.YDANCODE) AS number
FROM IMEDITAB LEFT OUTER JOIN
DPTTAB ON IMEDITAB.DPTCODE = DPTTAB.DPTCODE
GROUP BY IMEDITAB.YDANCODE, IMEDITAB.DPTCODE, IMEDITAB.I_E_DATE,
DPTTAB.DPTNAME) DERIVEDTBL
ORDER BY I_E_DATE DESC
select col1,min(col2) col4,min(col3) col5,max(col2) col6,max(col3) col7
datediff(n,cast(min(col2) as datetime),cast(min(col3) as datetime)) col8,
datediff(n,cast(max(col2) as datetime),cast(max(col3) as datetime)) col9
from tb group by col1
--> Test Data: @T
declare @T table ([1] int,[2] varchar(10),[3] varchar(10))
insert into @T
select 10001,'10:10','10:20' union all
select 10001,'10:25','11:25' union all
select 10001,'10:45','12:00'
select * from @T
--Code
select [1],
[4]=MIN([2]),
[5]=MIN([3]),
[6]=Max([2]),
[7]=Max([3]),
[8]=DATEDIFF(mi,MIN([2]),MIN([3])),
[9]=DATEDIFF(mi,max([2]),max([3]))
from @T group by [1]
--Result
/*
1 4 5 6 7 8 9
----------- ---------- ---------- ---------- ---------- ----------- -----------
10001 10:10 10:20 10:45 12:00 10 75
*/
复杂顶.....