June 19, 2008 at 12:37 pm
I am having a brain freeze. I really should know how to do this, but the old thinker isn't working today. I have a table full of donor amounts with each individual contribution on its own row with its own date. I need to find the date when a summed dollar value exceeded a certain amount. If it helps to have some data:
CREATE TABLE [dbo].[GIFTCREDITDETAIL](
[PEOPLE_ORG_CODE_ID] [varchar](10) NOT NULL,
[GIFT_AMOUNT] [numeric](18, 6) NOT NULL,
[GIFTCREDIT_DATE] [datetime] NOT NULL)
INSERT giftcreditdetail
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','100.00','7/1/2007')
INSERT giftcreditdetail
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','200.00','8/1/2007')
INSERT giftcreditdetail
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','300.00','9/1/2007')
INSERT giftcreditdetail
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','475.00','10/1/2007')
INSERT giftcreditdetail_la
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','125.00','11/1/2007')
I want it to tell me that person p0000001234 gave at least $1000 by 10/1/2007. That's when they would be eligible for a particular donor award. Thank you so much in advance for any help!
June 19, 2008 at 1:02 pm
[...I want it to tell me that person p0000001234 gave at least $1000 by 10/1/2007. That's when they would be eligible for a particular donor award. Thank you so much in advance for any help!
If you know the date ahead of time, you can do something like this
SELECT [PEOPLE_ORG_CODE_ID], max([GIFTCREDIT_DATE])
FROM [GIFTCREDITDETAIL]
WHERE [GIFTCREDIT_DATE] <= CONVERT(DATETIME, '10/1/2007')
GROUP BY [PEOPLE_ORG_CODE_ID]
HAVING SUM(gift_amount) >= 1000.0
like I said though, if you know the date ahead of time
June 19, 2008 at 1:06 pm
Thanks for your response. I've been trying a similar set of code, but the problem is that I don't know the date and it's going to be a different date for each person. I want to know the date by which time each individual had contributed at least $1000.
June 19, 2008 at 1:08 pm
I modified your sample code so it uses a temp table (didn't want to create a table in my database) and has the correct length for the code.
The CROSS APPLY is going to be a bit slow with large data sets, but for the sample code, it works fine.
At least it will get you thinking.
[font="Courier New"]CREATE TABLE #GIFTCREDITDETAIL(
[PEOPLE_ORG_CODE_ID] [varchar](11) NOT NULL,
[GIFT_AMOUNT] [numeric](18, 6) NOT NULL,
[GIFTCREDIT_DATE] [datetime] NOT NULL)
GO
INSERT #GIFTCREDITDETAIL
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','100.00','7/1/2007')
INSERT #GIFTCREDITDETAIL
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','200.00','8/1/2007')
INSERT #GIFTCREDITDETAIL
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','300.00','9/1/2007')
INSERT #GIFTCREDITDETAIL
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','475.00','10/1/2007')
INSERT #GIFTCREDITDETAIL
(People_org_code_Id, gift_amount, giftcredit_date)
values
('p0000001234','125.00','11/1/2007')
GO
SELECT
D.PEOPLE_ORG_CODE_ID
, MIN(D.GIFTCREDIT_DATE) AS MinDate
FROM
#GIFTCREDITDETAIL D
CROSS APPLY (SELECT SUM(X.GIFT_AMOUNT) AS CurrentSum
FROM #GIFTCREDITDETAIL X
WHERE X.PEOPLE_ORG_CODE_ID = D.PEOPLE_ORG_CODE_ID
AND X.GIFTCREDIT_DATE <= D.GIFTCREDIT_DATE) S
WHERE
S.CurrentSum >= 1000
GROUP BY
D.PEOPLE_ORG_CODE_ID[/font]
June 19, 2008 at 1:09 pm
Ok, I'm an idiot. I was trying to make the example simple but my problem was actually that I wasn't using the right fields in my group by clause. (I didn't give gift_year in the example because I thought it would overcomplicate the issue, but that was the column that was causing my answers to be all wrong!) I've got it now!!! Thanks again!
SELECT [PEOPLE_ORG_CODE_ID], max([GIFTCREDIT_DATE])
FROM [GIFTCREDITDETAIL]
GROUP BY [PEOPLE_ORG_CODE_ID], gift_year
HAVING SUM(gift_amount) >= 1000.0
and people_org_code_ID like 'p%'
and gift_year='2008'
June 19, 2008 at 1:14 pm
FYI - your last piece of code did not work. It came back with 11/1 and the first date when 1000 was reached was 10/1.
June 19, 2008 at 1:16 pm
You're right. Thank you for pointing that out. I'm trying your cross apply logic now. (I have never used a cross apply in a real environment. I do remember it from one of the SQL tests, though!) Where should add my criteria about gift_year='2008'?
June 19, 2008 at 1:19 pm
So you want the SUM by year to be more than $1000.
You need an additional line in the APPLY and also add the WHERE to limit it to a single year:
SELECT
D.PEOPLE_ORG_CODE_ID
, MIN(D.GIFTCREDIT_DATE) AS MinDate
FROM
#GIFTCREDITDETAIL D
CROSS APPLY (SELECT SUM(X.GIFT_AMOUNT) AS CurrentSum
FROM #GIFTCREDITDETAIL X
WHERE X.PEOPLE_ORG_CODE_ID = D.PEOPLE_ORG_CODE_ID
AND X.GIFTCREDIT_DATE <= D.GIFTCREDIT_DATE
AND X.GIFT_YEAR = D.GIFT_YEAR) S
WHERE
S.CurrentSum >= 1000
AND D.GIFT_YEAR = 2008
GROUP BY
D.PEOPLE_ORG_CODE_ID
June 19, 2008 at 1:21 pm
Remember, because of the CROSS APPLY, this is going to be row-by-row processing, so if you have 100m rows, this is going to be pretty slow.
My guess is that people are not very charitable and you don't have a lot of rows.
June 19, 2008 at 1:22 pm
What you want to do is use a running total, and pick the row where that goes over the desired threshhold.
There's a good article on running totals at:
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 1:26 pm
I just went and dug up that article to help illustrate why the cross apply was going to perform poorly if there were a lot of records.
And then you beat me to posting it.
June 19, 2008 at 1:54 pm
Thank you all so much! The CROSS APPLY logic worked great. I had 272 donors who fit the bill! Also, I've bookmarked that article to read as soon as I can. I really appreciate the help!:)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply