mercredi 11 mars 2015

sql query to get the date difference in working hours and considering weekends the instances could happen on weekends and after working hours as well

using sql server 2005 table looks like below



Fax no Date Action Date Received Action Taken By
44358820 3/1/15 8:18 AM 3/1/15 7:12 AM hmagzoub


want to have something like this



Fax no Date Action Date Received Action Taken By ResponseTime in Hours STATE
44358820 3/1/15 8:18 AM 3/1/15 7:12 AM hmagzoub 1 OK
44386911 3/1/15 8:18 AM 3/1/15 7:20 AM hmagzoub 1 OK
44059999 3/1/15 8:26 AM 3/1/15 7:24 AM naqawi 9 LATE


I simply want to get the date diff only for weekdays here its sunday to thursday and friday saturday is off , and in work hours that is from 7am to 3pm , the dates could fall on the weekday and weekends and after working hours as well , further i want to add another column that is already there in the table called username that will identify which user processed the respective id .


My table looks like this



id, creation_date, completed_date, userid



if i could add the responsetime here then i would like to add add another function that checks if the resposetime is less than 8hrs then OK if over 8hrs then Late ... PLz help me , i tried for hours playing with the code above but could not complete the whole thing


Aucun commentaire:

Enregistrer un commentaire