Création d'un rapport sur le temps passé par l'utilisateur qui inclut les semaines zéro heure

Question

J'ai beaucoup de mal à rédiger une requête que je pensais assez simple. J'ai une table qui enregistre le nombre total d'heures passées sur une tâche et l'utilisateur qui a déclaré ces heures. J'ai besoin de créer une requête qui renvoie le nombre d'heures qu'un utilisateur donné a facturé pour chaque semaine de l'année (y compris les semaines où aucune heure n'a été facturée).

Résultats attendus :

    |USER_ID | START_DATE | END_DATE  | HOURS |
    -------------------------------------------
    |'JIM'   | 4/28/2019  | 5/4/2019  |   6   |    
    |'JIM'   | 5/5/2019   | 5/11/2019 |   0   |
    |'JIM'   | 5/12/2019  | 5/18/2019 |   16  |

J'ai une fonction qui renvoie la date de début et de fin de la semaine pour chaque jour. J'ai donc utilisé cette fonction et l'ai jointe au tableau des tâches par date et j'ai additionné les heures. J'ai donc utilisé cette fonction et l'ai jointe à la table des tâches par date et j'ai additionné les heures. Cela me rapproche beaucoup, mais comme je joins sur la date, je me retrouve évidemment avec NULL pour l'USER_ID sur toutes les lignes d'heures nulles.

Sortie de courant :

|USER_ID | START_DATE | END_DATE  | HOURS |
-------------------------------------------
|'JIM'   | 4/28/2019  | 5/4/2019  |   6   |    
| NULL   | 5/5/2019   | 5/11/2019 |   0   |
|'JIM'   | 5/12/2019  | 5/18/2019 |   16  |

J'ai essayé plusieurs autres approches, mais à chaque fois je me heurte au même problème. Des idées ?

Schéma :

---------------------------------
|          TASK_LOG             |
---------------------------------    
|USER_ID | DATE_ENTERED | HOURS |
-------------------------------
|'JIM'   | 4/28/2019    |   6   |    
|'JIM'   | 5/12/2019    |   6   |
|'JIM'   | 5/13/2019    |   10  |

------------------------------------
|       DATE_HELPER_TABLE           |
|(This is actually a function, but I| 
| put it in a table to simplify)    | 
-------------------------------------    
|DATE | START_OF_WEEK | END_OF_WEEK |
-------------------------------------
|5/3/2019 | 4/28/2019  |  5/4/2019  |    
|5/4/2019 | 4/28/2019  |  5/4/2019  |
|5/5/2019 | 5/5/2019   |  5/11/2019 |
| ETC ...                           |

Requête :

 SELECT HRS.USER_ID
        ,DHT.START_OF_WEEK
        ,DHT.END_OF_WEEK
        ,SUM(HOURS)
    FROM DATE_HELPER_TABLE DHT
    LEFT JOIN (
        SELECT TL.USER_ID
            ,TL.HOURS
            ,DHT2.START_OF_WEEK
            ,DHT2.END_OF_WEEK
        FROM TASK_LOG TL
        JOIN DATE_HELPER_TABLE DHT2 ON DHT2.DATE_VALUE = TL.DATE_ENTERED
        WHERE TL.USER_ID = 'JIM1'
        ) HRS ON HRS.START_OF_WEEK = DHT.START_OF_WEEK
    GROUP BY USER_ID
        ,DHT.START_OF_WEEK
        ,DHT.END_OF_WEEK
    ORDER BY DHT.START_OF_WEEK

http://sqlfiddle.com/#!18/02d43/3 (note : pour cette astuce sql, j'ai converti ma fonction d'aide de date en un tableau pour simplifier)

Answer 1

Croisez les utilisateurs (en question) et incluez-les dans la condition de jointure. Utiliser coalesce() pour obtenir 0 au lieu de NULL pour les heures des semaines où aucun travail n'a été effectué.

SELECT u.user_id,
       dht.start_of_week,
       dht.end_of_week,
       coalesce(sum(hrs.hours), 0)
       FROM date_helper_table dht
            CROSS JOIN (VALUES ('JIM1')) u (user_id)
            LEFT JOIN (SELECT tl.user_id,
                              dht2.start_of_week,
                              tl.hours
                              FROM task_log tl
                                   INNER JOIN date_helper_table dht2
                                              ON dht2.date_value = tl.date_entered) hrs
                      ON hrs.user_id = u.user_id
                         AND hrs.start_of_week = dht.start_of_week
       GROUP BY u.user_id,
                dht.start_of_week,
                dht.end_of_week
       ORDER BY dht.start_of_week;

J'ai utilisé un VALUES clause ici pour lister les utilisateurs. Si vous ne voulez obtenir que les temps pour des utilisateurs particuliers, vous pouvez le faire aussi (ou utiliser une autre sous-requête, ou ...). Sinon, vous pouvez utiliser votre table d'utilisateurs (que vous n'avez pas affichée, j'ai donc dû utiliser ce substitut).

Cependant, les chiffres qui en résultent (et votre question initiale) me semblent étranges. Dans l'exercice, votre utilisateur a travaillé pour un total de 23 heures dans les task_log table. Pourtant, vos sommes dans le résultat sont 24 y 80 C'est beaucoup trop en soi et c'est encore pire si l'on tient compte du fait que l'on ne peut pas se passer de l'aide de l'Union européenne. 1 heure en task_log n'est même pas à une date figurant dans la liste des date_helper_table .

Je pense que vous obtiendrez des chiffres plus précis en adhérant à l'association. task_log et non pas ce tableau dérivé bizarre.

SELECT u.user_id,
       dht.start_of_week,
       dht.end_of_week,
       coalesce(sum(tl.hours), 0)
       FROM date_helper_table dht
            CROSS JOIN (VALUES ('JIM1')) u (user_id)
            LEFT JOIN task_log tl
                      ON tl.user_id = u.user_id
                         AND tl.date_entered = dht.date_value
       GROUP BY u.user_id,
                dht.start_of_week,
                dht.end_of_week
       ORDER BY dht.start_of_week;

Mais c'est peut-être juste moi.

Fidèle à SQL

Answer 2

http://sqlfiddle.com/#!18/02d43/65

En utilisant votre bidouille SQL, j'ai simplement mis à jour l'instruction de sélection pour prendre en compte et convertir les valeurs nulles. Pour autant que je puisse en juger, il n'y a rien dans votre message qui rende cette option non viable. Veuillez me faire savoir si ce n'est pas le cas et je mettrai à jour. (Ceci n'a pas pour but de nuire à la réponse de sticky bit, mais d'offrir une alternative)

SELECT ISNULL(HRS.USER_ID, '') as [USER_ID]
    ,DHT.START_OF_WEEK
    ,DHT.END_OF_WEEK
    ,SUM(ISNULL(HOURS,0)) as [SUM]
FROM DATE_HELPER_TABLE DHT
LEFT JOIN (
    SELECT TL.USER_ID
        ,TL.HOURS
        ,DHT2.START_OF_WEEK
        ,DHT2.END_OF_WEEK
    FROM TASK_LOG TL
    JOIN DATE_HELPER_TABLE DHT2 ON DHT2.DATE_VALUE = TL.DATE_ENTERED
    WHERE TL.USER_ID = 'JIM1'
    ) HRS ON HRS.START_OF_WEEK = DHT.START_OF_WEEK
GROUP BY USER_ID
    ,DHT.START_OF_WEEK
    ,DHT.END_OF_WEEK
ORDER BY DHT.START_OF_WEEK

Answer 3

Créez un tableau de dates comprenant toutes les dates des 100 prochaines années dans la première colonne, la semaine de l'année, le jour du mois, etc. dans la suivante.

Sélectionnez ensuite dans cette table de dates et joignez à gauche tout le reste. Utilisez la fonction isnull pour remplacer les zéros par des zéros.