查询语句为:
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
卡号     消费时间  早餐打卡   是否第二次打卡    午餐  是否第二次打卡  晚餐   是否第二次打卡
各路英雄好汉帮帮忙哦