Help doing a SUM for Fiscal Year data

I have a problem that at first sounded easy (perhaps it still is), but as I
get into it I'm getting more and more . Here is the problem. I
have a table holding transctions (we'll call the table Trx for ease of use),
and this table has a Date column that records the date of the transaction.
It also has a quantity column showing how many widgets were sold. Now, if I
want to do a SUM on this table to count the total number of widgets sold
YTD, BUT my year is a fiscal year running 5/1 - 4/30, how would one do this
programmatically?
I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
and '4/30/2006') b/c this query will embed into a report that needs to flow
from year to year with no updates.
I've tried using various combo's of DatePart and comparing the Trx.Date to
GetDate() and so on, but can't quite get this. It needs to be smart enough
to capture the right data if I am printing this report on 2/1/2006 (which
would then go back to 5/1/2005) or on 7/1/2006 (which would then go back to
5/1/2006).
Any thoughts/ideas/help would be GREAT! Thanks so much...
JasonThe following will find the current fiscal year beginning, assuming that
your FY starts on May 1:
select
dateadd (mm, -(datediff (mm, convert (char (4), getdate (), 112) + '0501',
getdate ()) + 12) % 12, convert (char (6), getdate (), 112) + '01')
It will determine it to be 2005-05-01. You can adapt this as you like. For
example, change the '0501' to '0901' for a FY start on Sep 1. Running it
today (Aug 4, 2005) will show a FY start of 2004-09-01.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jason" <jason@.nospam.com> wrote in message
news:Ops4H1TmFHA.3448@.TK2MSFTNGP12.phx.gbl...
I have a problem that at first sounded easy (perhaps it still is), but as I
get into it I'm getting more and more . Here is the problem. I
have a table holding transctions (we'll call the table Trx for ease of use),
and this table has a Date column that records the date of the transaction.
It also has a quantity column showing how many widgets were sold. Now, if I
want to do a SUM on this table to count the total number of widgets sold
YTD, BUT my year is a fiscal year running 5/1 - 4/30, how would one do this
programmatically?
I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
and '4/30/2006') b/c this query will embed into a report that needs to flow
from year to year with no updates.
I've tried using various combo's of DatePart and comparing the Trx.Date to
GetDate() and so on, but can't quite get this. It needs to be smart enough
to capture the right data if I am printing this report on 2/1/2006 (which
would then go back to 5/1/2005) or on 7/1/2006 (which would then go back to
5/1/2006).
Any thoughts/ideas/help would be GREAT! Thanks so much...
Jason|||create table fiscal_year(num int, date_from datetime, date_to datetime)
insert into fiscal_year values(2005, '5/1/2005', '4/30/2006')
-- assuming date_sold truncated to midnight
select ...
from sales, fiscal_year
where date_sold between date_from and date_to
and fiscal_year.num = @.year|||This works GREAT! Thanks so much...
"AK" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1123193194.307575.11490@.g49g2000cwa.googlegroups.com...
> create table fiscal_year(num int, date_from datetime, date_to datetime)
> insert into fiscal_year values(2005, '5/1/2005', '4/30/2006')
> -- assuming date_sold truncated to midnight
> select ...
> from sales, fiscal_year
> where date_sold between date_from and date_to
> and fiscal_year.num = @.year
>|||The key here is to create a value to adjust the dates based on when your
fiscal year starts. Assuming 5/1/2004 is the beginning of fiscal year 2005.
DECLARE @.FYStart INT,
@.FYOffset INT
SET @.FYStart = 5 -- The calendar month that begins the fiscal year
SET @.FYOffset = CASE @.FYStart WHEN 1 THEN 0 ELSE 13 - @.FYStart END
Then we can use this value (@.FYOffset) to adjust the dates.
DECLARE @.FiscalYear INT
SET @.FiscalYear = 2005 -- The fiscal year you want to search for
SELECT ...
FROM Trx
WHERE DATEPART(YEAR,DATEADD(MONTH,@.OFFSET,Trx.Date) = @.FiscalYear
"Jason" <jason@.nospam.com> wrote in message
news:Ops4H1TmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I have a problem that at first sounded easy (perhaps it still is), but as I
>get into it I'm getting more and more . Here is the problem. I
>have a table holding transctions (we'll call the table Trx for ease of
>use), and this table has a Date column that records the date of the
>transaction. It also has a quantity column showing how many widgets were
>sold. Now, if I want to do a SUM on this table to count the total number
>of widgets sold YTD, BUT my year is a fiscal year running 5/1 - 4/30, how
>would one do this programmatically?
> I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
> and '4/30/2006') b/c this query will embed into a report that needs to
> flow from year to year with no updates.
> I've tried using various combo's of DatePart and comparing the Trx.Date to
> GetDate() and so on, but can't quite get this. It needs to be smart
> enough to capture the right data if I am printing this report on 2/1/2006
> (which would then go back to 5/1/2005) or on 7/1/2006 (which would then go
> back to 5/1/2006).
> Any thoughts/ideas/help would be GREAT! Thanks so much...
> Jason
>