Good day. There was a question on the design of the table. The third day I can not decide, does not go and that's it) I hope for your help. The request should return the number of working hours for a particular @IDEmpl employee between @FirstTime & @SecondTime.

Database

If you do this, it works only for graphics with id = 1, i.e. with a working time of 8 hours, because in the holiday table it is written -8 hours. And if you take a work schedule at 10 working hours, then this method is not suitable.

-- Диапозон времени, за котрый считаем рабочие часы DECLARE @FirstTime as datetime = '2014-04-01' DECLARE @SecondTime as datetime = '2014-04-30' -- id работника, нужно для сортировки его рабочих дат DECLARE @IDEmpl AS int = 6 -- Расчет времени рабочего дня, согласно графику DECLARE @ScheduleTime as int = ( Select DATEDIFF (HH, StartTime, EndTime) From Schedules AS Sch RIGHT JOIN Employees AS Empl ON Sch.IDSchedule = Empl.IDSchedules Where IDEmployee = @IDEmpl ) Select IDEmployee, LastName + ' ' + FirstName AS Name, sum(@ScheduleTime - ISNULL(H.MinusHours,0)) AS [Hours Of Work] From Employees AS E JOIN Calendar AS C ON C.Date BETWEEN E.HireDate AND ISNULL(E.DismissalDate,GETDATE()) LEFT JOIN Holidays AS H ON H.HDate = C.Date Where E.IDEmployee=@IDEmpl AND C.Date BETWEEN @FirstTime AND @SecondTime Group by IDEmployee, LastName, FirstName;

From here, how to design the weekend table correctly so that in the query the working time depends on the schedule?

  • It seems to me here either to add IDSchedule to the Holidays table, on which MinusHours will depend (8 for IDSchedule=1 , 10 for IDSchedule=2 ). Either not to look at MinusHours in this table at all (you have 8 everywhere, or are other values ​​possible?), But simply sum up the working time over a range of days, excluding weekends / holidays. - i-one
  • There can be either -8 hours, that is, a full day off, or -1 hour, i.e. Short Day - Droncom
  • one
    Then apparently add IDSchedule in Holidays . It will also provide an opportunity for different schedules to have a different set of weekends (for example, someone works on holidays, someone doesn’t, some of them work some days, some rest.) Although ... you can make a flag instead of MinusHours on Holidays - a full day off, or a shortened day, depending on it, subtract either an hour or a full duration for that day. But this is if only two options (-1 and -8/10), also this option will not give the opportunity to have a different set of weekends. In general, see what suits you best. - i-one
  • "You can make a flag instead of MinusHours on Holidays. But this is if there are only two options (-1 and -8/10)." There are two options, I think I will. Could you tell me more, please, newcomer. Is it through if to do, or case? Or where can you somehow register? - Dronkom pm
  • one
    Most likely case , or iif (appeared since SqlServer 2012). - i-one

1 answer 1

Thank you @ i-one for the answer. The right solution now looks like this. For me, it is completely suitable.

enter image description here

  • one
    Insert the SQL code better with text (you cannot copy the text from the picture, but what if someone else is useful). - i-one