Grouping and totaling by date in SQL Issue SQL2005

[wsproperties]

The following query, done in QA, totals the sum of giving for a particular account number. It will list each gift separately and insert the number of years that elapsed when the gift was given. In my case, the account number made 3 gifts in the first year or no_of_years. Here are my questions:

a. How can I structure the code to analyze the no_of_gifts based on a fiscal year starting in July or 7th month, then count back by a year

b. How can I structure the code so that if mutliple gifts were given in the same year, I get one grand total as opposed to each total being separated based on the giffeffdate

c. Must I group by each aggregate as I have at the bottom? Any way around that

select datediff(yy, gifteffdate,getdate()) AS no_of_years,
case when cash.giftjntkey >=1 and marr.SPOUSEid=cash.giftjntid
then
sum(giftamount*2)
else sum(giftamount)end as GivenLastyear
from gifts cash
left outer join coreVIEW_married marr
on marr.SPid=cash.giftjntid
where cash.gifttype in ('g','b','c','y')
and cash.giftid = '0000143134'

group by gifteffdat,cash.giftjntid,cash.giftjntkey,
marr.SPid

 

[bborissov]

Could you please post some example data and what you want as a result from the data you post?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[wsproperties]

This is what i get now
no_of_Years GivenLastyear
4 350.00
3 5000.00
2 5000.00
2 5000.00
1 50.00
1 5000.00

Here is what I want
no_of_Years GivenLastyear
4 350.00
3 5000.00
2 10000.00
1 5050.00