Help me with SQL query

[neerajm575]

I am strugling to write a MS access SQL query. I have sepeate fields for date and time being captured. I want to write a query which helps me to calculate TAT. For eg if i received JE on 2/11/2010 at 07:00Am and submit the same at 10AM on same day then it should be 3 hours or 180Min. but if i received inputs after 3:00Pm then it should take next day 7Am as input received time ( working hours: 7:00AM to 4:00PM). Futher adding to this, If there are weekends or holidays in between that time should not be considered in calculating TAT..

Please help ..



Respond me at neerajm575@gmail.com

[weekend00]

You need a table holding alll the day of holiday and weekend days, let's say the table is: Happyday
field: dday date/time

query would like this (duration is in hour):

SELECT pName, 24*([daySubmit]-[dayReceive]+[timeSubmit]-[timeReceive]-DCount("*","HappyDay","dday < #" & [daySubmit] & "# AND dday >#" & [dayReceive] & "#")) AS Duration FROM MyTable;

[neerajm575]

You need a table holding alll the day of holiday and weekend days, let's say the table is: Happyday
field: dday date/time

query would like this (duration is in hour):

SELECT pName, 24*([daySubmit]-[dayReceive]+[timeSubmit]-[timeReceive]-DCount("*","HappyDay","dday < #" & [daySubmit] & "# AND dday >#" & [dayReceive] & "#")) AS Duration FROM MyTable;

thanks weekend 00 for this great help.. but i want to include some thing else in to this query:

Please let me know if above query will take care of normal working business hours. for eg daily working business hours is 7:00AM to 03:00PM. Anything received after 3:00PM should be considered as received as next working day as 7:00AM.

Thanks
Neeraj