August 30, 2005 at 8:30 am
I am trying to write a T-sql statement that will give me the the following info:
Comparison with Last Year - Actual Aug 2005 to Actual Aug 2004
I want the headers to display this even thought we are in the fiscal year 2006 which started July 1.
Comparison With This year's Budget - Actual August 2005 to Actual August 2005
My code is as follows:
<< CREATE Procedure WeeklySalesReport
as
declare @thisMonth integer, @thisYear integer, @fiscalYear int
declare @testDate dateTime
select @testDate = getDate()
--select @testDate = '07/04/05'
select @thisMonth = datepart(month, @testDate) -1
select @thisYear = datepart(year, @testDate)
select @fiscalYear = @thisYear
if( @thisMonth >= 7 )
select @fiscalYear = @fiscalYear+1
declare @startDate datetime, @endDate dateTime
select @endDate = endDate from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear
select @startDate = startDate
from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear
-------------
this is not working. Any ideas or suggestions on how to get the correct
fiscal year and months will be greatly appreciated.
thanks
August 30, 2005 at 9:13 am
Whats not working?
Without sample data from the harvardclub.dbo.fiscalMonths table, its hard to say what the issue is.
I get these values for the parameters.
thismonth thisYear FiscalYear TestDate
----------- ----------- ----------- ------------------------
7 2005 2006 2005-08-30 08:55:40.073
August 30, 2005 at 10:13 am
Ok.
1. Unless you are setting multiple items with the same statement STOP using SELECT. USE SET instead. i.e SET @thisMonth = DATEPART(month, @testDate) - 1
2. Why do 2 different calls to the same data when you want to populate 2 different variables? (Where you are populating @endDate and @startDate). Use this SELECT @StartDate = startDate, @EndDate = endDate FROM ... instead.
3. When you are using IF THEN type logic use the following: IF @thisMonth >=7 BEGIN SET @fiscalYear = @fiscalYear + 1 END. This way you know what will be executed. Also you don't need parentheses around it (usually)..
Now.... Are you sure that you should ALWAYS set @thisMonth to really be LAST month? What happens if THIS month is JANUARY and LAST month is DECEMBER. You now have conflicting YEAR information...
Just a few thoughts.......
Good Hunting!
AJ Ahrens
webmaster@kritter.net
August 30, 2005 at 11:19 am
Thanks all. To clarify here is some sample data:
COMPARISON WITH LAST YEAR
-------------------------
ACTUAL ACTUAL
August August
2005 2004
Room sales $300,000 $220,000
Rooms Available 2,000 2,085
=============
COMPARISON WITH This YEAR's Budget
----------------------------------
Actual Budget
August August
2005 2005
Room sales $300,000 $200,000
Rooms Available 2,000 1,500
I am trying to write the code so that the Actual & Budget numbers for each year will be accurate under each column header. Our fiscal year(2006) begins July1, 2005 and ends June, 2006.
Right now the data is correct but the column header dates are wrong.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply