1

I want to create a basic case logging system and when somebody opens a new issue, the issue is assigned a Sr_number with a given number of hours. For example Sr_number 1 is 4 hours, 2 is 6 hours, 3 is 8 hours and 4 is 24 hours.

Now adding hours onto a time stamp is easy but the catch is I need to take into account working hours which are 09:00 to 17:00 Monday to Friday.So if a case is given a 12 hour Sr_number and the deadline for this falls at 16:00 on a week day then the deadline is extended to the next working day. Basically the deadline is 12 working hours.And calculation should be 1 hour worked for the issue logged on same day and remaining 11 hours to next working day.

If in case it is sun, it should consider directly go to monday.

Example:

Case created on: 10/06/2015 12:04:39 PM- with Sr_number 1 (12 Hours) Deadline is now: 10/07/2015 12.05 PM Make sense?

Another catch is I need to take into account hours On Hold and these two have to be only within working hours. For some case,saturdays is working ,for some its holiday.

enter image description here

enter image description here

How should i proceed. I tried performing datepart,dateadd and datediff functions.But i could find only weekday. I am new at sql.Please guide for the same

NoNaMe
  • 6,020
  • 30
  • 82
  • 110
tbs
  • 57
  • 1
  • 8
  • 6
    Tag dbms used. (Many of them are far from ANSI SQL compliant when it comes to date/time.) – jarlh Nov 30 '15 at 12:38
  • 5
    Your question sounds as not related with `c#` to me. – Soner Gönül Nov 30 '15 at 12:39
  • Dupe? http://stackoverflow.com/q/5274208/1305911 – JNF Nov 30 '15 at 12:55
  • 1
    We have a similar thing at our company by calculating stages in delivery lead time. We have a table for each user and their working hours then use a couple of BETWEEN statements to check if the deadline is within that day, if it is not, then it takes 1 day away and repeats. I can post example if wished for but this is very specific to your business. If you are able to add table design I would suggest a similar type of restructure holding each employees working hours maybe in groups to simplify the comparison process. – A. Greensmith Nov 30 '15 at 13:17
  • It sounds like you need a calendar table. Such a table (in its most basic form) has one row per day and describes whether its a working day or not and any other useful parameters. If different teams have different work patterns, you would generally expand to one row per team per day. Either way, you build this table which can easily have 20 years worth of calendar stored in it and then join it to your queries that need to work out durations. – Damien_The_Unbeliever Nov 30 '15 at 13:36
  • Yea,I have just created the sample table.I can group by customers.What I am not getting is how to add working hours to issue logged in.@ GreenSmith – tbs Dec 01 '15 at 04:15
  • Sorry i had uploaded wrong table before. – tbs Dec 01 '15 at 04:18
  • None of your priorities is 12-hour in the first paragraph yet you refer to it multiple times. At the end you say 12 hours is Priority 1 which I thought was 4 hours?? – shawnt00 Dec 01 '15 at 04:27
  • For abc record,my time is 12 hours ,that is my deadline. @ shawnt00 – tbs Dec 01 '15 at 04:52
  • So which DBMS are you using? Postgres? Oracle? –  Dec 01 '15 at 06:24
  • sql server 5.0 @ a_horse – tbs Dec 01 '15 at 08:29

0 Answers0