I have a problem when trying to make a query, I explain what I have:
I have some schedules for X device, to calculate the hours of operation against the hours that have faults, what I need is a query to total the hours of operation per month. Let me explain, I have a table that has the hours of operation for Monday-Friday, Saturdays, and Sundays. For example: X device works from Monday to Friday from 7:00 AM to 8:00 PM, on Saturdays from 8:00 AM to 8:00 PM and on Sundays from 8:00 AM to 12:00 PM. So the idea is to find out how many hours they should work per month, but I'm stuck in the query, I would like it to be possible to go through the whole month, that is, add up the hours worked per month.
The SCHEDULE tables is this:
CREATE TABLE [Horarios](
[COD_ATM] [int] NOT NULL,
[LV_INI] [datetime] NULL,
[LV_FIN] [datetime] NULL,
[SA_INI] [datetime] NULL,
[SA_FIN] [datetime] NULL,
[DO_INI] [datetime] NULL,
[DO_FIN] [datetime] NULL)
LV_INI = Monday to Friday Starts to work LV_FIN = Monday to Friday Stops working (so the others too) Some data:
COD_ATM LV_INI LV_FIN SA_INI
SA_FIN DO_INI DO_FIN
1 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000
2 2014-06-09 08:00:00.000 2014-06-09 16:00:00.000 2014-06-09 08:00:00.000 2014-06-09 12:00:00.000 2014-06-09 00:00:00.000 2014-06-09 00:00:00.000
3 2014-06-09 00:00:00.000 2014-06-09 23:59:00.000 2014-06-09 00:00:00.000 2014-06-09 23:59:00.000 2014-06-09 00:00:00.000 2014-06-09 23:59:00.000
4 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000 2014-06-09 07:00:00.000 2014-06-09 20:00:00.000
5 2014-06-09 08:00:00.000 2014-06-09 20:00:00.000 2014-06-09 08:00:00.000 2014-06-09 20:00:00.000 2014-06-09 08:00:00.000 2014-06-09 20:00:00.000
6 2014-06-09 07:00:00.000 2014-06-09 21:00:00.000 2014-06-09 07:00:00.000 2014-06-09 21:00:00.000 2014-06-09 07:00:00.000 2014-06-09 21:00:00.000
And the query I have is this:
/****** Promedio de horas ******/
SELECT COD_ATM,
sum(convert(int, DATEDIFF(HOUR, LV_INI, LV_FIN))*5) as LunesAViernes,
sum(convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN))) as Sabado,
sum(convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN))) as Domingo,
sum((convert(int, (DATEDIFF(HOUR, LV_INI, LV_FIN))*5) + convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) + convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)))) AS TOTAL,
sum((convert(int, (DATEDIFF(HOUR, LV_INI, LV_FIN))*5) + convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) + convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)))*4) AS TOTAL
FROM [ATMs].[dbo].[AT1203]
group by COD_ATM, LV_INI, LV_FIN
So in this query I calculate them assuming that the month has 4 weeks. What I would like to do is add according to the month that I am.
And what I get is this: It is close to the desired data but it is not what I want
COD_ATM LunesAViernes Sabado Domingo TOTAL TOTAL
1 65 13 13 91 364
2 40 4 0 44 176
3 115 23 23 161 644
4 65 13 13 91 364
5 60 12 12 84 336
6 70 14 14 98 392
I hope I made myself understood, any doubt I will be pending. I clarify that the date fields are not necessary, what I occupy of those fields is the start time and the end time respectively.
An expected example would be the following for ATM 1, September has 5 Saturdays, 4 Sundays, and 21 days of the week. So ATM1 works from Monday to Friday from 7AM to 8PM, which means that it works 13 hours a day, if we multiply it by 21 we obtain the hours it works a week (Monday to Friday) which would be 273 hours a week and there are 4 sundays (4 sundays * 13) = 52 equal to saturday = 52
Entoces el total trabajado seria:
273 los dias de la semana
52 los sabados
52 los domingos
**377 Total**. Ese seria el resultado esperado, **y en la consulta que tengo solo me salen 364**
I imagine that you will start from a range of dates to list, the following is the "easy" way to solve your problem, which basically involves calculating what day of the week is each date between the requested ranges. Let's see how it would be:
First of all
SET DATEFIRST 1
to make sure that all calculations are based on our first day of the week being a Monday. Then we set the dates to list.The following is a reformulation of your table
Horarios
, instead of having start and end dates, I have the ATM, the day of the week and the hours that correspond to it for that day, according to your example 13 from Monday to Friday, 12 on Saturdays and 16 on Sundays (add another ATM with another scheme as a test). All this just to make it a bit simpler and easier to understand, but you could easily use your tableHorarios
and generate a structure similar to this. Then comes the importance of generating our set of Dates from@FechaDesde
to@FechaHasta
and for each date we calculate what day of the week it is.There are several methods to generate sequences in SQL, in this example we use a Cartesian product of 3 queries of 10 values which will give us a total of 1000 records, that will be our maximum range of days, if we need more we must modify this query.
Finally we make the final query:
That something like this would return us
Before I told you that this was the "easy" way, because if I remember correctly there is a more direct formula to calculate how many working days, Saturdays and Sundays there are in an interval, knowing these numbers is simply a matter of multiplying the hours and adding them. If I find this other solution I add it to the answer.
Edited: As I was telling you, the other way is calculating how many days of the week there are in a date range ( This question helped a lot ), it is simpler and does not require an intermediate table.
Finally, for your example that you need the days Monday to Friday, Saturday and Sunday, you could do a join directly with this table and get how many days there are of each in the month
Finally you could have something like this:
Finally I am like this:
Being that way: