Prev: Help with Query
Next: Person's age based on DOB
From: Rocky20 on 27 May 2010 15:17
I have a table that contains donation data. What I want to do is pull
records based on if they gave $1000 or more for 3 or more consecutive
years or more. The years in the table range from 2005 to 2010. I'm
running SQL Server 2005. I've included some sample data to put into
the table. Can anyone help with this?

Here is the code to create the table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Donations](
[MemberID] [int] NOT NULL,
[FiscalYr] [char](4) NOT NULL,
[Amount] [money] NULL,
CONSTRAINT [PK_Donations] PRIMARY KEY CLUSTERED
(
[MemberID] ASC,
[FiscalYr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Here is some sample data
Insert into Donations values(1111,'2005',2000)
Insert into Donations values(1111,'2006',1000)
Insert into Donations values(1111,'2007',1500)
Insert into Donations values(1112,'2005',1000)
Insert into Donations values(1112,'2006',1200)
Insert into Donations values(1112,'2008',1000)
Insert into Donations values(1113,'2007',1000)
Insert into Donations values(1114,'2007',1000)
Insert into Donations values(1114,'2008',1400)
Insert into Donations values(1114,'2009',1000)
Insert into Donations values(1114,'2010',1100)
Insert into Donations values(1115,'2006',1000)
Insert into Donations values(1115,'2007',100)
Insert into Donations values(1115,'2008',1000)
Insert into Donations values(1115,'2009',1000)

With this sample data only 1111 and 1114 would be pulled.
From: Eric Isaacs on 27 May 2010 16:13
SELECT DISTINCT
D2.MemberID
FROM
dbo.Donations D2
INNER JOIN Donations D1 ON D2.MemberID = D1.MemberID
AND CAST(D2.FiscalYr AS INTEGER) - 1 =
CAST(D1.FiscalYr AS INTEGER)
INNER JOIN Donations D3 ON D2.MemberID = D3.MemberID
AND CAST(D2.FiscalYr AS INTEGER) + 1 =
CAST(D3.FiscalYr AS INTEGER)


- Eric Isaacs
From: Eric Isaacs on 27 May 2010 16:17
Sorry, I missed part of the criteria. This will work...

SELECT DISTINCT
D2.MemberID
FROM
dbo.Donations D2
INNER JOIN Donations D1 ON D2.MemberID = D1.MemberID
AND CAST(D2.FiscalYr AS INTEGER) - 1 =
CAST(D1.FiscalYr AS INTEGER)
AND D1.Amount >= 1000
INNER JOIN Donations D3 ON D2.MemberID = D3.MemberID
AND CAST(D2.FiscalYr AS INTEGER) + 1 =
CAST(D3.FiscalYr AS INTEGER)
AND D3.Amount >= 1000
WHERE
D2.Amount >= 1000

- Eric Isaacs
From: Rocky20 on 27 May 2010 16:29
On May 27, 3:17 pm, Eric Isaacs <eisa...(a)gmail.com> wrote:
> Sorry, I missed part of the criteria.  This will work...
>
> SELECT DISTINCT
>     D2.MemberID
> FROM
>     dbo.Donations D2
>     INNER JOIN Donations D1 ON D2.MemberID = D1.MemberID
>                                AND CAST(D2.FiscalYr AS INTEGER) - 1 =
> CAST(D1.FiscalYr AS INTEGER)
>                                AND D1.Amount >= 1000
>     INNER JOIN Donations D3 ON D2.MemberID = D3.MemberID
>                                AND CAST(D2.FiscalYr AS INTEGER) + 1 =
> CAST(D3.FiscalYr AS INTEGER)
>                                AND D3.Amount >= 1000
> WHERE
>     D2.Amount >= 1000
>
> - Eric Isaacs

Thanks Eric, this works great!
From: Plamen Ratchev on 27 May 2010 16:49
Here is one solution:

SELECT MemberId
FROM (
SELECT MemberID, FiscalYr, Amount,
FiscalYr - ROW_NUMBER() OVER(PARTITION BY MemberId ORDER BY
FiscalYr) AS grp
FROM Donations
WHERE Amount >= 1000 ) AS T
GROUP BY MemberID, grp
HAVING COUNT(FiscalYr) >= 3;

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Help with Query
Next: Person's age based on DOB