How to create yearly summary view

[nysouth]

I would like to create a view that would show membership in a chapter for the past five years, based on a fiscal year running from July 1 - June 30. The data resides in an activity table that combines a hodge-podge of information you select out based on type. Here is the data that I would have to call includes:

ID
ActivityType (Membership)
Transaction Date (date/time field)
Membership

I would like it to then give me:

ID, Past fiscal year membership (currently 03/04), past year (02-03), three years ago (01-02), etc...

Is this possible?
Colleen

 

[donutman]

An example of a few rows of your data would help. And then the output you expect from the example you provide. It's not clear why you listed ID as part of your output. Maybe you just want a yes/no under each years' column? Or are you expecting a total count based on either a type of activity or membership?
It just isn't clear what you're asking for.
-Karl

 

[nysouth]

Sure, here is a sample of the data using two chapters and three years (I do want five years, but to keep from being too redundant in the sample I have done three years):

ID Name Date Amount
1 Chapter 1 4/1/2004 $50.00
1 Chapter 1 1/1/2004 $75.00
2 Chapter 2 3/1/2004 $100.00
1 Chapter 1 6/1/2003 $100.00
2 Chapter 2 5/1/2003 $50.00
1 Chapter 1 2/1/2003 $75.00
2 Chapter 2 2/1/2002 $100.00
1 Chapter 1 8/1/2001 $100.00
2 Chapter 2 9/1/2001 $50.00
I would like the view to take this and output:

ID Name This Year Last Year Two Years
1 Chapter 1 $125.00 $175.00 $100.00
2 Chapter 2 $100.00 $50.00 $150.00

This year would be the current fiscal year (7/1/03-6/30/04), last year would be the previous year (7/1/2002-6/30/2003), two years would be (7/1/2001-6/30/2002) and so on for the past five years.

Any help is greatly appreciated.

Colleen

 

[donutman]

create function dbo.MembershipAmount (@Yr as int, @ID as int)
returns money
as
begin
return (Select sum(Membership) from MyTable where ID=@ID and @Yr=YEAR(TransactionDate)+MONTH(TransactionDate)/7-1)
end
GO
Create procedure spMembershipSummary
@Yr as int
AS
Select distinct ID, ActivityType, dbo.MembershipAmount(@Yr, ID), dbo.MembershipAmount(@Yr-1,ID), dbo.MembershipAmount(@Yr-2,ID) etc.
from MyTable

I really like this because it looks clean, but I think it may be a performance hog. Let me know how it works and I'll think about how to improve the performance.
-Karl

-Karl

 

[donutman]

I think this SP would be faster. Don't forget to drop the old one.

Create procedure spMembershipSummary
@Yr as int
AS
Select M.ID, M.ActivityType, dbo.MembershipAmount(@Yr, ID), dbo.MembershipAmount(@Yr-1,ID), dbo.MembershipAmount(@Yr-2,ID) etc.
from (Select distinct ID, ActivityType from MyTable) as M

I'd be curious as to how much of an improvement it makes?
-Karl

 

[nysouth]

Hi Karl!

I have been working with the stored proceedure you sent and am able to get it to create the proceedure and the function. The problem now is this is my first time to even work with a stored proceedure and I haven't a clue how to pull the data out of them. I have been looking in books online, but have no idea what I am looking for. What am I supposed to do to get this data? Any hints are greatly appreciated...I am still in the midst of self teaching until funding comes through for my classes in July. If you do not have the time to point this out that is fine,

Also, I changed a few things around to allow for it to only calculate when the activity is a gift and is made to our foundation (see below). I also renamed for clarity on this end.

create function dbo.DonationAmount (@Yr as int, @ID as int)
returns money
as
begin
return (Select sum(Activity.AMOUNT)
from Activity
where ID=@ID and @Yr=YEAR(Activity.TRANSACTION_DATE)+MONTH(Activity.TRANSACTION_DATE)/7-1)
end
GO

Create procedure spDonationSummary
@Yr as int
AS
Select distinct ID, dbo.DonationAmount(@Yr, ID), dbo.DonationAmount(@Yr-1,ID), dbo.DonationAmount(@Yr-2,ID)
, dbo.DonationAmount(@Yr-3,ID), dbo.DonationAmount(@Yr-4,ID), dbo.DonationAmount(@Yr-5,ID)
from Activity
where activity_type = 'gift' and org_code = 'found'

Thanks!
Colleen

 

[donutman]

GJ on the modifications. There is one more that you may end up deciding to make. The
+MONTH(Activity.TRANSACTION_DATE)/7-1)
can be changed to
+MONTH(Activity.TRANSACTION_DATE)/7)
depending on how you want to label your fiscal years. I used the IRS approach, the year refers to the year in which the fiscal year starts. Suit yourself.

How you get the data depends upon what is driving your SQL. From the enterprise manager (EM) you can open a query window and type :
exec spDonationSummary 2005

