SQL Query Hilfe

[Salomon]

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:

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?

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

[DeddyH]

Versuch es mal so:

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 :oops: [/edit]

[Salomon]

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

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;

[Salomon]

@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

[Salomon]

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:

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;