Zapytanie o połączenie SQL, aby wyświetlić wiersze z nieistniejącymi wierszami w jednej tabeli

Usiłuję przygotować raport dotyczący rekordów czasu pracy pracownika.

Mamy dwie tabele specjalnie na to pytanie. Pracownicy są wyszczególnieni w Memberstabeli i każdego dnia wprowadzają wpisy czasu pracy, którą wykonali i są przechowywane w Time_Entrytabeli.

Przykładowa konfiguracja z SQL Fiddle: http://sqlfiddle.com/#!3/e3806/7

Końcowy wynik, którego szukam, to tabela, która pokazuje WSZYSTKIE na Membersliście kolumn, a następnie pokazuje ich sumę godzin dla daty zapytanej w innych kolumnach.

Problem wydaje się polegać na tym, że jeśli nie ma wiersza w Time_Entrytabeli dla konkretnego elementu, to jest teraz wiersz dla tego elementu. Wypróbowałem kilka różnych typów złączeń (lewy, prawy, wewnętrzny, zewnętrzny, pełny zewnętrzny itp.), Ale żaden nie wydaje mi się tego, czego chcę, co byłoby (w oparciu o ostatni przykład w SQL Fiddle):

/*** Desired End Result ***/

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
ADavis      | 0               | 11-10-2013    | 0               | 0
BTronton    | 0               | 11-10-2013    | 0               | 0
CJones      | 0               | 11-10-2013    | 0               | 0
DSmith      | 0               | 11-10-2013    | 0               | 0
EGirsch     | 1               | 11-10-2013    | 0.92            | 1
FRowden     | 0               | 11-10-2013    | 0               | 0

Co aktualnie otrzymuję, gdy pytam o konkretną datę 11-1:

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
EGirsch     | 1               | 11-10-2013    | 0.92            | 1

Co jest poprawne w oparciu o jeden wiersz Wpisu Czasu, który jest datowany na 11-10-2013 dla EGirsch, ale muszę zobaczyć zera dla innych członków, aby uzyskać raporty i ewentualnie internetowy pulpit nawigacyjny / raport dla tych informacji.

To jest moje pierwsze pytanie i podczas wyszukiwania zapytań Join itp. Nie jestem pewien, jak mogłaby się nazywać ta funkcja, więc mam nadzieję, że nie jest to duplikat i pomoże innym również w znalezieniu rozwiązania podobne problemy.

Dziękujemy za SQLfiddle i przykładowe dane! Chciałbym, żeby więcej pytań zaczynało się w ten sposób.

Jeśli chcesz, aby wszyscy członkowie, niezależnie od tego, czy mieli wpis na tę datę, chcesz LEFT OUTER JOIN. Byłaś bardzo bliska tej wersji, jednak mała sztuczka z łączeniami zewnętrznymi polega na tym, że jeśli dodasz filtr do tabeli zewnętrznej w WHEREklauzuli, zamienisz łączenie zewnętrzne w łączenie wewnętrzne, ponieważ spowoduje to wykluczenie wierszy znajdujących się NULLpo tej stronie (ponieważ nie wie, czy NULLpasuje do filtra czy nie).

Zmodyfikowałem pierwsze zapytanie, aby uzyskać wiersz dla każdego członka:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

Zostawię to jako ćwiczenie dla czytelnika, aby wziąć je stamtąd i dodać inne kolumny, formatowanie COALESCEitp.

Inne uwagi:

• zawsze używaj prefiksu schematu podczas tworzenia obiektów i odwoływania się do nich

• zawsze używaj długości podczas konwersji na varchar itp.

• trzymaj się z dala od dwuznacznych regionalnych formatów dat, takich jak mm-dd-yyyy

• rozważ użycie aliasów, aby ułatwić czytanie zapytań. Np. Powyższe można zapisać ponownie jako:

  SELECT m.Member_ID
    ,t.Date_Start
    ,t.Hours_Actual
    ,t.Hours_Bill
  FROM dbo.Members AS m
  LEFT OUTER JOIN dbo.Time_Entry AS t
  ON m.Member_ID = t.Member_ID
  AND t.Date_Start = '20131110';
  

  ... dużo ładniej, IMHO, o ile używasz rozsądnych aliasów.

Kiedy w przeszłości miałem do czynienia z tego typu problemem, utworzyłem tabelę „liczb”, która pomaga radzić sobie z brakującymi wierszami.

Tabelę liczb utworzyłem specjalnie z myślą o datach, ponieważ:

CREATE TABLE Dates
(
    dDate DATETIME NOT NULL CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED
);

INSERT INTO Dates (dDate)
SELECT TOP(73049) DATEADD(d, -1, ROW_NUMBER() OVER (ORDER BY o.object_id)) AS dDate
FROM master.sys.objects o, master.sys.objects o1, master.sys.objects o2

Spowoduje to utworzenie tabeli z pojedynczym wierszem dla każdej daty między 1900-01-01 a 2099-12-31. Używam, TOP(73049)aby ograniczyć zakres dat wygenerowany w moim przykładzie do tych dat - jeśli pracujesz z innym zakresem dat, możesz dostosować tę liczbę.

Następnie dodaję dDatestabelę do mojego zapytania, aby wiersz był zwracany dla każdej daty w żądanym zakresie dla każdego member_id. Wynik jest następnie dołączany do Time_Entrytabeli jako taki:

SELECT MD.Member_ID,
    MD.dDate,
    T.Date_Start,
    T.Hours_Actual,
    T.Hours_Bill
FROM 
    (
        SELECT M.Member_ID, D.dDate
        FROM dbo.Dates D, dbo.Members M
        WHERE D.dDate >= '20131110' AND D.dDate < '20131112'
    ) AS MD
    LEFT JOIN dbo.Time_Entry T ON MD.Member_ID = T.Member_ID AND MD.dDate = T.Date_Start
ORDER BY MD.Member_ID, MD.dDate

Umożliwia to określenie zakresu dat dla raportu.

Możesz dodatkowo zawęzić wyniki, dodając COALESCE(...)i SUM(...)zgodnie z:

SELECT MD.Member_ID,
    MD.dDate,
    T.Date_Start,
    SUM(COALESCE(T.Hours_Actual, 0)) AS TotalHoursActual,
    SUM(COALESCE(T.Hours_Bill, 0)) AS TotalHoursBill
FROM 
    (
        SELECT M.Member_ID, D.dDate
        FROM dbo.Dates D, dbo.Members M
        WHERE D.dDate >= '20131110' AND D.dDate < '20131112'
    ) AS MD
    LEFT JOIN dbo.Time_Entry T ON MD.Member_ID = T.Member_ID AND MD.dDate = T.Date_Start
GROUP BY MD.Member_ID, MD.dDate, T.Date_Start
ORDER BY MD.Member_ID, MD.dDate

Powoduje to następujące dane wyjściowe dla przykładowych danych: