Prev: Name of MS Online Classes
Next: How to retrieve all data of a table(with only 3 rows and 3Columns) in only one row in a view?
From: Rocky20 on 2 Jul 2010 13:01
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 2 Jul 2010 16:14
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 2 Jul 2010 16:47
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 4 Jul 2010 00:21
>> 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,

 | 
Pages: 1
Prev: Name of MS Online Classes
Next: How to retrieve all data of a table(with only 3 rows and 3Columns) in only one row in a view?