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
1737 Views

Similar Articles

[PageSpeed] 5

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:

Help Please
What i want to do is really simple. I have column B with 50 numbers. I want to set up column C so that in C2, if B2=B1 and then it returns a value or something like false. It really doesn't matter what it returns though. Then in C3, if B3=B2 OR B3=B1 then it returns the false value Then in C4, if B4=B3 OR B4=B2 OR B4=B1 then it returns the false. Then in C5, if B5=B4 OR B5=B3 OR B5=B2 OR B5=B1 then it returns the false value. Can anyone help please? I think I could do it with alot of logical statements, but you can only put 30 conditions into one cell. With a list of 50 numbers t...

Landed Costs Setup help
Hello, I would appreciate some advice on landed costs setup. Let=92s say I import goods from Germany. I use a shipping company called CargoShipers, Inc. CargoShippers takes care of handling the shipping costs, duty and inspection fees. So I go into inventory and setup 3 service type items for shipping, duty and inspection. But what do I set the inventory asset account to? These are really expenses right? Then once I create the service type inventory items, I need to create 3 landed cost ids =96 one for each type and assign those ids to CargoShippes as the vendor correct? I...

Querying
I have been playing with MS Query in Excel2000, and have noticed that when I type data that contains both numbers and letters, query doesn't recognise it and does not display it. I have tried to format the cell so that it is recognised as text but still it does not recognise it. It is also interesting to note that MS Query puts a decimal point and a 0 behind the data that are numbers. Please tell me what I have to do to make MSQuery recognise data that contains both numbers and letters ...

