查询语句为:
Select EmpID, CardNO,DKtime, Breakfast =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 420 and 540 Then 1
Else 0
End ,
Lunch =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 660 and 840 Then 1
Else 0
End , Supper =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 1020 and 1140 Then 1
Else 0
End
From C_K200411
where day(DKtime)>1 and day(dktime)<30
group by EmpID, CardNO, DKtime
order by day(DKtime)
最后结果如下:
CardNo DKtime BreakFast Lunch Supper
153 2004-11-29 08:08:10.000 1 0 0
153 2004-11-29 12:10:29.000 0 1 0
153 2004-11-29 13:45:52.000 0 1 0
153 2004-11-29 17:22:46.000 0 0 1
162 2004-11-29 08:03:26.000 1 0 0
162 2004-11-29 08:10:14.000 1 0 0
162 2004-11-29 12:00:33.000 0 1 0
162 2004-11-29 12:06:06.000 0 1 0
我想要得到第二次打卡的记录,不知道怎样将同一张卡在同一天的同一个时间段的不同记录区分出来,要用到那些东西
最后想得到的结果如下:
CardNo DKtime BreakFast SecondBreakFast Lunch secondlunch Supper secondsupper
卡号 消费时间 早餐打卡 是否第二次打卡 午餐 是否第二次打卡 晚餐 是否第二次打卡
各路英雄好汉帮帮忙哦
Select EmpID, CardNO,DKtime, Breakfast =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 420 and 540 Then 1
Else 0
End ,
Lunch =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 660 and 840 Then 1
Else 0
End , Supper =
Case
When (DatePart(hh,DKTime)*60 + DatePart(mi,DKTime)) Between 1020 and 1140 Then 1
Else 0
End
From C_K200411
where day(DKtime)>1 and day(dktime)<30
group by EmpID, CardNO, DKtime
order by day(DKtime)
最后结果如下:
CardNo DKtime BreakFast Lunch Supper
153 2004-11-29 08:08:10.000 1 0 0
153 2004-11-29 12:10:29.000 0 1 0
153 2004-11-29 13:45:52.000 0 1 0
153 2004-11-29 17:22:46.000 0 0 1
162 2004-11-29 08:03:26.000 1 0 0
162 2004-11-29 08:10:14.000 1 0 0
162 2004-11-29 12:00:33.000 0 1 0
162 2004-11-29 12:06:06.000 0 1 0
我想要得到第二次打卡的记录,不知道怎样将同一张卡在同一天的同一个时间段的不同记录区分出来,要用到那些东西
最后想得到的结果如下:
CardNo DKtime BreakFast SecondBreakFast Lunch secondlunch Supper secondsupper
卡号 消费时间 早餐打卡 是否第二次打卡 午餐 是否第二次打卡 晚餐 是否第二次打卡
各路英雄好汉帮帮忙哦
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货