Problem bei Abfrage abhängig von Datumseinträgen

[Ruediger007]

Hallo erstmal!

Ich habe ein Problem bei dem ich einfach nicht weiterkomme.

Ich berechne Überstunden für Mitarbeiter. Ich habe die Tabelle jetzt für einen Mitarbeiter generiert, so dass wir uns über die Selektion nicht kümmern müssen. Dabei ist die Anzahl der Überstunden abhängig von dem jeweils aktuellem Vertrag.

Jetzt habe ich folgende Tabelle:

CREATE TABLE #test(WorkerID int,
    WorkerName varchar(50),
    TimeWorked int,
    TimeToWork int,
    ContractStartDate datetime,
    DateOfEntry datetime
)
--------------------------------------------------------------------------------------------------------------------- ContractStartDate, DateOfEntry
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 8, '2013-01-01', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 6, '2013-08-15', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 8, '2013-01-01', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 6, '2013-08-15', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 7, 8, '2013-01-01', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 6, 4, '2013-04-15', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 6, 6, '2013-08-15', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 4, 8, '2013-01-01', '2013-04-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-04-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 4, 6, '2013-08-15', '2013-04-16');

Was jetzt passieren muss ist folgendes, ich muss checken, zu welchem Vertragsanfangsdatum ein Eintrag in der Tabelle gehört und dann die Überstunden berechnen und summieren. Raus kommt dann einfach eine Zahl mit der ich dann weiterarbeite. Ich war natürlich nicht untätig. Was ich mir überlegt habe ich sowas wie:

Zuerst wird die Tabelle partitioniert nach Tag und dann wird jedem Eintrag ein aufsteigender Wert zugewiesen:

select ID, Name,(sum(time_worked)-sum(time_to_work)) as 'overtime'
from (
Select *,
ROW_NUMBER() over (partition by Date_of_Entry order by Contract_Start_Date asc) as seqnum
from MyTable  where Contract_Start_Date <= Date_of_Entry
)

Dann müsste man jedem Eintrag, der kleiner ist als das nächst folgende Vertragsdatum ebenfalls eine laufenden Nummer zuweisen.

Das würde dann so aussehen:

h**ps://www.dropbox.com/s/x18p8e8xlczsmuy/test.PNG

Würde man nun Where seqnumber = conseqnumber ausführen, hätte man das Ergebnis, denke ich. Ich kann es nur nicht umsetzen. Könnte mir dabei vielleicht jemand helfen oder hat vielleicht sogar eine bessere idee?

Viele Grüße

Rüdi

 

[Ruediger007]

Habe es selbst hinbekommen. Lösung sieht aber leichter aus als es ist

select *
from (
Select *,
ROW_NUMBER() over (partition by DateOfEntry order by ContractStartDate desc) as seqnum
from #test  
where ContractStartDate <= Date_of_Entry)
#test
where seqnum = 1
)