none
i am working on a code for this scenario An associate who worked over a 10 hours span within a 24 hour period (EX- they work from 10am -12 pm and then come back in from 6pm-10pm- that would be 6 hours worked over a 12 hour span) RRS feed

  • Question

  •  I am beginner in sql sever 

    in my present task i  struck at a point where my ideas got drained so i looking for some hints to move forward

    my task is 

    1) if one person with empid XXXX  worked multiple shifts in a perticular day and my task is to find out the gap between the shifts is < or > 1hr and if it is <1hr i want to raise a flag as 0 and 1 for >1hr 

    this is the code which i am working 

    SELECT  DISTINCT X.PERSONID,X.PERSONNUM, X.PERSONFULLNAME, X.STOREID, X.SPECIFICDAY, X.HoursWorked,X.PUNCHIN, X.PUNCHOUT

     FROM ( SELECT  P.PERSONID,P.PERSONNUM, P.PERSONFULLNAME, P.HOMELABORLEVELNM5 AS 'STOREID',CAST(T.EVENTDATE AS VARCHAR(11)) AS 'SPECIFICDAY',
     RIGHT(T.STARTDTM,8) AS 'PUNCHIN', RIGHT(T.ENDDTM,8) AS 'PUNCHOUT',
    T.TIMEINSECONDS / 3600.00 AS 'HoursWorked'
     from VP_EMPLOYEEV42 P
     INNER JOIN  VP_TIMESHEETITMV42 T ON P.EMPLOYEEID = T.EMPLOYEEID
     WHERE  
     T.EVENTDATE >= '2016-12-01'
     AND P.EMPLOYMENTSTATUS = 'ACTIVE'
     AND P.HOMELABORLEVELNM5 IN ('8166','8111','8168','8171','8175','8197','8233','8236',
     '8255','8267','8313','8320','8325','8328''8331','8333','8334','8335','8336','8337',
     '8339','8340','8341','8344','8346','8348','8359','8360','8361','8362','8363','8364',
     '8365','8366','8367','8368','8369','8370','8372','8374','8375','8376','8377','8379',
     '8380','8386','8391','8392','8393','8394','8395','8396','8413','8415','8416','8427',
     '8444','16992','21036','21043','21066','21067','69902','71021','72046')

     GROUP BY P.PERSONID,P.PERSONNUM, P.PERSONFULLNAME, P.HOMELABORLEVELNM5, T.EVENTDATE,T.STARTDTM,T.ENDDTM,T.TIMEINSECONDS)X
    GROUP BY X.PERSONID,X.PERSONNUM, X.PERSONFULLNAME, X.STOREID, X.SPECIFICDAY,X. HoursWorked, X.PUNCHIN, X.PUNCHOUT 
      ORDER BY X.STOREID

    the output i got for this code is 

    

      



     
    Thursday, May 16, 2019 2:35 PM