Queries returning different formats
Hello, I have cut and paste the same expression from a query to another, however when i do this it changes the format of the information returned. In the inital query it returns (Hours:Minutes) perfectly, expression is "LOS (Hours:Mins)": (EDMain!EDDischargeDate+EDMain!EDDischargeTime)-(EDMain!EDPresentationDate+EDMain!EDPresentationTime). When I paste it, it returns a decimal value (which I presume is the way access formats time), and adds '[]' to the expression i.e. "LOS (Hours:Mins)": ([EDMain]![EDDischargeDate]+[EDMain]![EDDischargeTime])-([E...

The query could not be processed: Error opening data file
Using Excel 2003. Create a chart and have saved it as a web page and published to a remote share. When I save it locally, it works fine and is interactive,etc.. When I save it remotely and open the htm file in a web browser, it opens like the charting area but the data is not present. Gives error below in the pivot table that appears. Getting error The query could not be processed: o Error opening data file "file://\\remoteserver01\hassan\numbers_files\Numbers_Jan24_12558_cachedata001.xml". Any idea how to fix it ? I want others to access it remotely. Thanks Trying other...

convert Days to Years, Months, Days
I want to convert lets say 1890 Days from today to Years Months, Days!! Ex: 1890 Days = 6 years, 3 months and 23 days What is the formula??? Thanks =TODAY()+1890 and format as a date. for the first part. Don't know what the Example has to do with the question. If you don't want the date shown above, then show what is supplied and what you want preferable with low cell addresses.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/sea...

MS Troub Assis
Greetings, Dagnabit! I need help on the HELP!!! I'm not very server tech savvy, but.... can someone give me an example of a server name and corresponding Global Catalog Server Name? I have entered the following for the server name (this is for example purposes): name.servername.local and the following for the Global Catalog Server Name: \Global Address List When I click on the next button in the Microsoft Troubleshooting Assisstant 1.0 during a check for mail delivery trouble, the following error comes back: Error accessing active directory Comment: I don't know if my server ...

Text cutting off on screen but perfect on print preview HELP
Whats up everyone I am having a weird problem. I have column labels i Row 1 and Olympic Mean is showning up as pic Mean, the rest is cutof due to the column size (6.67) Anyway when I print out this workboo Olympic mean displays properly. How can I get the sheet to displa correctly? I am currently in page preview. I have the same labels in another workbook and Olympic mean display perfect in the other workbook. This is frustrating me to no end. Please help. Thank you all i advance -- TwEaKFrEa ----------------------------------------------------------------------- TwEaKFrEaK's Pr...

converts queries from access2002 to sql sarver
hello : Is there a tool that converts the queries form access2002 to sql sarver? monther Lots of Access Queries should work fine in T-SQL unless you use functions (Access / VBA inbuilt or custom functions) or Parameter Queries. I am not aware of any tools to convert these Queries to T-SQL. -- HTH Van T. Dinh MVP (Access) "monther" <monther80@hotmail.com> wrote in message news:uotBXqgoDHA.1724@TK2MSFTNGP10.phx.gbl... > hello : > Is there a tool that converts the queries form access2002 to sql sarver? > monther > > Sorry, I don't know of any as I wro...

Access Help Forum
Is there an Access Help Forum? Sye -- sazi ----------------------------------------------------------------------- saziz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=635 View this thread: http://www.excelforum.com/showthread.php?threadid=38569 Here's a page with LOTS of MS Application links (including Access): http://www.microsoft.com/office/community/en-us/FlyoutOverview.mspx#2 Regards, Ro -- Ron Coderr ----------------------------------------------------------------------- Ron Coderre's Profile: http://www.excelforum.com/member.php?action=geti...

URGENT HELP!!!!!
Hi There, I am trying to fix some problems our company is having with the e-mail system. we are using Microsoft outlook 2000. the problem is..... We use the same server for incoming and outgoing mail. eg. mail.xxxxxxxxx.co.uk, the problem is we can all (8 of us) receive mail but we can't send it. we have tried the firewall but no ports or programs are blocked. it must be something in our building as when I went home I set up my work account on my laptop and could send and receive it from there. This is urgent, so all help would be appreciated. Regards John <johnhutchisono2...

Help with public folders
Im running windows 2003 with exchange 2003 and when i try to view the properties of a public folder i created i get "The operation failed because of an HTTP error 501 (not implemented). ID no c1030af6. If i try to view system folders under the folder root i get a similar error: The operation failed because of an http error 501 (not implemented) verify that the exadmin virtual root exitsts on the destination server. Id no c1030af7. I am recieveing an error from the client application, outlook 2003, specified object cannot be found while syncing up the public folders Any help wou...

plz help....transfer .aba to outlook
hey i just had a quick question on .aba fiels i want ot transfer all my contacts from my really old palm to my new hp ipaq h1945....i created a address book file...but the only thign they had was *.aba type i would like to get teh *.abab fiel into outlook so i can tranfer that to my hp ipaq hopefully that made sense thnaks for the help Sakibles ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Need Help in MS Access & using Themes
Hi every body, I am a new member in google and google groups.I am from Saudi Arabia (Qatif) , My House is near the Arabian Gulf. I am a student in King Fahd University of Petrolium and Minerals in Saudi Arabia,(My major is Managment Informatiom System), I am very interesting to join with You and shares information and Technique . I am preparing for project in (a course), and I faced some problems: 1. I want some themes related to FootBall for Access , and How can I implemented/use it. 2. This is what I think about [This is my CustomerID- the initial one >>> 07010001] 07 01 000...

help on sort
Hello I have a sheet contains data in colum A to G and I keep on adding dat below it. I want to run a macro to sort the data. I need a macro t find the last row and do the sorting on column C. Hope you got what need. Thanks Tom -- Message posted from http://www.ExcelForum.com If there's no other data on your worksheet, you could just sort A:G. with worksheets("sheet1").range("a:g") .sort key1:=.columns(3), order1:=xlascending, header:=xlno end with if you can use a column to determine the last used row (I used A in my example): With Worksheets("sheet1&...

MSN Stock Quotes help
I am trying to do something that I assume is relatively simple. Using Excel 2000, I want to import **ONLY** the "Last Quote" stock quote for a particular symbol. I have tried the "Data | Get External Data | Run Saved Query" function, using Excel's built-in query for retrieving staok quote data from MSN website. However, this function returns the entire detailed quote tables with all sorts of info that I do not need. Does anyone know how I go about importing only the "Last Quote" data and nothing else? TIA. David G- In xl2002, you may edit the q...

Need help, clueless
I have just started using excel and im running into, what probably is, a simple problem. This is exactly what I need. I have two sheets (February & March). I want the number in February B8 to copy into March B3. Thanks in advance -- brian1175 ------------------------------------------------------------------------ brian1175's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31818 View this thread: http://www.excelforum.com/showthread.php?threadid=515468 =feb!B3 "brian1175" <brian1175.23naan_1140635403.8104@excelforum-nospam.com> wrote i...

Printing a Year-End Report Again #2
I was closing the Receivables Management for the year and I accidentally closed the Receivables Year-End report without printing it. How could I open this report again? I would like to print and save this report but I am already done closing the year. I am using Dynamics version 7.0. ...

Please help me!
I have an excel file as monthly report. Everymonth I input new value overwrite onto fields of file (replace old value). But I dont know why file size is increased after each change time. Thanks a lot! -- khanhtt ------------------------------------------------------------------------ khanhtt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25092 View this thread: http://www.excelforum.com/showthread.php?threadid=386039 Try the following to see if it helps: 1. http://www.officearticles.com/excel/clean_up_your_worksheet_in_microsoft_excel.htm 2. Copy the wor...

userform help
I'm trying to insert data into the first empty cell. My code keeps overwriting the last entry...could someone help me figure out where I've messed up? Thank you in advance Dawna Private Sub CommandButton2_Click() Dim rRng As Range Set rRng = Range("A" & Range("A" & Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count + 1, 0).Select rRng.Offset(1, 1) = Me.TbDate rRng.Offset(1, 3) = Me.cboname rRng.Offset(1, 5) = Me.TbPO rRng.Offset(1, 6) = Me.TbCustom Private Sub CommandButton2_Click() Dim rRng As Range LastRow = Range("A&qu...

RFR Interface Errors HELP!
Help! I keep getting the following errors on our Exchange 2k3 server. It's running on a Win2k3 DC that is also a Global Catalog server. It's been in this configuration since Exchange 2k3 was released. Suddenly, I'm getting these odd errors: Event Type: Error Event Source: MSExchangeSA Event Category: RFR Interface Event ID: 9143 Date: 4/19/2006 Time: 7:15:17 AM User: N/A Computer: SERVER_NAME_HERE Description: Referral Interface cannot contact any Global Catalog that supports the NSPI Service. Clients making RFR requests will fail to connect until a Global Catalog becomes av...

Conditional Format Formula Help
Greetings, I have a column of names. Some of these names are in a separate list and some are not. I have a macro to change the color of the cell of the names that are not in the list to red. When I find these names that are not in the list and correct them so that they are now in the list, the cell color is changed back to white. The problem is in quickly identifying which workbooks still have uncorrected names in this column when I open it. For example: In the CF of D1, If any cell in column D is red, then change the color of D1 to brown, otherwise, change it to orange (defa...

Check boxes on querys
i have a query about search for if a check box is ticked or not on a query. On one database if the box is ticked it is shown as -1 when i do a search, which is great as i can just search for those with -1 in it. However we have another database designed by someone else where in the search it is just showing as a box with a tick on it, -1 doesnt work, Is Null, Is Not Null doesnt work, does anyone know how to define the criteria on this checkbox that it picks up just those that are ticked/or not ticked? Many thanks Cathy, I am surprised that using -1 in the criteria doesn't work. I...

Please Help! COleDateTime in VC++ 6.0
I am using VC++ 6.0 in Win 2000 When I try to use the COleDateTime function in the app StringTmp = COleDateTime(vExpirationDate).Format(_T("%d.%m.%y %H:%M:%S")); I get the error msg on some computers the msg is "Debug Assertion Failed! File: afxwin1.in Line:2 Why this function doesn't work on some computer thank you Pol Hello Pola, Thanks for your post. I reviewed your description carefully, and I think more information is needed before moving forward: What's the value for vExpirationDate? Did you build your program with _UNICODE? What are the computers which ...

Min/Max need help
Im trying to put a formula in CL4 for min hours for CJ5:CJ65 & if <=0 have zero value ALSO in CM4 FOR max HOURS for CJ5:CJ65 For the MIN: Array entered** : =MIN(IF(CJ5:CJ65>0,CJ5:CJ65)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. For the MAX: =MAX(CJ5:CJ65) -- Biff Microsoft Excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:CABEFB94-1803-4BE3-AC79-1849BD763D7F@microsoft.com... > Im trying...