Hi,
ich brauche schon wieder Hilfe bei einem SQL Query für meine Zeiterfassungssoftware.

Und zwar werden die Zeiten in einer Tabelle TB_AM_Hours gespeichert. Aufbau siehe Anhang.


Nun brauche ich für einen Report ein SQL das folgedes aussgibt:

markieren

Delphi-Quellcode:

Date Billable Hours Non-Billable Hrs TotalHrs
====================================================
04.03.08 7 1 8
05.03.08 x y z

Ob die Stunden anrechenbar sind, hängt von FK_ActivityTypeID ab. 1 kann vergütet werden.

Dies selektiert die TotalHrs eine Tages. Wie bekomme ich nun in die selbe Abfrage die Anzahl an Stunden pro Tag die einen von 1 abweichenden Activitätstyp haben?

markieren

SQL-Code:

Select ActivityDate, SUM(Timespan) as TotalTime
FROM TB_AM_Hours
WHERE
  FK_UserID = 1
  and ActivityDate >= '03.03.2008 00:00:00'
  and ActivityDate <= '09.03.2008 00:00:00'
  and FK_ProgramID = 1
  and FK_ActivityTypeID = 1
group by ActivityDate;

Gruß
Marcus

Versuch es mal so:

zusammenfalten · markieren

SQL-Code:

Select ActivityDate,
  SUM(CASE FK_ActivityTypeID WHEN 1 THEN Timespan ELSE 0 END) AS 'Billable Hours',
  SUM(CASE FK_ActivityTypeID WHEN 0 THEN Timespan ELSE 0 END) AS 'Non-Billable Hrs',
  SUM(Timespan) as TotalTime
FROM TB_AM_Hours
WHERE
  FK_UserID = 1
  and ActivityDate >= '03.03.2008 00:00:00'
  and ActivityDate <= '09.03.2008 00:00:00'
  and FK_ProgramID = 1
group by ActivityDate;

[edit] Sry, falsches Feld summiert [/edit]

Ah super Tipp mit dem Case / Then Habe es ein klein wenig meinen Bedürfnissen angepasst:

zusammenfalten · markieren

SQL-Code:

Select ActivityDate,
  SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END) AS 'BillableHours',
  SUM(CASE WHEN FK_ActivityTypeID <> 1 THEN TimeSpan ELSE 0 END) AS 'NonBillableHours',
  SUM(Timespan) as TotalTime
FROM TB_AM_Hours
WHERE
  FK_UserID = 1
  and ActivityDate >= '03.03.2008 00:00:00' 
  and ActivityDate <= '09.03.2008 00:00:00' 
  and FK_ProgramID = 1
group by ActivityDate;

@DeddyH: Der Weg ist ja das wichtige, und der war richtig gut.

Das ganze würde ich gerne Wochenweise gruppieren. Das bekomme ich leider immer noch nicht hin. In den folgenden Thread hatte ich damit schon einmal angefangen, jedoch habe ich mit der Methode noch Probleme mit dem Jahrewechesel. KW 1 bündelt dort Datums von Anfang des Jahre und vom Ende, was natürlich nicht gewollt ist.
Zeiten Wochenweise bündeln

Mir ist gerade aufgefallen, das ich das oben beschrieben Problem hier gar nicht habe, da ich den Zeitraum ja fest vorgebe....

Trotzdem wäre es schön noch ein SQL Query zu finden, das ohne den Zeitraum vorzugeben alle Zeiten der Woche X liefert.

Ich habe das Query mal um Überstunden und reguläre Arbeitszeit erweitert. Außerdem ist es jetzt mit einer Kalender Tabelle Wocheweise gruppiert:

zusammenfalten · markieren

SQL-Code:

Select KW, ActivityDate,
  RegularHours =
   Case WHEN SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END)
     <= 480 THEN SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END)
    ELSE 480
   end,

  OvertimeHours =
   Case WHEN SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END)
     > 480 THEN (SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END) - 480)
    ELSE 0
   end,

  SUM(CASE FK_ActivityTypeID WHEN 1 THEN TimeSpan ELSE 0 END) AS 'BillableHours',
  SUM(CASE WHEN FK_ActivityTypeID <> 1 THEN TimeSpan ELSE 0 END) AS 'NonBillableHours',
  SUM(Timespan) as TotalTime
FROM TB_AM_Hours H left join TB_AM_Calendar C on
  CONVERT(varchar(8), H.ActivityDate, 112) = CONVERT(varchar(8), C.Date , 112)
WHERE
  FK_UserID = 1
  and ActivityDate >= '03.03.2008 00:00:00' 
  and ActivityDate <= '09.03.2008 00:00:00' 
  and FK_ProgramID = 1
group by KW, ActivityDate;