Data for every fifth year 3

[aajay]

How can I get data for every fifth year from table

Hope you might have done that also

thanks

 

[bborissov]

Can you post some example data and desired result?
Because I can't understand what you want )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

read this

http://support.microsoft.com/kb/555375

then come back

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[jbenson001]

How about something like this:

declare @StartYear int
set @StartYear = 2000

select Columns
from YourTable(nolock)
where datepart(yy, YourDateColumn) = @StartYear AND (datepart(yy, YourDateColumn) % 5) = 0

Jim

 

[bborissov]

Denis,
I didn't knew that such KB exists


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jbenson001]

Sorry made a little mistake:
The = should be >=

declare @StartYear int
set @StartYear = 2000

select Columns
from YourTable(nolock)
where datepart(yy, YourDateColumn) [b]>=[/b] @StartYear AND (datepart(yy, YourDateColumn) % 5) = 0

 

[bborissov]

jbenson001,
what you got if you want data starting from 1997 and to get the records for every 5th year after that? i.e. for 2002, 2007 etc? The modulus wouldn't work.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[AlexCuse]

Slight change to jbenson001's code, in case StartYear is not a multiple of 5:

[COLOR=blue]select[/color] [COLOR=#FF00FF]Columns[/color]
[COLOR=blue]from[/color] YourTable(nolock)
[COLOR=blue]where[/color] [COLOR=#FF00FF]datepart[/color](yy, YourDateColumn) >= @StartYear 
AND ([COLOR=#FF00FF]datediff[/color](yy, @StartYear, YourDateColumn) % 5) = 0

You would probably do well to look up in BOL any of these functions that you don't understand.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[aajay]

Thank all of you
here is what I'm trying to achieve

declare @rDate datetime
set @rDate = '5/30/2006'

select sum(AnnualFundGiven),sum(AnnualFundGifts),sum(ActualMatchAmount),sum(SoftCreditGiven),FiscalYear

from vwGiving_Summary_FY v
where FiscalYear in(dbo.GetWUFiscalYear(@rDate),dbo.GetWUFiscalYear(@rDate)-5,
dbo.GetWUFiscalYear(@rDate)-10 , dbo.GetWUFiscalYear(@rDate)-15 , dbo.GetWUFiscalYear(@rDate)-20
, dbo.GetWUFiscalYear(@rDate)-25 , dbo.GetWUFiscalYear(@rDate)-30 , dbo.GetWUFiscalYear(@rDate)-35
, dbo.GetWUFiscalYear(@rDate)-40 , dbo.GetWUFiscalYear(@rDate)-45 , dbo.GetWUFiscalYear(@rDate)-50
, dbo.GetWUFiscalYear(@rDate)-55 , dbo.GetWUFiscalYear(@rDate)-60 , dbo.GetWUFiscalYear(@rDate)-65)

group by FiscalYear



and UDF dbo.GetWUFiscalYear is

ALTER FUNCTION [dbo].[GetWUFiscalYear]
(
@InDate DATETIME
)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @Ret INT

IF MONTH(@InDate) > 6
SELECT @Ret = YEAR(@InDate) + 1
ELSE
SELECT @Ret = YEAR(@InDate)

RETURN @Ret
END

 

[bborissov]

So when your start date is '5/30/2006' you want results from:
2006
2001
1996
1991
...
If your start date is '7/01/2006' you want results from:
2007
2002
1997
1992
...

?????
That doesn't make many sense to me.


As I said post some example data with different years in it and what you want from THAT data as a result, with different starting dates.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[aajay]

you are right I need to go 5th back ward year
from start year to all the way up to 1932

 

[gmmastros]

Maybe this...

[COLOR=blue]declare[/color] @rDate [COLOR=#FF00FF]datetime[/color]
[COLOR=blue]set[/color] @rDate = [COLOR=red]'5/30/2006'[/color]

[COLOR=blue]declare[/color] @rMod [COLOR=blue]int[/color]
[COLOR=blue]Select[/color] @rMod = dbo.GetWUFiscalYear(@rDate) % 5

[COLOR=blue]select[/color] sum(AnnualFundGiven),
       sum(AnnualFundGifts),
       sum(ActualMatchAmount),
       sum(SoftCreditGiven),FiscalYear
[COLOR=blue]from[/color]   vwGiving_Summary_FY v
[COLOR=blue]where[/color]  FiscalYear % 5 = @rMod
[COLOR=blue]group[/color] [COLOR=blue]by[/color] FiscalYear

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[aajay]

Thanks George
That's exactly what I was looking for

I appreciate your help

 

[jbenson001]

Good point Borislav
Alex.. good change to my code.. thanks...

 

[Crowley16]

lol Denis, you deserve a star for that link...

--------------------
Procrastinate Now!