Wie zeigt man an, ob eine Spende im Vorjahr aufgetreten ist, aber nicht aktuell?

Ich schaffe einen Jahresabonnementbericht mit folgendem Schema & Abfrage in server 2012:

CREATE TABLE Donation ( HouseholdID INT, HouseholdName VARCHAR(150), [Donation] REAL, DateOrdered DATE ); INSERT Donation VALUES (1,'The Smiths',50,'2011-01-01'), (1,'The Smiths',75,'2012-02-02'), (2,'The Wilsons',10,'2011-03-01'), (2,'The Wilsons',60,'2011-07-01'), (3,'The Kellys',500,'2012-06-01'); --Params DECLARE @RunYear INT=2012 DECLARE @PriorYear INT=@RunYear-1 SELECT [HouseholdID],[HouseholdName],YEAR(DateOrdered) [Year], MAX(Donation) [MaxGift], SUM(Donation) [TotalDonated] FROM Donation WHERE YEAR([DateOrdered]) IN (@RunYear,@PriorYear) GROUP BY HouseholdID, [HouseholdName], YEAR(DateOrdered) ORDER BY HouseholdID, YEAR(DateOrdered) 

Ich möchte eine BIT Säule wie [DonatedInPriorYearOnly] hinzufügen, das wäre nur 1, wenn der Haushalt eine Spende im Vorjahr hatte (2011), aber keiner im Laufe des Jahres (2012).

Wie kann das erreicht werden?

 select blah, blah, blah from donation where householdid in (select householdid from donation where year(dateordered) = @runyear -1 except select householdid from donation where year(dateordered) = @runyear ) 

Das ist die logik Wenn Sie möchten, dass es schneller läuft, erstellen Sie eine Reihe von data, um mit dem 1. und Ende jedes Jahres zusammenzuarbeiten und verwenden Sie diese anstelle von Jahr (dateordered)

Bearbeiten Sie unten

Um eine Bit-Spalte auf dieser Logik zu haben, dies zu tun.

 select blah, blah, blah , case when householdid in (select householdid from donation where year(dateordered) = @runyear -1 except select householdid from donation where year(dateordered) = @runyear ) then 1 else 0 end nameofcolumn from donation where whatever 

Abhängig von Ihrer SQLserver-Version können Sie die Summary-Abfrage (in einer Unterabfrage oder CTE) einpacken und LAG verwenden . Wenn HouseholdID und Jahr nicht mit LAG HouseholdID und (Jahr – 1) übereinstimmen, dann gab es keine Spende im Vorjahr.

Etwas ungefähr so ​​(ungetestet):

 SELECT [HouseholdID], [HouseholdName], [Year], [MaxGift], [TotalDonated] ,CASE WHEN YEAR = previous_year +1 THEN 1 ELSE 0 END AS DonatedPreviousYear FROM ( SELECT [HouseholdID], [HouseholdName], [Year], [MaxGift], [TotalDonated] ,LAG(Year, 1, 0) OVER (PARTITION BY HouseholdID ORDER BY Year) AS previous_year FROM ( SELECT [HouseholdID],[HouseholdName],YEAR(DateOrdered) [Year], MAX(Donation) [MaxGift], SUM(Donation) [TotalDonated] FROM Donation WHERE [DateOrdered] BETWEEN @StartDt AND @EndDt GROUP BY HouseholdID, [HouseholdName], YEAR(DateOrdered) ORDER BY HouseholdID, YEAR(DateOrdered) ) ) 

Du OUTER APPLY eine SELECT TOP Abfrage OUTER APPLY , um im letzten Jahr irgendwelche Spenden von jeder Familie zu finden.

 SELECT <your select list> ,ISNULL(lastyear.donated_bit,0) AS donated_last_year FROM Donation AS d OUTER APPLY( SELECT TOP 1 1 AS donated_bit FROM Donation AS d_lastyear WHERE d_lastyear.HouseholdID = d.HouseholdID AND DATEPART(year, d_lastyear.DateOrdered) = @RunYear - 1 AND d_lastyear.Donation IS NOT NULL AND d_lastyear.Donation > 0 ) AS lastyear 

Ich konnte es nach der Überprüfung der verschiedenen Vorschläge ausarbeiten.

 [DonatedPriorYearOnly] = ( CASE WHEN EXISTS ( SELECT HouseholdID FROM Donation d2 WHERE YEAR(d2.DateOrdered)=@PriorYear AND d2.HouseholdID=d.HouseholdID EXCEPT SELECT HouseholdID FROM Donation d3 WHERE YEAR(d3.DateOrdered)=@RunYear AND d3.HouseholdID=d.HouseholdID ) THEN 1 ELSE 0 END ) 

Vielen Dank für die Antworten. Sie waren alle hilfsbereit.