Help with query for consecutive years

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.
0
Rocky20
5/27/2010 7:17:12 PM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
1730 Views

Similar Articles

[PageSpeed] 12

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
0
Eric
5/27/2010 8:13:40 PM
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
0
Eric
5/27/2010 8:17:58 PM
On May 27, 3:17=A0pm, Eric Isaacs <eisa...@gmail.com> wrote:
> Sorry, I missed part of the criteria. =A0This will work...
>
> SELECT DISTINCT
> =A0 =A0 D2.MemberID
> FROM
> =A0 =A0 dbo.Donations D2
> =A0 =A0 INNER JOIN Donations D1 ON D2.MemberID =3D D1.MemberID
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND CAST(D=
2.FiscalYr AS INTEGER) - 1 =3D
> CAST(D1.FiscalYr AS INTEGER)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND D1.Amo=
unt >=3D 1000
> =A0 =A0 INNER JOIN Donations D3 ON D2.MemberID =3D D3.MemberID
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND CAST(D=
2.FiscalYr AS INTEGER) + 1 =3D
> CAST(D3.FiscalYr AS INTEGER)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0AND D3.Amo=
unt >=3D 1000
> WHERE
> =A0 =A0 D2.Amount >=3D 1000
>
> - Eric Isaacs

Thanks Eric, this works great!
0
Rocky20
5/27/2010 8:29:08 PM
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
0
Plamen
5/27/2010 8:49:04 PM
On Thu, 27 May 2010 12:17:12 -0700 (PDT), Rocky20
<msmith1265@hotmail.com> wrote:

>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.


Hi

Look at http://pratchev.blogspot.com/2010/02/refactoring-ranges.html
this will give you the start and end dates for the range where they
donated $1000

John
0
John
5/27/2010 8:57:50 PM
I could not figure out what a NULL donation amount would mean; was
that supposed to be NOT NULL? Why did you use CHAR(4) for a numeric
value? I fleshed out the skeleton with a little meat and cut out the
rot. Also, never use MONEY -- it has math problems and makes you look
like a hillbilly.

CREATE TABLE Donations
(member_id INTEGER NOT NULL
  REFERENCES Membership (member_id),
 donation_fiscal_yr INTEGER NOT NULL
   CHECK (fiscal_yr BETWEEN 2005 AND 2010 ),
 donation_amt DECIMAL(10,2) DEFAULT 0.00 NOT NULL
   CHECK (donation_amt >= 0.00),
 PRIMARY KEY (member_id, fiscal_yr));

Since you have a fixed range, you use a look up table instead of
computations:

CREATE TABLE ThreeYearRanges
(donation_period INTEGER NOT NULL PRIMARY KEY,
 start_fiscal_yr INTEGER NOT NULL,
 end_fiscal_yr INTEGER NOT NULL,
 CHECK (start_fiscal_yr + 2 = end_fiscal_yr)
);

INSERT INTO ThreeYearRanges
VALUES (1, 2005, 2007),
       (2, 2006, 2008),
       (3, 2007, 2009),
       (4, 2008, 2010);

This is soooo short, I would put it in a CTE, but you get the point.

>> I want to do is pull [sic: RDBMS is not punch cards] records [sic: rows are not records] based on if they gave $1000 or more for 3 or more consecutive years. <<

No wonder you used CHAR(4); you still think in punch cards! Selecting
a set is not like pulling cards one at a time.

SELECT DISTINCT D.member_id
  FROM Donations AS D, ThreeYearRanges AS R
 WHERE D.donation_fiscal_yr BETWEEN R.start_fiscal_yr AND
R.end_fiscal_yr
   AND D.donation_amt >= 1000.00
 GROUP BY D.member_id, R.donation_period
HAVING COUNT(DISTINCT start_fiscal_yr) >= 3;

You can play with the Range table to do a LOT of other things, but
that is another topic.
0
CELKO
5/28/2010 2:42:38 AM
Reply:

Similar Artilces:

Problems of layout
When I import data from the web such as tables etc, lots ot the rows fold up almost on top of each other so that to read the data, I have to alter the row height every time. How can I preserve the formatting of the cells so that this doesn't happen. thanks ...

Workday function query
Hi there. I'm trying to find a way of forcing the output of a formula to be a workday. i.e. If the outcome of the formula is a Saturday or Sunday, to deliver the prior Friday's date I know I could add an IF statement, but the formula is messy enough as it is, and I'd rather not confuse the poor souls who will inherit the spreadsheet from me. Many thanks Hi The following will add -1 day and -2 days respectively, to the date derived from your calculation =your_calc+CHOOSE(WEEKDAY(your_calc,2),0,0,0,0,0,-1,-2) -- Regards Roger Govier <MrIainMacleod@gmail.com> wrote in m...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