That will execute the SP and display the rowset. You can now write your own queries that use the dbo.DonationAmount function however you want. In fact you can just use the select statement from the SP and replace the @Yr-n with whatever year you want.

Good Luck, Colleen
-Karl

 

[nysouth]

Hi Karl!

I tried running the sp today and it is the performance hog you were afraid of. I gave up after an hour and half. The query did not return any rows when ended. I tried doing the second one you suggested (with a few modifications) and it ran just as long, but when I stopped there were some rows of data. So, I did some further modifications to limit the selection to the member type I am using for this one particular report (see below) and can get 100 rows in about 1.40 mins. There are a few problems with the data returned that are really purplexing to me. First of it gives me two rows of null data so I switched the date to 2003 and that seems to be OK. But in either case, the first row of data (which matches 03-04) is wrong, but the rest of the years are OK.

I think I need a way to narrow down the selection of the amount for the function, but everything I do seems to not work. I somehow need for it to only add the amount if org_code = 'found' and activity_type = 'gift'. I think that may be what the culpret is. How would I add this?

My last question is, how do I get the information, once I get it correct into a view or table that I can use for reporting?

Thanks for all the help!

Colleen

Here is the new proceedure I wrote:

Create procedure spDonationSummary
@Yr as int
AS
Select M.ID, dbo.DonationAmount(@Yr, ID), dbo.DonationAmount(@Yr-1,ID), dbo.DonationAmount(@Yr-2,ID)
, dbo.DonationAmount(@Yr-3,ID), dbo.DonationAmount(@Yr-4,ID), dbo.DonationAmount(@Yr-5,ID)
from (Select distinct Activity.ID from Activity inner join name on name.id = activity.id where activity_type = 'gift' and activity.org_code = 'found' and name.Status = 'A' and name.member_type = 'C') as M

 

[donutman]

I'll start work on an alternative method using a view that I'm fairly sure will execute much faster, but you apparently have a nice test bed for the use of functions in queries. I've heard (only once) that they don't perform well, so I'd like to continue to investigate on your data.
Perform a Select dbo.DonationAmount(2005, ID), dbo.DonationAmount(2004,ID), dbo.DonationAmount(2003,ID)
, dbo.DonationAmount(2002,ID), dbo.DonationAmount(2001,ID), dbo.DonationAmount(2000,ID)
but replace the ID with a known value of a good donar. How long does it take? Also double check your table design and make sure ID is an indexed field in both your Activity table and Name table.
BTW, if you can you should change the names of those tables so they don't sound so generic. Use something like DonarActivity and DonarName. If you do, you'll will have to change the syntax of your function and SP, but better now than later. While you are at it, it would also be a good idea to make sure you have a Primary Key in both tables. Preferably an identity column like DAKey and DNKey. Naturally you can't make these modifications if you can't modify the front-end apps that you may have. But trust me, a little extra work now will pay big dividends in the long run.
-Karl

 

[donutman]

Another quick question. Is it a problem adding new columns to the Activity Table? A way to enhance performance is to break the rule about normilization of a data table and put an int column named FiscalYear into it.
-Karl

 

[donutman]

Create View dbo.vwDonarFiscalAmount
AS
Select ID, YEAR(Activity.TRANSACTION_DATE)+MONTH(Activity.TRANSACTION_DATE)/7-1) as FiscalYear, sum(Activity.AMOUNT) as DonationAmount from Activity
Group By ID, YEAR(Activity.TRANSACTION_DATE)+MONTH(Activity.TRANSACTION_DATE)/7-1)
GO

After you create that view, run it with
Select * from vwDonarFiscalAmount
let me know how long it takes before the rows are returned. Stop it if it takes more than 2 minutes. BTW, how many rows have you got in the Activity table? One more thing, I don't know what I was thinking, but year 2005 and 2004 should return nulls because they haven't started yet!
Now, you can build your cross tab query this way:
SELECT A.ID, V1.DonationAmount, V2.DonationAmount, V3.DonationAmount, V4.DonationAmount, V5.DonationAmount
FROM (Select Distinct ID from Activity) A
FULL JOIN vwDonarFiscalAmount V1 on A.ID=V1.ID and V1.FiscalYear=@Yr
FULL JOIN vwDonarFiscalAmount V2 on A.ID=V2.ID and V2.FiscalYear=@Yr-1
FULL JOIN etc
ORDER BY ID

Make that a SP and execute it. That should be very fast. If it isn't then you either have index problems and/or very very large tables. If all is well then add your filters and make it even faster. I'll wait for your responses.
-Karl

 

[tektipdjango]

Have you try this?

select
Id,
Name,
sum(case when (Date<'06/30/2004' and Date>'07/01/2003') then amount else 0) as thisYear,
sum(case when (Date<'06/30/2003' and Date>'07/01/2002') then amount else 0) as lastYear,
sum(case when (Date<'06/30/2002' and Date>'07/01/2001') then amount else 0) as 2Years,
sum(case when (Date<'06/30/2001' and Date>'07/01/2000') then amount else 0) as 3Years,
sum(case when (Date<'06/30/2000' and Date>'07/01/1999') then amount else 0) as 4Years
from
ActivityTable
group by ID,Name

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[donutman]

