SQL中的Substract对 [英] Substract pairs in SQL

问题描述

Hello Guys，



我必须扩展现有的数据库应用程序以进行时间跟踪。数据库设计不是很好，所以我必须处理很多SQL语句。

我们有一个这样的表：

（日期= ddMMyyyy时间= hhmmss）

用户名指示日期时间< br /> 
用户1办理登机手续26012017 075598< br /> 
用户2办理登机手续26012017 075652< br /> 
用户3办理登机手续26012017 075865< br /> 
用户1退房26012017 083652< br /> 
用户3退房26012017 083743< ; br /> 
用户3办理登机手续26012017 083856< br /> 
用户1办理登机手续26012017 085536 

在工作日开始时，用户必须在用户必须结账的那天结束时检查，用户必须检查并再次办理入住手续的每一支香烟或咖啡。

我想得到两对之间的整体差异，但我不知道如何管理它。



谢谢你，

Vince



我的尝试：



总和函数，差异Funktion只有两对手工但我需要更多

解决方案

请检查以下sql查询，它将显示每个用户入住和查看详细信息

选择用户名，日期，时间作为签到，0作为结帐，0作为差异
成＃来自tablename的temp为t 
其中t.indicator ='签到'
 
 
 update t set t.checkout = isnull（（从表名中选择top 1 a.time作为a其中a.username = t.username和a.date = t.date和a.indicator ='Check-out'和a.time> t.checkin order by checkin desc），0）
来自#temp as t 
 
 update t set t.diff =（checkout-checkin）
 from #temp as t 
 
 select * from #temp 

Hello Guys,

I have to extend an existing database application for time tracking. The database design is not that nice so I have to handle a lot with SQL statements.
We have a table like this:
(Date= ddMMyyyy Time= hhmmss)

Username      Indicator      Date      Time<br />
User 1        Check-in       26012017  075598<br />
User 2        Check-in       26012017  075652<br />
User 3        Check-in       26012017  075865<br />
User 1        Check-out      26012017  083652<br />
User 3        Check-out      26012017  083743<br />
User 3        Check-in       26012017  083856<br />
User 1        Check-in       26012017  085536

at the beginning of the workday the user has to check in, at the end of the day the user has to check out, for every cigarette or coffee the user has to check out and check in again.
I want to get the overall difference between two pairs but I dont get it how to manage it.

Thank you,
Vince

What I have tried:

Sum-function, difference Funktion with only two pairs by hand but I need more

解决方案

Please check the following sql query which will show the each user check in and check out details

select username,date,time as checkin, 0 as checkout, 0 as diff 
into #temp from tablename as t
where t.indicator='Check-in'  


update t set t.checkout=isnull((select top 1 a.time from tablename as a where a.username=t.username and a.date=t.date and a.indicator='Check-out'  and a.time>t.checkin order by checkin desc),0)
from #temp as t   

update t set t.diff=(checkout-checkin)
from #temp as t 

select * from #temp

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