E

I imagine that you will start from a range of dates to list, the following is the "easy" way of solving your problem, which basically goes by calculating which day of week is every date between the requested ranges. Let's see what it would be like:SET DATEFIRST 1
DECLARE @FechaDesde DATETIME
DECLARE @FechaHasta DATETIME
SELECT @FechaDesde = '20170113'
SELECT @FechaHasta = '20170521'
First of all, SET DATEFIRST 1 to make sure all the calculations are based on our first day of the week being a Monday. Then we set the dates to list.-- Horas semanales de cada ATM
DECLARE @HorasATMSemana TABLE (
COD_ATM INT,
DiaSemana INT,
Horas INT
)
INSERT INTO @HorasATMSemana (COD_ATM, Diasemana, Horas)
VALUES (1, 1, 13), (1, 2, 13), (1, 3, 13), (1, 4, 13), (1, 5, 13), (1, 6, 12), (1, 7, 16),
(2, 1, 8), (2, 2, 8), (2, 3, 8), (2, 4, 8), (2, 5, 8), (2, 6, 8), (2, 7, 8)
The following is a reformulation of your table HorariosInstead of having start and end dates, I have the ATM, the weekday and the hours that correspond to it for that day, according to your example Monday to Friday 13th, 12th on Saturdays and 16th on Sundays (Add another ATM with another trial regimen). All this just to make it a little easier and easier to understand, but quietly you could use your table Horarios and generate a structure similar to this. Then, comes how important it is to generate our set of dates from @FechaDesde until @FechaHasta and every date we calculate that day of the week is.-- Generamos las fechas (1000 dias)
DECLARE @Fechas TABLE (
Nro INT,
DiaSemana INT,
Fecha DATETIME
)
INSERT INTO @Fechas(Nro)
SELECT (T1.NRO-1)*100 + (T2.NRO-1)*10 + T3.NRO
FROM ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T1
CROSS JOIN ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T2
CROSS JOIN ( SELECT 1 AS 'NRO' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) T3
ORDER BY T1.NRO,T2.NRO,T3.NRO
UPDATE @Fechas
SET DiaSemana = DATEPART(dw, DATEADD(DAY, Nro-1, @FechaDesde)),
Fecha = DATEADD(DAY, Nro-1, @FechaDesde)
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 need to modify this query.Finally we do the final consultation:SELECT D.COD_ATM,
SUM(CASE WHEN D.DiaSemana BETWEEN 1 AND 5 THEN D.Horas ELSE 0 END) AS 'LaV',
SUM(CASE WHEN D.DiaSemana = 6 THEN D.Horas ELSE 0 END) AS 'Sab',
SUM(CASE WHEN D.DiaSemana = 7 THEN D.Horas ELSE 0 END) AS 'Dom',
SUM(D.Horas) AS 'Tot'
FROM @Fechas F
INNER JOIN @HorasATMSemana D
ON D.DiaSemana = F.DiaSemana
WHERE F.Fecha BETWEEN @FechaDesde AND @FechaHasta
GROUP BY D.COD_ATM
That would return us something like that.COD_ATM LaV Sab Dom Tot
1,00 1.183,00 228,00 304,00 1.715,00
2,00 728,00 152,00 152,00 1.032,00
Before I told you that this was the "easy" form, because if I do not remember 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 told you the other way is by calculating how many days of the week there are in a range of dates ( https://stackoverflow.com/questions/7563069/how-to-calculate-the-number-of-tuesdays-between-two-dates-in-tsql ), is simpler and does not require an intermediate table.SELECT D.COD_ATM,
SUM(CASE WHEN D.DiaSemana BETWEEN 1 AND 5 THEN D.Horas * F.Cant ELSE 0 END) AS 'LaV',
SUM(CASE WHEN D.DiaSemana = 6 THEN D.Horas * F.Cant ELSE 0 END) AS 'Sab',
SUM(CASE WHEN D.DiaSemana = 7 THEN D.Horas * F.Cant ELSE 0 END) AS 'Dom',
SUM(D.Horas*F.Cant) AS 'Tot'
FROM @HorasATMSemana D
INNER JOIN (
SELECT 1 AS Diasemana, DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 AS Cant UNION
SELECT 2 AS Diasemana, DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 AS Cant UNION
SELECT 3 AS Diasemana, DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 AS Cant UNION
SELECT 4 AS Diasemana, DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 AS Cant UNION
SELECT 5 AS Diasemana, DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS Cant UNION
SELECT 6 AS Diasemana, DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS Cant UNION
SELECT 7 AS Diasemana, DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY, 0, @FechaDesde)/7 AS Cant
) F
ON F.Diasemana = D.Diasemana
GROUP BY D.COD_ATM
Finally, for your example you need on Monday to Friday, Saturdays and Sundays, you could join directly with this table and get how many days there are of each in the monthSET DATEFIRST 1
DECLARE @FechaDesde DATETIME
DECLARE @FechaHasta DATETIME
SELECT @FechaDesde = '20170901'
SELECT @FechaHasta = '20170930'
SELECT DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 +
DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 +
DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 +
DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 +
DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS 'LaV',
DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS 'Sab',
DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY, 0, @FechaDesde)/7 AS 'Dom'
LaV Sab Dom
21,00 5,00 4,00
Finally something like that could be left:SELECT COD_ATM,
sum(convert(int, DATEDIFF(HOUR, LV_INI, LV_FIN)) * B.LaV) as LunesAViernes,
sum(convert(int, DATEDIFF(HOUR, SA_INI, SA_FIN)) * B.Sab) as Sabado,
sum(convert(int, DATEDIFF(HOUR, DO_INI, DO_FIN)) * B.Dom) as Domingo
FROM [ATMs].[dbo].[AT1203] A
CROSS JOIN (SELECT DATEDIFF(DAY, -7, @FechaHasta)/7-DATEDIFF(DAY, -6, @FechaDesde)/7 +
DATEDIFF(DAY, -6, @FechaHasta)/7-DATEDIFF(DAY, -5, @FechaDesde)/7 +
DATEDIFF(DAY, -5, @FechaHasta)/7-DATEDIFF(DAY, -4, @FechaDesde)/7 +
DATEDIFF(DAY, -4, @FechaHasta)/7-DATEDIFF(DAY, -3, @FechaDesde)/7 +
DATEDIFF(DAY, -3, @FechaHasta)/7-DATEDIFF(DAY, -2, @FechaDesde)/7 AS 'LaV',
DATEDIFF(DAY, -2, @FechaHasta)/7-DATEDIFF(DAY, -1, @FechaDesde)/7 AS 'Sab',
DATEDIFF(DAY, -1, @FechaHasta)/7-DATEDIFF(DAY, 0, @FechaDesde)/7 AS 'Dom'
) B
GROUP BY COD_ATM, LV_INI, LV_FIN