SQL server: Auswählen eines Jahres des Kontos basierend auf einem bestimmten date und einem datesbereich

Ich muss einige Werte auf ein Geschäftsjahr verteilen, das am 1. Dezember beginnt und am 30. November jedes Jahr endet.

Die Zeilen, die die Wertefelder enthalten, befinden sich in einer Tabelle (TABELLE A), die eine referencenummer und ein Ereignisdatum hat

Tabelle A

referencenummer, Wert, IncidentDate

1, 10.00, 01/12/14

2, 15.00, 10/05/13

3, 20.00, 14.10.13

TABELLE A ist die Verbindung zu TABELLE B, die ebenfalls die referencenummer hat und Transaktionsdaten einschließlich eines Startdatumfeldes enthält. Jede referencenummer kann mehrere transactions mit unterschiedlichen Startdatumwerten haben und das Ziel ist es, sicherzustellen, dass die aus TABELLE B ausgewählte Zeile diejenige ist, bei der das Startdatum das jüngste Startdatum vor dem Ereignisdatum aus Tabelle A ist

TABELLE B

referencenummer, StartDate

1, 01/05/14

1, 01/05/15

2, 12/04/14

2, 12/04/15

3, 05/06/14

3, 04/06/15

TABELLE C ist eine timetabelle, die bestimmte Termine zu den Geschäftsjahren verteilt.

TABELLE C date, Geschäftsjahr

30/11/14, FY2013 / 14

01/12/14, FY2014 / 15

Ich versuche, eine Abfrage zu erstellen, die Tabelle A zu Tabelle B auf der referencenummer und dem Aufnahmedatum zum Startdatum wie oben beschrieben verbindet und dann den FinancialYear-Wert basierend auf dem Startdatum aus Tabelle B addiert.

Ich kämpfe darum, um das richtige Geschäftsjahr zurückzugeben.

Darüber hinaus ist die dataqualität schlecht, so dass es viele Beispiele gibt, bei denen das Incident-date aus Tabelle A größer ist als der scope des Geschäftsjahres, das auf der Grundlage des Startdatums aus Tabelle B ausgewählt wurde.

Ich muss in der Lage sein, entweder das entsprechende Geschäftsjahr auf der Grundlage des Startdatums oder, falls dies nicht der Fall ist, dem Geschäftsjahr, das dem Vorfallsdatum entspricht, zurückzugeben

Hier ist der Code, den ich derzeit habe:

SELECT a.ReferenceNumber, b.StartDate, c.FinancialYear FROM dbo.TableA a INNER JOIN dbo.TableB b ON a.ReferenceNumber = b.ReferenceNumber AND b.StartDate = (SELECT MIN(StartDate) FROM dbo.TableB WHERE a.IncidentDateTime > StartDate AND ReferenceNumber = a.ReferenceNumber) INNER JOIN dbo.Calendar c ON rdc.PolicyStartDate = c.[Date] 

   
 select a.ReferenceNumber, min(Value) as Value, min(IndicentDate) as IncidentDate, max(StartDate) as StartDate /* others are dummy aggregates but this one is not */ 'FY' + cast(year(dateadd(month, -11, min(IncidentDate))) as char(4)) + '/' + cast(year(dateadd(month, -11, min(IncidentDate))) - 1999 as char(2)) as FY from TableA a cross apply ( select * from TableB b where b.ReferenceNumber = a.Reference.Number and b.StartDate < a.IncidentDate ) b group by a.ReferenceNumber 

Ihr Geschäftsjahr beginnt elf Monate "spät", so dass es leicht ist zu bestimmen, wo ein date fällt ohne einen Nachschlag.

 year(dateadd(month, -11, <date>)) 

Wenn du es mit deinem "FY2013 / 14" -Format verwende, nehme ich ein wenig mehr Arbeit, aber du könntest kleine functionen schreiben, um diese Berechnungen zu machen. Übrigens kommt der 1999 aus dem Hinzufügen von 1 und der Subtraktion von 2000, um einen zweistelligen Jahreswert zu erhalten. Könnte modulo 100 verwenden, um es generisch über das Jahr 2098 zu machen, wenn das wichtig ist.

Meine Annahmen gehen hinein:

  • IncidentDate und StartDate sind datatyp "DATE". Dies sollte auch funktionieren, wenn sie DATETIME sind, wobei alle timewerte gleich sind.
  • TableC enthält eine Zeile für jedes mögliche date (was Sie impliziert haben). Ein anderer Stil wäre {FinancialYear, FirstDate, LastDate}, und du würdest zu dieser Tabelle mit der in der on Klausel join.
  • Ich habe nicht ganz bekommen, was Sie in Bezug auf "die dataqualität ist schlecht" bedeutet. Diese Abfrage wird das gewünschte IncidentDate und StartDate (falls verfügbar) zurückziehen, so dass Sie eine Geschäftslogik anwenden können. Meine Probe hier ist "wenn es kein anwendbares StartDate gibt, basiere das FinancialYear auf IncidentDate. (Ersetzen Sie diese äußeren Joins mit inneren Joins, wenn die data es zulassen.)
  • Toss in Parameter, wenn du diese data nicht für alle ReferenceNumbers wünschst.
  • Überprüfen Sie auf Syntaxerrors, ich konnte diese Abfrage nicht ausführen und testing.

(Beachten Sie, dass "Date" ein verwirrender Name für eine Spalte ist.)

 WITH ctePart1 (ReferenceNumber, IncidentDate, ClosestStartDate) as (-- Data based on the join to "most recent prior StartDate" select ta.ReferenceNumber ,ta.IncidentDate ,max(tb.StartDate) from TableA ta left outer join TableB tb on tb.ReferenceNumber = ta.ReferenceNumber and tb.StartDate < ta.IncidentDate group by ta.ReferenceNumber ,ta.IncidentDate) select cte.ReferenceNumber ,cte.IncidentDate ,cte.ClosestStartDate ,isnull(tcStart.FinancialYear, tcIncident.FinancialYear) FinancialYear from ctePart1 cte left outer join TableC tcStart on tcStart.Date = cte.ClosestStartDate left outer join TableC tcIncident on tcIncident.Date = cte.IncidentDate