Programming Fiscal & Calendar months

  • 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

     

     

      

     

     

  • 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

  • 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

  • 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