Each entry table row contains the ID of an object, a date range, and the number of recorded hours. It is also observed that there are overlapping dates in the intervals; that is, the intervals overlap.
From each row of the input table it is necessary to generate several rows, one for each date of the range defined by the Start/End columns. For example, for the first row of the input table,
333 | 01/01/2017 | 03/01/2017 | 5
the following lines are generated:
After this transformation from "date range" to "dates", calculating the sum of the hours for each ID/day is a simple process.
To assemble the third table, a shape is
-- código #1 v2
-- informe o mês e ano
declare @Mês int, @Ano int;
set @Mês= 1;
set @Ano= 2017;
-- calcula o período de emissão
declare @DataInicial date, @DataFinal date;
set @DataInicial= Convert(date, '1/' + Cast(@Mês as varchar) +
'/' + Cast(@Ano as char(4)), 103);
set @DataFinal= DateAdd(day, -1, DateAdd(month, +1, @DataInicial));
--
SELECT H.ID, Day(I.dt) as Dia, sum(H.Horas) as Horas
from tbHoras as H
cross apply dbo.GeraIntervalo (H.Início, H.Fim) as I
where H.Fim >= @DataInicial
or H.Início <= @DataFinal
group by H.ID, I.dt
having I.dt between @DataInicial and @DataFinal
order by H.ID, Dia;
go
To obtain the layout of the second table, the usual technique is the pivoteamento.
-- código #2 v2
-- informe o mês e ano
declare @Mês int, @Ano int;
set @Mês= 1;
set @Ano= 2017;
-- calcula o período de emissão
declare @DataInicial date, @DataFinal date;
set @DataInicial= Convert(date, '1/' + Cast(@Mês as varchar) +
'/' + Cast(@Ano as char(4)), 103);
set @DataFinal= DateAdd(day, -1, DateAdd(month, +1, @DataInicial));
--
with T3 as (
SELECT H.ID, I.dt, H.Horas
from tbHoras as H
cross apply dbo.GeraIntervalo (H.Início, H.Fim) as I
where H.Fim >= @DataInicial
or H.Início <= @DataFinal
),
T3_Mês as (
SELECT ID, day(dt) as Dia, Horas
from T3
where dt between @DataInicial and @DataFinal
)
SELECT ID, [01], [02], [03], [04], [05], [06], [07], [08], [09], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
from T3_Mês
pivot (sum(Horas) for Dia in ([01],[02],[03],[04],[05],[06],
[07],[08],[09],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],
[27],[28],[29],[30],[31])) as P
go
The following functions have been used::
-- código #3
IF OBJECT_ID('dbo.GetNums') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT TOP (@n) n FROM Nums ORDER BY n;
GO
Source: Packing Date Intervals
and
-- código #4
CREATE FUNCTION GeraIntervalo (@D1 date, @D2 date)
returns table as
return
with Datas as (
SELECT DateAdd(day, n-1, @D1) as dt
from dbo.GetNums(DateDiff(day, @D1, @D2) + 1) as Nums
)
SELECT dt
from Datas;
go
Code to generate test data.
-- código #5
CREATE TABLE tbHoras (ID int, Início date, Fim date, Horas tinyint);
INSERT into tbHoras values
(333, '1/1/2017', '3/1/2017', 5),
(333, '2/1/2017', '5/1/2017', 1),
(333, '5/1/2017', '7/1/2017', 3),
(333, '1/1/2017', '7/1/2017', 6),
(337, '30/12/2016', '2/1/2017', 3),
(337, '1/1/2017', '3/1/2017', 2),
(337, '30/1/2017', '2/2/2017', 4);
go
"I am doing the sum of Hours by grouping by Start and End days": the values of tables 2 and 3 do not coincide with this statement. For example, for this rule day 2 would have only 1 hour but in tables 2 and 3 the value displayed is 12.
– José Diz
The interval can start in one month and end in another?
– José Diz
Yes, it can. Or even years.
– Onaiggac