SQL查询帮助!!!!!! [英] SQL Query HELP!!!!!!

问题描述

DECLARE @Today DATETIME 
SET @Today = CONVERT(DATE,GETDATE()) 
Declare @Next datetime
--select @Today 
set @Next = Dateadd(hour,23,@today)
set @Next = DATEADD(minute,59, @Next )
set @Next =DATEADD (second,59,@Next )
--Select @Next 

select EMP.FIRSTNAME + ' ' + EMP.LASTNAME As FULLNAME, MIN(CONVERT(varchar,Eventime,14)) as TIMEIN, max(CONVERT(varchar,Eventime,14)) as TIMEOUT
from EMP inner join EVENTS on EVENTS.EMPID = EMP.ID 
where (EVENTS.EVENTIME  between @Today  and @Next )
group by EMP.FIRSTNAME, EMP.LASTNAME

朋友，

我需要上述查询的帮助.我需要计算总工作时间(TimeIN- TimeOUT)(我尝试用datediff进行操作，但它只提供数小时或仅小结或仅秒).

其次，我需要计算午餐时间.为此，我计划以这种方式放弃查询

上午时段TIME IN(最短时间)和TIME OUT(最长时间)(在00:00:01和12:59:59之间)
下午会话TIME IN(最小时间)和TIME OUT(最大时间)(在13:00:00和23:59:59之间)

为此，我尝试使用并集和子查询，但我无法:(.

在这方面的任何帮助将不胜感激.谢谢大家.

Hi Friends,

I need some help on the above query. i need to calculate total work time ( TimeIN- TimeOUT) ( i tried with datediff but it gives only hours or only mintue or only second ).

Secondly want i need is to calculate Lunch Time.For this i planned to divede the query in this way

Morning session TIME IN ( min time ) and TIME OUT (max time) ( between 00:00:01 and 12:59:59)
Afternoon session TIME IN (min time) and TIME OUT(max time) ( between 13:00:00 and 23:59:59)

for this i try using union and subquery, but i couldn''t :(.

any help in this regards will be highly appreciated. Thank you all.

推荐答案

尝试使用类似这样的东西:

Try to use something like this:

DECLARE @tInFrom DATETIME 
DECLARE @tInTo DATETIME 
DECLARE @tOutFrom DATETIME
DECLARE @tOutTo DATETIME
 
SET @tInFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 00:00:00')
SET @tInTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 12:59:59')
SET @tOutFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 13:00:00')
SET @tOutTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 23:59:59')
 
--SELECT @tInFrom AS TimeInFrom, @tInTo AS TimeInTo, @tOutFrom AS TimeOutFrom, @tOutTo AS TimeOutTo 

DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)

SET @cols = '[TimeIn],[TimeOut]'

SET @dt = 'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MIN(Eventime) AS [EventTime], ''TimeIn'' AS [Description] ' +
		'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID ' +
		'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tInFrom + ''' AND ''' + @tInTo + ''') ' +
		'GROUP BY EMP.ID ' +
		'UNION ALL ' +
		'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MAX(Eventime) as [EventTime], ''TimeOut'' AS [Description] ' +
		'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID  ' +
		'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tOutFrom + ''' AND ''' + @tOutTo + ''') ' +
		'GROUP BY EMP.ID '
--EXEC(@dt)
SET @pt = 'SELECT FULLNAME, ' + @cols + ' ' +
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (MAX(EventTime) FOR [Description] IN (' + @cols + ')) AS PT'

EXEC(@pt)

上面的示例应适用于MS SQL Server2005.请阅读有关日期的信息和时间函数 [ ^ ]与您的MS SQL Server版本相对应.

The above example should works with MS SQL Server 2005. Please, read about Date and Time functions[^] corresponding with your version of MS SQL Server.

这篇关于SQL查询帮助!!!!!!的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！