Group records per day

I ended up opting for the following solution

Data

DECLARE @INI SMALLDATETIME = '20170101'
DECLARE @FIM SMALLDATETIME = '20170131'
DECLARE @TAB TABLE(
    ID INT
    ,INICIO SMALLDATETIME
    ,FIM SMALLDATETIME
    ,HORAS FLOAT
)

INSERT INTO @TAB
SELECT 333, '20170101', '20170103', 5
UNION ALL
SELECT 333, '20170102', '20170105', 1
UNION ALL
SELECT 333, '20170105', '20170107', 3
UNION ALL
SELECT 333, '20170101', '20170107', 6

Logic

;WITH sample AS (
SELECT INICIO AS dt, ID, HORAS, FIM
FROM @TAB
WHERE   (
            INICIO BETWEEN @INI AND @FIM
            OR FIM BETWEEN @INI AND @FIM
            OR (INICIO <= @INI AND FIM >= @FIM)
        )
    UNION ALL
    SELECT DATEADD(dd, 1, dt), S.ID, S.HORAS, FIM
    FROM sample s 
    WHERE DATEADD(dd, 1, dt) <= FIM
)

SELECT dt, SUM(HORAS) FROM sample
WHERE DT BETWEEN @INI AND @FIM
GROUP BY DT
ORDER BY DT
OPTION (MAXRECURSION 0);

It will not display until the 31st day but I can set up this client side (graphic component)

You can add a GROUP BY YEAR(Início) clause. Or use the two columns to group the result.

See how MySQL date and time functions work. .

You can use SUM and DAY and MONTH to take only the day for each month.

SELECT
    ID,
    DAY (INICIO) AS DIA,
    MONTH (INICIO) AS MES,
    SUM(HORAS) AS HORAS
FROM
    TABELA
GROUP BY
    DAY (INICIO),
    MONTH (INICIO)
ORDER BY
    DAY (INICIO),
    MONTH (INICIO)

SELECT
    ID,
    SUM(CASE WHEN DAY(INICIO) = 1 THEN HORAS ELSE 0 END) AS D01,
    SUM(CASE WHEN DAY(INICIO) = 2 THEN HORAS ELSE 0 END) AS D02,
    ...
    SUM(CASE WHEN DAY(INICIO) = 31 THEN HORAS ELSE 0 END) AS D31 
FROM
    TABELA

See also Pivot and Unpivot link

Each row in the input table contains the ID of an object, a range of dates, and the number of hours recorded. It is also observed that there are overlapping of dates in intervals; that is, the intervals overlap.

From each row of the input table you need to generate several rows, one for each date in 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:

Afterthis"date range" transformation into "dates", calculating the sum of the hours for each ID / day is a simple process.

To mount the third table, a form 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

Nowtogetthelayoutofthesecondtable,theusualtechniqueispivoting.

--código#2v2--informeomêseanodeclare@Mêsint,@Anoint;set@Mês=1;set@Ano=2017;--calculaoperíododeemissãodeclare@DataInicialdate,@DataFinaldate;set@DataInicial=Convert(date,'1/'+Cast(@Mêsasvarchar)+'/'+Cast(@Anoaschar(4)),103);set@DataFinal=DateAdd(day,-1,DateAdd(month,+1,@DataInicial));--withT3as(SELECTH.ID,I.dt,H.HorasfromtbHorasasHcrossapplydbo.GeraIntervalo(H.Início,H.Fim)asIwhereH.Fim>[email protected]ício<=@DataFinal),T3_Mêsas(SELECTID,day(dt)asDia,HorasfromT3wheredtbetween@DataInicialand@DataFinal)SELECTID,[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]fromT3_Mêspivot(sum(Horas)forDiain([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]))asPgo

Thefollowingfunctionswereused:

--código#3IFOBJECT_ID('dbo.GetNums')ISNOTNULLDROPFUNCTIONdbo.GetNums;GOCREATEFUNCTIONdbo.GetNums(@nASBIGINT)RETURNSTABLEASRETURNWITHL0AS(SELECT1AScUNIONALLSELECT1),L1AS(SELECT1AScFROML0ASACROSSJOINL0ASB),L2AS(SELECT1AScFROML1ASACROSSJOINL1ASB),L3AS(SELECT1AScFROML2ASACROSSJOINL2ASB),L4AS(SELECT1AScFROML3ASACROSSJOINL3ASB),L5AS(SELECT1AScFROML4ASACROSSJOINL4ASB),NumsAS(SELECTROW_NUMBER()OVER(ORDERBY(SELECTNULL))ASnFROML5)SELECTTOP(@n)nFROMNumsORDERBYn;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 data for testing.

-- 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