Tektipdjango, you may have a winner there. It has the advantage of no joins. It is necessary to tweak the dates and add an "end":
sum(case when (Date<'07/01/2004' and Date>'06/30/2003') then amount else 0 end) as ThisYear,
I assuming that there is no time of day in the field, if there is then use
sum(case when (Date<'07/01/2004' and Date>='07/01/2003') then amount else 0 end) as ThisYear,
It's probably better to use the later and not worry about the existance of a time value. Colleen, I hope you will try all the variations discussed in previous posts and report their speeds. I think you will find Tektipdjango's the best, if so you should parameterize it (so that you don't have to modify it each year -- it won't slow it down), then you can either pass a @Yr starting point and compute all the dates into other variables or always "seed" the first year as follows:
Declare @Yr1 datetime, @Yr2 datetime, @Yr3 datetime, @Yr4 datetime, @Yr5 datetime, @Yr6 datetime
Set @Yr1=cast('07/01/'+str(year(getdate())+month(getdate())/7) as datetime) --note there is no minus 1 here
Set @Yr2=cast('07/01/'+str(year(@Yr1)-1) as datetime)
Set @Yr3=cast('07/01/'+str(year(@Yr2)-1) as datetime)
Set @Yr4=cast('07/01/'+str(year(@Yr3)-1) as datetime)
Set @Yr5=cast('07/01/'+str(year(@Yr4)-1) as datetime)
Set @Yr6=cast('07/01/'+str(year(@Yr5)-1) as datetime)
Now write the case statements as:
sum(case when (Date<@Yr1 and Date>=@Yr2) then amount else 0 end) as ThisYear,
sum(case when (Date<@Yr2 and Date>=@Yr3) then amount else 0 end) as LastYear,
etc.

-Karl

 

[nysouth]

Hi Karl!

Sorry to not respond sooner, I was called away with other duties. I ran the query you first suggested and it only took a second to come back with the data. I tried looking at indexes, but did not get too far with that. I am not able to update these tables as they are defined by the application, but would changing indexing hurt the program? Just curious. The activity table has 224,921 rows of data and is one of the largest in the database.

I tried running the command to create the view you suggested, but there was an error in running it that I could not figure out. It said it was line 3 near ")." I played around for a while, but just could not get anything to happen with this statement.

Tektipdjango's solutions works quickly (one second to return 21,444 rows), but like you I don't want to have to keep updating the year each July.

I ran this with the parameters you suggested and they work like a charm. The only problem is, it will not create a view with a declare statement. Here is what I was trying for:

Create View dbo.v_DonarFiscalFiveYrHistory
AS

Declare @Yr1 datetime, @Yr2 datetime, @Yr3 datetime, @Yr4 datetime, @Yr5 datetime, @Yr6 datetime, @Yr7 datetime
Set @Yr1=cast('07/01/'+str(year(getdate())+month(getdate())/7) as datetime) --note there is no minus 1 here
Set @Yr2=cast('07/01/'+str(year(@Yr1)-1) as datetime)
Set @Yr3=cast('07/01/'+str(year(@Yr2)-1) as datetime)
Set @Yr4=cast('07/01/'+str(year(@Yr3)-1) as datetime)
Set @Yr5=cast('07/01/'+str(year(@Yr4)-1) as datetime)
Set @Yr6=cast('07/01/'+str(year(@Yr5)-1) as datetime)
Set @Yr7=cast('07/01/'+str(year(@Yr6)-1) as datetime)

select
Id,
sum(case when (transaction_date <@Yr1 and transaction_date >=@Yr2) then amount else 0 end) as ThisYear,
sum(case when (transaction_date <@Yr2 and transaction_date >=@Yr3) then amount else 0 end) as LastYear,
sum(case when (transaction_date <@Yr3 and transaction_date >=@Yr4) then amount else 0 end) as TwoYearsAgo,
sum(case when (transaction_date <@Yr4 and transaction_date >=@Yr5) then amount else 0 end) as ThreeYearsAgo,
sum(case when (transaction_date <@Yr5 and transaction_date >=@Yr6) then amount else 0 end) as FourYearsAgo,
sum(case when (transaction_date <@Yr6 and transaction_date >=@Yr7) then amount else 0 end) as FiveYearsAgo
from Activity
where activity_type = 'gift' and activity.org_code = 'found'
group by ID

Again, thank you both so much for the tips! I would never have gotten this far. If we can just make this work as a view we would be home free.

Thanks!
Colleen

 

[donutman]

Are you sure you can't just use a SP? Views don't support parameters directly although there is a kludge you can utilize if you insist. It would require that you run a SP (prior to running your view) which puts the parameters into a single row of a "work table" that you then cross join with the table in the view. That way you have access to each parameter.
-Karl