现有下面一个原始表和两个查询sql语句
想得到只入未出或只出未入的卡号并通过系统时间判断已经超过多久
ticket=4为入 ticket=20为出
下面分别为查出只入未出到现在过多久记录,及判断一张卡唯一即未入或未出。想将两个sql查出的结果合并 不知怎样做。
或用其它方法实现所要求结果。
原始表
可查出离最近多久时间sql 但会漏掉唯一的只入未出记录
select
tt.Workshop,
tt.InTicket,
CONVERT(VARCHAR(24),tt.InTime,120)AS InTime,
tt.OutTicket,
CONVERT(VARCHAR(24),tt.OutTime,120)AS OutTime,
tt.Card#,
case when
tt.NotExitCard3hours =tt.NotExitCard3hours
or tt.NotExitCard4hours =tt.NotExitCard4hours
or tt.NotExitCard5hours =tt.NotExitCard5hours
or tt.NotExit1day=tt.NotExit1day
or tt.NotExitCard2day = tt.NotExitCard2day
or tt.NotExitCard3day = tt.NotExitCard3day
then tt.Workshop end as notexitworkshop,
tt.NotExitCard3hours,
tt.NotExitCard4hours,
tt.NotExitCard5hours,
tt.NotExit1day,
tt.NotExitCard2day,
tt.NotExitCard3day
from(
select
getOut.Workshop,
getin.Card#,
getin.InTicket,
getin.InTime,
getOut.OutTicket,
getOut.OutTime,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=3
then getOut.Card# end as NotExitCard3hours,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=4
then getOut.Card# end as NotExitCard4hours,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=5
then getOut.Card# end as NotExitCard5hours,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=1
then getOut.Card# end as NotExit1day,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=2
then getOut.Card# end as NotExitCard2day,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=3
then getOut.Card# end as NotExitCard3day
from(
select
tk.card#,
tk.workshop,
case when tk.Ticket#=4
then tk.Ticket# end as InTicket,
case when tk.Ticket#=4
then min(tk.[Receive Time])end as InTime
from Test.dbo.TicketDetailData125 tk
group by
tk.card#,
tk.workshop,
tk.Ticket#
) as getin,
(
select
tk.card#,
tk.workshop,
case when tk.Ticket#=20
then tk.Ticket# end as OutTicket,
case when tk.Ticket#=20
then max(tk.[Receive Time])end as OutTime
from test.dbo.TicketDetailData125 tk
group by
tk.card#,
tk.workshop,
tk.Ticket#
)as getOut
where
getin.Card#=getOut.Card#
and getOut.OutTime>getin.InTime
and getOut.OutTicket=20
)as tt
where tt.Workshop=tt.Workshop
order by
tt.Workshop
这是可查出那张卡是唯一的即只入或其它状况记录