Microsoft SQL Server – Gruppierung nach Datum (Snippets)

Ein paar Zeilen SQL, um Daten nach Woche, Monat, Quartal und Jahr gruppiert zurückzugeben.

Ziel ist es, Daten summiert und gruppiert nach Datum zurückzugeben. Das Datum soll dabei ein DateTime bleiben. Angenommen, wir haben folgende Tabelle mit Daten:

Id	Date	Value
1	2014-10-01	5
2	2014-10-01	3
3	2014-10-06	76
4	2014-10-08	1
…	…	…
433	2015-04-30	6

Gruppierung nach Monat

Um nun die Daten gruppiert nach Monat auszugeben und dabei das Datum als DateTime beizubehalten kann man das Datum im Select z.B. auf den 1. des Monats setzen.

Select DateAdd(Month, DateDiff(Month, 0, Date), 0) From Table

Die Gruppierung könnte dann so aussehen:

Select 
  DateAdd(Month, DateDiff(Month,0,Date), 0) AS Date, 
  Sum(Value) as Value 
From [Table] 
Group by DateAdd(Month, DateDiff(Month,0,Date), 0) 
Order by Date

Date	Value
2014-10-01	233
2014-11-01	345
2014-12-01	75
…	…

Analog funktioniert das auch für andere Granularitäten.

Gruppierung nach Jahr

Hier setzen wir das Datum auf den 1.1. des Jahres.

Select 
  DateAdd(year, DateDiff(year,0,Date), 0) AS Date, 
  Sum(Value) as Value 
FROM [Table] 
Group by DateAdd(year, DateDiff(year,0,Date), 0) 
Order by Date

Date	Value
2014-01-01	5675
2015-01-01	10964

Gruppierung nach Quartal

Hier sorgt der datepart „quarter“ für das korrekte Ergebnis.

select 
  DateAdd(quarter, DateDiff(quarter,0,Date), 0) AS Date, 
  Sum(Value) as Value 
FROM [Table] 
Group by DateAdd(quarter, DateDiff(quarter,0,Date), 0) 
Order by Date

Date	Value
2014-10-01	5675
2015-01-01	3452
2015-04-01	7512

Gruppierung nach Woche

Das funktioniert auch mit Woche.

Select 
  DateAdd(week, DateDiff(week,0,Date), 0) AS Date, 
  Sum(Value) as Value 
FROM [Table] 
Group by DateAdd(week, DateDiff(week,0,Date), 0) 
Order by Date

Date	Value
2014-10-06	133
2014-10-13	7
2014-10-20	206
…	…

Formatierung

Da das Datum weiterhin als DateTime-Object zurückgegeben wird, kann die Formatierung individuell im Code erfolgen. Hier eine Beispielmethode in C#.

public string Format(DataGranularity g, DateTime value)
{
    switch (g)
    {
        case HighchartsBuilderEnums.HighChartsDataGranularity.Week:
            return "W" + GetWeekOfYear(value) + " " + (value.ToString(" yyyy"));
        case HighchartsBuilderEnums.HighChartsDataGranularity.Month:
            return value.ToString("MMM yyyy");
        case HighchartsBuilderEnums.HighChartsDataGranularity.Quarter:
            return "Q"+GetQuarter(value) +" " +(value.ToString(" yyyy"));
        case HighchartsBuilderEnums.HighChartsDataGranularity.Year:
            return value.ToString("yyyy");
    }
    return value.ToShortDateString();
}
public static int GetQuarter(DateTime date)
{
    int[] quarters = new int[] { 4, 4, 4, 1, 1, 1, 2, 2, 2, 3, 3, 3 };
    return quarters[date.Month - 1];
}
public static int GetWeekOfYear(DateTime date)
{
    System.Globalization.CultureInfo cul = System.Globalization.CultureInfo.CurrentCulture;
    int weekNum = cul.Calendar.GetWeekOfYear(
        date, 
        System.Globalization.CalendarWeekRule.FirstFourDayWeek, 
        DayOfWeek.Monday);

    return weekNum;
}

Links

DateAdd-Funktion in der MSDN
DateDiff-Funktion in der MSDN

 

Ich hoffe wie immer, diese erstbeste Lösung war hilfreich.