From: Rocky20 on
I need to pull data from the table below for anyone who has donated
$1000 or more in at least 3 years between 2005 and 2010 with a lapse
year in between. In other words they skipped a year somewhere.


Here is the table layout

CREATE TABLE [dbo].[Donations](
[ID] [varchar](10) NOT NULL,
[FiscalYear] [int] NOT NULL,
[Amount] [money] NULL,

CONSTRAINT [PK_Donations] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[FiscalYear] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY]
) ON [PRIMARY]



Data
insert into donations values(101,2005,1000)
insert into donations values(101,2006,1200)
insert into donations values(101,2008,1500)
insert into donations values(102,2005,1000)
insert into donations values(102,2009,1500)
insert into donations values(102,2010,1500)
insert into donations values(103,2005,1500)
insert into donations values(103,2006,1500)
insert into donations values(103,2008,1500)
insert into donations values(103,2009,1100)
insert into donations values(103,2010,1600)
insert into donations values(104,2008,1500)
insert into donations values(104,2007,1500)
insert into donations values(105,2007,1000)
insert into donations values(105,2008,1000)
insert into donations values(105,2009,1000)
insert into donations values(105,2010,1000)
insert into donations values(106,2005,1000)
insert into donations values(106,2006,1000)
insert into donations values(106,2008,900)

For this data my result set would be
101
102
103

I would not pull
104 because they donated only twice
105 because they have not lapsed
106 because only twice did they exceed 1000

Any help anyone can give me would be apprciated. Let me know if you
need more info or a better explaination.

Thanks
From: --CELKO-- on
CREATE TABLE dbo.CelkoDonations
(donor_id CHAR(10) NOT NULL,
fiscal_year INTEGER NOT NULL,
PRIMARY KEY (donor_id, fiscal_year),
donation_amt DECIMAL (8,2) NOT NULL);

INSERT INTO CelkoDonations
VALUES (101, 2005, 1000.00), (101, 2006, 1200.00), (101, 2008,
1500.00),
(102, 2005, 1000.00), (102, 2009, 1500.00), (102, 2010,
1500.00),
(103, 2005, 1500.00), (103, 2006, 1500.00), (103, 2008,
1500.00), (103, 2009, 1100.00), (103, 2010, 1600.00),
(104, 2008, 1500.00), (104, 2007, 1500.00),
(105, 2007, 1000.00), (105, 2008, 1000.00), (105, 2009,
1000.00), (105, 2010, 1000.00),
(106, 2005, 1000.00), (106, 2006, 1000.00), (106, 2008,
900.00);

/*I need to pull data from the table below for anyone who has donated
$1000 or more in at least 3 years between 2005 and 2010 with a lapse
year in between. In other words they skipped a year somewhere.*/

SELECT donor_id
FROM CelkoDonations
WHERE fiscal_year BETWEEN 2005 and 2010
AND donation_amt >= 1000.00
GROUP BY donor_id
HAVING COUNT(fiscal_year) >= 3
AND MAX(fiscal_year) - MIN(fiscal_year) + 1 <> COUNT(fiscal_year);

From: Rocky20 on
On Jul 2, 3:14 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> CREATE TABLE dbo.CelkoDonations
> (donor_id CHAR(10) NOT NULL,
>  fiscal_year INTEGER NOT NULL,
> PRIMARY KEY (donor_id, fiscal_year),
>  donation_amt DECIMAL (8,2) NOT NULL);
>
> INSERT INTO CelkoDonations
> VALUES (101, 2005, 1000.00), (101, 2006, 1200.00), (101, 2008,
> 1500.00),
>        (102, 2005, 1000.00), (102, 2009, 1500.00), (102, 2010,
> 1500.00),
>        (103, 2005, 1500.00), (103, 2006, 1500.00), (103, 2008,
> 1500.00), (103, 2009, 1100.00), (103, 2010, 1600.00),
>        (104, 2008, 1500.00), (104, 2007, 1500.00),
>        (105, 2007, 1000.00), (105, 2008, 1000.00), (105, 2009,
> 1000.00), (105, 2010, 1000.00),
>        (106, 2005, 1000.00), (106, 2006, 1000.00), (106, 2008,
> 900.00);
>
> /*I need to pull data from the table below for anyone who has donated
> $1000 or more in at least 3 years between 2005 and 2010 with a lapse
> year in between.  In other words they skipped a year somewhere.*/
>
> SELECT donor_id
>   FROM CelkoDonations
>  WHERE fiscal_year BETWEEN 2005 and 2010
>    AND donation_amt >= 1000.00
>  GROUP BY donor_id
> HAVING COUNT(fiscal_year) >= 3
>   AND MAX(fiscal_year) - MIN(fiscal_year) + 1 <> COUNT(fiscal_year);

That worked great, thank you very much. I was struggling with this
one.
From: --CELKO-- on
>> That worked great, thank you very much.  I was struggling with this one. <<

I have a bunch of these kinds tricks with a HAVING cause in SQL FOR
SMARTIES. Once you get a set-oriented mindset, SQL programming is so
much easier.

But have other problems if this skeleton was accurate.

A vague, magical "id" that should have the identifier of something in
particular (the law of Identity from classic Greek logic -- "to be is
to be something in particular, etc." Very few industry standard
identifiers are VARCHAR(n) -- their length is part of validation.

The fiscal year should have a constraint on it in the real DDL

Never use the proprietary, inaccurate MONEY data type. Google for the
details about the math errors in it.




[ID] [varchar](10) NOT NULL,
[FiscalYear] [int] NOT NULL,
[Amount] [money] NULL,