Date Format turn to Year
Hi, I tried to convert the date to YEAR and then the year plus 25 Years later. =Year(A1) I'm getting the result 1900 instead of 1965. I tried to add 25 years later to 1990 from 1965. Your help would be much apprecated. Thanks What's in A1? Are you sure it's a real date? "learning_codes@hotmail.com" wrote: > > Hi, > > I tried to convert the date to YEAR and then the year plus 25 Years > later. > > =Year(A1) I'm getting the result 1900 instead of 1965. > > I tried to add 25 years later to 1990 from 1965. > > Your ...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Crosstab Query 04-06-07
I have a crosstab query that shows the products i sale with the number i have sold for each day. instead of showing the sales for each day i would like to show sales dor each months. How do i show it my month Thanks In query design view, enter this into a fresh column in the Field row: TheYear: Year([SaleDate]) Replace SaleDate with your actual field name. In the next column: TheMonth: Month([SaleDate]) You can now group on these fields instead of on each date. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html ...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

IWAN & IUSR bei Crystal Reports? *help*
Hi NG, my problem is that i can see the crystal reports, but when i open one i get "more information is needed". But till yesterday i could open reports!! Now after some search i see that i have no IUSR_servane and no IWAM_servername. how could this happen? My system is AD, SQL, CRM and each of them is one a seperate 2003 server. Please let me know if you have any suggestions. Regards Nicolas F�hrs sound strange with the IWAN and IUSR. I offten have this problem. There are a techknowledge article with 13 resoluti...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Need online services to the end of the year
I have been a loyal Money customer for years. This morning my Money Plus subscription expired. There seems to be no way to extend it and without it I can't do updates from my bank and credit cards--which I do daily. I understand that everything is shutting down by Jan 11, but it is terrible not to be able to extend basic online services temporarily at least through the end of this year to get me through tax season, etc. and then switch to Quicken at the beginning of 2010. This really sucks! Please, please Microsoft give us at least a stay of exectution through the end of the ye...

Help With Strange Error When Trying to Save Any Record
I am getting astrane error happens when try to create any new record (Customer, Item, Vendor or Account), the system gives me a message that "Save Operation Failed" and when i click "More Info" the error message is "Could not find stored procedure 'DYNAMICS.dbo.aagGetCompanyStatus'", although i have created alot of items, customers, accounts and vendors before but suddenly this error appeared. Any quick help will be highly appreciated. It would appear that the AAG has something to do with a product produced by American Association of Geographers. I...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

help with preview pane and "read receipts"
Hi, I'm new to Outlook administration. We run Outlook on a server wit Exchange. Is it possible to setup Outlook on client PC's so that th preview pane cannot be activated by individual users? Also, can the blocking of "read receipts" by individual PC users b prohibited within Outlook, at the server level? If not, is there an other way to do it? My reasoning for wanting to do the above two things is to make th "read receipts" function work more effectively. Thanks for your suggestions. : ----------------------------------------------- ~~ Message posted from h...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help with Do...Loop
Hi I need a check to be done to see if column a has a number in it then to check if column b has a number. If column B doesn't have a number then I need it to stop and give a msgbox, When column A doesn't have data then I need the loop to stop as we dont have to check column b The code below is what I have but when I try to run it it keeps saying LOOP WITHOUT DO. I hope someone can help me as I am not very good with loops. mykeycode = Range("B32") mysell = Range("N32") Do mykeycode = mykeycode + 1 mysell = ...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

Active docuent
Hello, I just want to show up an RTF report file previously created using automation into a non-modal dialog. CRichText activeX control does not work for this purpose as shows tables, headers etc... diferent from what is into RTF file. I've read about Active Document and the ability to use MS Word seamessly into my aplication. I've downloaded a couple of examples and read a bit about (hard to implement technology, my aplication doesn´t support (i.e. didn't check active container support in wizard when created). It looks that MS Word (or any other active document) takes co...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

Backing Up Database Help
Does anyone know how I would go about creating a macro that when I click it, it will backup the latest data onto a usb storage device. Any help is appreciated. You can't backup a database from within itself. Steveo wrote: >Does anyone know how I would go about creating a macro that when I click it, >it will backup the latest data onto a usb storage device. Any help is >appreciated. -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/2...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...