none
trovare i giorni in cui i dipendenti non hanno inserito le ore lavorative. RRS feed

  • Domanda

  • ciao,

    Ciao ho una tabella [tbl_consuntivi] dove gli operai [tbl_personale] devrebbero inserire giorno per giorno il numero di ore che lavorano durante ogni giornata lavorativa.
    Capita che per dimenticanza gli operai non si ricordino anche per più di un giorno di fila di aggiungere una riga per una giornata. Poi quando ricominciano a segnare le ore non si ricordano di compilare i giorni che in passato non avevano compilato.

    Ho bisogno di una query che esamini la tabella tbl_consuntivi e UTENTE PER UTENTE restituisca UNA riga PER OGNI giorno lavorativo (giorni diversi da sabato e domenica) in cui l'utente NON ha inserito le ore lavorative della giornata. Limitando la ricerca per esempio agli ultimi 60giorni in dietro rispetto alla data corrente.

    Di seguito lo script per le tabelle di esempio nel tempdb e qualche dato di esempio.

    grazie mille , marco

    USE

     

    tempdb

    GO

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    CREATE

     

    TABLE [dbo].[tbl_Personale](

    [IDPersonale] [int]

    IDENTITY(1,1) NOT NULL,

    [Nome] [nvarchar]

    (50) NULL,

     

    CONSTRAINT [PK_tbl_Personale] PRIMARY KEY CLUSTERED

    (

    [IDPersonale]

    ASC

    )

     

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

     

    ON [PRIMARY]

    GO

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    CREATE

     

    TABLE [dbo].[tbl_consuntivi](

    [IDConsuntivoOre] [int]

    IDENTITY(1,1) NOT NULL,

    [OperatoreID] [int]

    NULL,

    [OreLavorate] [int]

    NULL,

    [Data] [datetime]

    NULL,

     

    CONSTRAINT [PK_tbl_consuntivi] PRIMARY KEY CLUSTERED

    (

    [IDConsuntivoOre]

    ASC

    )

     

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

     

    ON [PRIMARY]

    GO

    ALTER

     

    TABLE [dbo].[tbl_consuntivi] WITH CHECK ADD CONSTRAINT [FK_tbl_consuntivi_tbl_Personale] FOREIGN KEY([OperatoreID])

    REFERENCES

     

    [dbo].[tbl_Personale] ([IDPersonale])

    GO

    ALTER

     

    TABLE [dbo].[tbl_consuntivi] CHECK CONSTRAINT [FK_tbl_consuntivi_tbl_Personale]

    GO

    INSERT

     

    INTO [dbo].[tbl_Personale] ([Nome]) VALUES ('Marco');

    INSERT

     

    INTO [dbo].[tbl_Personale] ([Nome]) VALUES ('Luigi');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (1,8,'01/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (1,8,'04/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (1,8,'05/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (1,8,'04/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (1,8,'08/01/2010');

     

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (2,8,'01/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (2,8,'04/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (2,8,'05/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (2,8,'07/01/2010');

    INSERT

     

    INTO [dbo].[tbl_consuntivi] ([OperatoreID],[OreLavorate],[Data]) VALUES (2,8,'08/01/2010');


    lunedì 8 febbraio 2010 15:52

Risposte

  • Ciao Marco,

    puoi fare così:
    DECLARE @Data Datetime
    SET @Data = CONVERT(Datetime,CONVERT(CHAR(8),GETDATE(),112));
    SET DATEFIRST 1;
    
    WITH Giorni AS
    (SELECT @Data AS Giorno
     UNION ALL
     SELECT Giorno-1
     FROM   Giorni
     WHERE Giorno > @Data -59
    )
    SELECT P.Nome,G.Giorno
    FROM   [dbo].[tbl_Personale] AS P
       CROSS JOIN Giorni AS G
       LEFT JOIN  [dbo].[tbl_consuntivi] AS C
               ON P.IDPersonale = C.OperatoreID AND
                  C.Data BETWEEN @Data-59 AND @Data AND
                  G.Giorno = C.Data 
    WHERE  C.Data IS NULL 
      AND  Datepart(dw,G.Giorno) NOT IN (6,7)
    ORDER BY P.IDPersonale,G.Giorno
    

    Ciao
    Giorgio Rancati
    lunedì 8 febbraio 2010 20:36
    Moderatore

Tutte le risposte

  • Ciao Marco,

    puoi fare così:
    DECLARE @Data Datetime
    SET @Data = CONVERT(Datetime,CONVERT(CHAR(8),GETDATE(),112));
    SET DATEFIRST 1;
    
    WITH Giorni AS
    (SELECT @Data AS Giorno
     UNION ALL
     SELECT Giorno-1
     FROM   Giorni
     WHERE Giorno > @Data -59
    )
    SELECT P.Nome,G.Giorno
    FROM   [dbo].[tbl_Personale] AS P
       CROSS JOIN Giorni AS G
       LEFT JOIN  [dbo].[tbl_consuntivi] AS C
               ON P.IDPersonale = C.OperatoreID AND
                  C.Data BETWEEN @Data-59 AND @Data AND
                  G.Giorno = C.Data 
    WHERE  C.Data IS NULL 
      AND  Datepart(dw,G.Giorno) NOT IN (6,7)
    ORDER BY P.IDPersonale,G.Giorno
    

    Ciao
    Giorgio Rancati
    lunedì 8 febbraio 2010 20:36
    Moderatore
  • Grazie Giorgio,
    mi piacerebbe anche capire come funziona.
    Ti chiedo troppo se chiedo di spiegarmelo?
    Vedere la query tutta insieme cosi è troppo difficile per me per poterla capire.

    grazie per una eventuale risposta.

    ciao,
    Marco Bosco
    martedì 9 febbraio 2010 07:52
  • Ciao Marco,

    partiamo da:
    WITH Giorni AS
    (SELECT @Data AS Giorno
     UNION ALL
     SELECT Giorno-1
     FROM   Giorni
     WHERE Giorno > @Data -59
    )
    SELECT P.Nome,G.Giorno
    FROM   [dbo].[tbl_Personale] AS P
       CROSS JOIN Giorni AS G
    

    è composta dalla cte ricorsiva Giorni che rende 60 righe contenenti le date dalla data odierna alla data odierna - 59 giorni.
    La cte Giorni viene messa in cross Join con tbl_Personale, in questo modo otteniamo che ogni IdPersonale è ripetuto 60 volte con la data che va da oggi a oggi - 59 giorni.

    Questo risultato lo mettiamo in Left Join con la tabella tbl_consuntivi
    LEFT JOIN  [dbo].[tbl_consuntivi] AS C
               ON P.IDPersonale = C.OperatoreID AND
                  G.Giorno = C.Data 
    

    ottenendo sempre 60 righe per ogni IDPersonale, con il campo Data valorizzato solo se il suo valore è uguale a Giorno, in altro caso Data sarà valorizzato a NULL

    ora basta mettere il filtro
    WHERE  C.Data IS NULL 
      AND  Datepart(dw,G.Giorno) NOT IN (6,7)
    

    per avere solo le righe con Data valorizzata a Null, quindi quelle non registrate,  e che abbiano Giorno diverso da sabato e domenica.

    Nell'esposizione ho tralasciato il filtro nella ON della left join
    C.Data BETWEEN @Data-59 AND @Data AND
    perchè non è determinante ai fini del risultato, serve per filtrare preventivamente le righe estratte da tbl_Consuntivi per allegerire la left join. Se la tabella tbl_Consuntivi ha un indice composto da IdOperatore e Data allora questa riga non serve perchè verrà sfruttato l'indice.

    Ciao
    Giorgio Rancati
    martedì 9 febbraio 2010 10:36
    Moderatore
  • ciao,
    grazie della risposto.
    C'è un piccolo malfunzionamento.
    Credo che sia dovuto al fatto che le date inserite nella tbl_consuntivi contiene anche le ore minuti e secondi e quindi non matcha bene con le date generate dalla cte.

    come posso risolvere il problema?

    grazie.

    Marco Bosco
    martedì 9 febbraio 2010 15:57
  • Ciao Marco,

    modifica la on della left join attuale da così
    ON P.IDPersonale = C.OperatoreID AND
       C.Data BETWEEN @Data-59 AND @Data AND
       G.Giorno = C.Data 
    

    a così
    ON P.IDPersonale = C.OperatoreID AND
       C.Data BETWEEN @Data-59 AND CONVERT(CHAR(8),@Data,112)+' 23:59:59.997' AND
       C.Data BETWEEN G.Giorno AND CONVERT(CHAR(8),G.Giorno,112)+' 23:59:59.997'
    

    Come detto sopra, se hai un indice composto da IdOperatore e Data su tbl_Consuntivi, togli la seconda riga.

    Ciao
    Giorgio Rancati
    martedì 9 febbraio 2010 16:19
    Moderatore