none
Formula Sql calcolo ore giornaliere di un marcatempo RRS feed

  • Domanda

  • Salve il codice qui sotto esegue una select per visualizzare le ore di un marcatempo in determinate condizioni

    Esempio Output:

    Data              Ore

                         IN     OUT    IN      OUT

    23/09/2019   7:30  12:00  14:00  17:00

    24/09/2019   8:02  13:04

     quello che deve fare il codice è eseguire la differezna fra out e in di ciascun giorno ed eseguire la somma, cioè:

     12:00 - 7:30 =   04:30

     17:00 - 14:00 = 03:00  

    ed eseguire la somma 03:00+04:30=07:30 

    Quello che voglio ottenere è 

    Data              Totale

    24/09/2019    07:30

    "select Data, string_agg(Ore, '  ') as Ore  from( 
    select FORMAT(dateadd(minute, datediff(minute,0,DataCreazione) / 15 * 15, 0), 'dd/MM/yyyy', 'it-IT') as Data, CONCAT( (Case when Datepart(MINUTE, DataCreazione) > 45 
    then 
    Datepart(hour, Datacreazione) + 1 else Datepart(hour, Datacreazione) end),':' ,
    (case when DATEPART(MINUTE, DataCreazione) between 0 and 15  and Stato = 'Ingresso' then '15' 
     when DATEPART(MINUTE, DataCreazione) between 16 and 30 and Stato = 'Ingresso' then '30'  when DATEPART(MINUTE, DataCreazione)
     between 31 and 45 and Stato = 'Ingresso' then '45' else '00' end)) as Ore
     from Marcatura where 
    IdUtente = @IdUtente and(Stato = 'Ingresso' or Stato = 'Uscita') and
     cast(DataCreazione as DateTime) between cast(CONVERT(VARCHAR(10), CONVERT(date, @Start, 5), 23) as datetime)  and  cast(CONVERT(VARCHAR(10), CONVERT(date, @End, 5), 23) as datetime) ) t group by Data order by CONVERT(datetime, Data, 105) desc 


    lunedì 23 settembre 2019 16:48

Risposte

  • Ciao,

    ho cercato di riprodurre il caso, supponendo che la tabella #Marcatura abbia il seguente schema:

    USE [tempdb];
    GO
    
    DROP TABLE IF EXISTS #Marcatura;
    
    CREATE TABLE #Marcatura
    (
      [Data] DATE
      ,[Ore] TIME
      ,[IDUtente] INTEGER
      ,[Stato] VARCHAR(256)
      ,[Datacreazione] DATETIME DEFAULT GETDATE()
    );
    GO

    Inseriamo alcune marcature:

    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190923', '7:30', 1, 'Ingresso');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190923', '12:00', 1, 'Uscita');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190923', '14:00', 1, 'Ingresso');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190923', '17:00', 1, 'Uscita');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190924', '7:45', 1, 'Ingresso');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190924', '12:45', 1, 'Uscita');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190924', '8:45', 2, 'Ingresso');
    
    INSERT INTO #Marcatura
    ([Data], [Ore], [IDUtente], [Stato])
    VALUES ('20190924', '14:45', 2, 'Uscita');
    GO

    Per la differezna tra Ingressi e Uscite di ciascun giorno per utente, potresti utilizzare (di base) la funzione LEAD ecco un esempio che fa uso di CTE:

    WITH CTE_Marcature AS
    (
    SELECT
      IDUtente
      ,[Data]
      ,Ore
      ,[Next] = LEAD(Ore, 1, NULL) OVER (PARTITION BY IDUtente, [Data]  ORDER BY DataCreazione)
      ,[MinutiLavorati] = DATEDIFF(MINUTE, Ore, LEAD(Ore, 1, GETDATE()) OVER (PARTITION BY IDUtente ORDER BY DataCreazione))
      ,RNumb = ROW_NUMBER() OVER (PARTITION BY [Data], IDUtente ORDER BY DataCreazione)
    FROM
      #Marcatura
    ),
    CTE_Marcature_Giorno AS
    (
    SELECT
      *
    FROM
      CTE_Marcature
    WHERE
      ((RNumb % 2) <> 0)
    ),
    CTE_Marcature_Finale AS
    (
    SELECT
      IDUtente
      ,[Data]
      ,[OreLavorate] = CAST(DATEADD(MINUTE, SUM([MinutiLavorati]) OVER (PARTITION BY IDUtente, [Data]), '00:00:00') AS TIME(0))
      ,RNumb1 = ROW_NUMBER() OVER (PARTITION BY IDUtente, [Data] ORDER BY IDUtente, [Data])
    FROM
      CTE_Marcature_Giorno
    )
    SELECT
      IDUtente
      ,[Data]
      ,[OreLavorate]
    FROM
      CTE_Marcature_Finale
    WHERE
      (RNumb1 = 1);

    L'output restituito è questo:

    Non ho eseguito test di performance con molti dati :) la query è sicuramente migliorabile, ma è una buona base di partenza.

    Spero sia utile!

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn

    domenica 29 settembre 2019 09:44
    Moderatore