CODEWITHSUNDEEP
sql-server-2008-r2
Rain Check
Rain Check

Reputation: 35

Count 2 consecutive years of Members in DB SQL Server Management Studio - 2008 R2

Microsoft SQL Server Management Studio - 2008 R2

Hi,

I have a table called [VotingStatusHistory] in this table I have 5 columns named:

UID
MemberID
AffilliationDate
VotingStatus
Inserted

This table has [MemberID's] and holds [AffilliationDates] for each year a member is Affilliated.

I would like to know what [MemberID] has [AffilliationDate's] for 2 consecutive years with a [VotingStatus] of 1 (example below for 2017, 2018)

UID     MemberID       AffilliationDate      VotingStatus        Inserted
535436  153393      2017-04-24 09:46:13.000     1        2018-03-14 00:00:00.000
582084  153393      2018-04-30 09:46:13.000     1        2019-01-29 00:00:00.000

Please some help how to approach this, I’ve tried using SUM and CASE but got nowhere so far. Thanks in advance...

Upvotes: 0

Views: 25

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82484

First, you should know that the 2008 r2 version has ended it's extended support this July. This means that it will no longer be receiving updates (not even security updates!) from Microsoft.
This is one reason why you should seriously consider upgrading to a later version of SQL Server.
The other reason is that starting with 2012 (which is the very next version), SQL Server is supporting the lag and lead window functions, which can be very helpful in queries like this.

Having said all that, and assuming you can't upgrade to a supported version, here's a query that should get you the desired results with 2008 r2 version:

SELECT vsh.UID, vsh.MemberID, vsh.AffilliationDate, vsh.VotingStatus, vsh.Inserted
FROM [dbo].[VotingStatusHistory] As vsh
WHERE vsh.VotingStatus = 1
AND EXISTS
(
    SELECT 1
    FROM [dbo].[VotingStatusHistory] As i
    WHERE vsh.MemberID = i.MemberID
    AND i.VotingStatus = 1
    AND YEAR(i.AffilliationDate) = YEAR(vsh.AffilliationDate) + 1
)

Upvotes: 1

Related Questions