SQL Gruppe Distinct Count

Question

Ich habe die folgende Tabelle

User-ID    Startdatum   Enddatum
-------------------------------------
John Doe   11. März 2011  28. Mai 2011
Robret S   21. März 2011  29. Juni 2011
Tina T     01. Februar 2011  20. August 2011

Ich möchte zeigen, wie viele Personen ich in den letzten 6 Monaten zur Verfügung habe, auch wenn der Monat keine Personen hat. Wie kann das möglich sein? Ich weiß, dass ich Gruppierung machen muss und distinct count verwenden muss.

Erwartete Ausgabe:

Februar  = 1 Ressource
März     = 3 Ressourcen
April     = 3 Ressourcen
Mai       = 3 Ressourcen
Juni      = 2 Ressourcen
Juli      = 1 Ressource
August    = 1 Ressource

Answer 1

Mit Kalender Als 
    (
    Wählen Guss('20110501' Als DateTime) Als [Datum]
    Union Alle 
    Wählen DateAdd(m,-1,[Datum])
    Von Kalender
    Wo [Datum] > DateAdd(m,-5,'20110501')
    )
Wählen DateName(m, C.Datum) + ' ' + Guss(Jahr(C.Datum) Als char(4))
    , Fall Zählen(*)
        Wenn 1 Dann Guss(Zählen(*) Als varchar(10)) + ' Ressource'
        Sonst Guss(Zählen(*) Als varchar(10)) + ' Ressourcen'
        Ende
Von Kalender Als C
    Links Verbinden MeineTabelle Als T
        An C.Datum Zwischen T.StartDatum Und T.EndDatum
Gruppieren Nach C.Datum

Ergebnisse:

Dezember 2010   | 1 Ressource
Januar 2011     | 1 Ressource
Februar 2011    | 1 Ressource
März 2011       | 1 Ressource
April 2011      | 3 Ressourcen
Mai 2011        | 3 Ressourcen

Answer 2

Sie benötigen die vorhandenen Datensätze der letzten 6 Monate, damit Sie die beiden Datensätze zusammenführen können. Sie können die letzten 6 Monate in einem CTE generieren und ein LEFT JOIN mit Ihren Daten durchführen. Dadurch können Sie die letzten 6 Monate anzeigen, auch wenn Sie keine Daten haben.

Answer 3

Ich glaube nicht, dass du das, was du willst, mit einem "einfachen" SELECT-Statement machen kannst (auch wenn du GROUPing usw. verwendest). Folgendes ist mir spontan eingefallen, also musst du ein wenig damit experimentieren und idealerweise Joe Celkos ausgezeichnetes SQL for Smarties Buch lesen.

Du musst eine zweite Tabelle erstellen, die alle deine Monate (Start-/Enddaten) enthält. Du benötigst nur eine Tabelle für alle Arten ähnlicher Abfragen, und sie muss alle Monate in den Datumsbereichen enthalten, die dich interessieren:

CREATE TABLE months (id, start DATE, end DATE);

INSERT INTO months (id, start, end) 
          values ( (1, 2011-01-01, 2011-01-31), 
                   (2, 2011-02-01, 2011-02-28), ...);

Dann führst du ein LEFT OUTER JOIN von deiner Benutzer-Tabelle zu dieser Monatstabelle durch. Das gibt dir eine Zeile für jeden Benutzer für jeden Monat, in dem sie verfügbar waren, die du wie erforderlich gruppieren kannst:

SELECT months.id, COUNT(user.id) 
  FROM months LEFT OUTER JOIN users 
         ON user.start_date < months.end 
        AND user.end_date > months.start 
GROUP BY months.id;

Hoffe, das hilft.

Answer 4

MIT Ressourcen ALS (
  AUSWÄHLEN
    Datum = DATEADD(monat, v.number, [Startdatum])
  VON atable t
    INNER JOIN master.dbo.spt_values v AN v.typ = 'P'
      UND v.nummer ZWISCHEN 0 UND DATEDIFF(monat, t.[Startdatum], t.[Enddatum])
)
AUSWÄHLEN
  Monat = DATENAME(monat, Datum),
  Ressourcenanzahl = CAST(COUNT(*) AS varchar(30)) +
                  FALLS COUNT(*) WENN 1 DANN ' Ressource' SONST ' Ressourcen' END
VON ressourcen
WO Datum > DATEADD(monat, -6, DATEADD(tag, -TAG(GETDATE()), GETDATE()))
GRUPPIEREN NACH JAHR(Datum), MONAT(Datum), DATENAME(monat, Datum)
SORTIEREN NACH JAHR(Datum), MONAT(Datum)