Fiscal Year To-Date in Where Clause (T-SQL)

1
votes

Company's Fiscal Year: July 1 - June 30

I have a query where I am trying to capture aggregate # of units and $ revenue by product and cost center for the fiscal year-to-date. It will run on the 1st of the month and look through the last day of the previous month. Fiscal year does not appear in the report - it is criteria.

    Mix of pseudocode and SQL:
    Where
      If datepart(mm,getdate()) - 1 < 7
        THEN 
           transaction_post_date BETWEEN 7/1/ previous year AND dateadd(day,-(day(getdate()),getdate())          
        Else 
           transaction_post_date BETWEEN 7/1/current year AND dateadd(day,-(day(getdate()),getdate())

Am I on the write track? How do I write the SQL for a specific date on a year that depends on SQL - 7/1/current year?

I am weak using variables and do not even know if I have access to create them on the SQL Server DB, which is rather locked down. Definitely can't create a function. (I'm a business analyst.)

UPDATE, Fiscal year goes forward, so July 1, 2010, is Fiscal Year 2011.

I think this works:

    Year(dateadd(month,6,htx.tx_post_date)) = Year(DateAdd(Month, 5, GetDate()))

Feeback?

And now I've been asked to add Fiscal Year-To-Date fields for quantity and revenue to the following query which gave me totals for 

    Select 
      inv.ITEM_CODE 
    , inventory.ITEM_NAME 
    , cc.COST_CENTER_CODE 
    , tx.REV_CODE_ID 
    , tx.PRICE 
    , tx.ITEM_SALE_ID
    , sum(tx.quantity)
    , sum(tx.amount)

    from
    transactions tx
    inner join inventory inv on inv.item_id = tx.item_id
    left outer join cost_center cc on cc.cost_center_id = tx.cost_center_id

    where 
    DATEPART(mm, tx.tx_date) = DATEPART(mm,dateadd(m,-1,getdate()))
and DATEPART(yyyy, tx.tx_date) = DATEPART(yyyy,dateadd(m,-1,getdate()))

    group by 
      inv.ITEM_CODE 
    , inventory.ITEM_NAME 
    , cc.COST_CENTER_CODE 
    , tx.REV_CODE_ID 
    , tx.PRICE 
    , tx.ITEM_SALE_ID

I need to add the fiscal year-to-date quantity and and amount columns to this report. Would a correlated subquery by the way to go? Would the joins be tricky? I've never used a subquery with an aggregation/grouping query.

Thanks for all the previous help.

3
sqlsql-servertsql
Step one to knowing if you can do something is to try it. You should have no problems doing DECLARE @myVar AS INT, etc. – MatBailie
You really don't want to use BETWEEN for date ranges, especially when doing things like end-of-month/end-of-day checks (it's easiest to do 'less than 1st of next month/start of day', especially for timestamps). Also, you might want to look into creating a calendar file regardless, although my instinct is telling me there is a way to do this with a set of functions (you may need to define some yourself, though). – Clockwork-Muse

3 Answers

2
votes

Here is how I would do it if I needed to group by Fiscal Year:

Group by Year(DateAdd(Month, -6, TransactionDate))

May be not exactly it, but you get the idea.

0
votes

I would add a calculated column to your table called FiscalYear (with the proper calculation) and select based on that column

0
votes

I believe the easiest way is to do this in two steps. Use the WHERE Clause to filter your YTD and then a GROUP BY to group by FY. Since your FY begins in July(7) then increment the FY if the month is greater than June(6).

WHERE CLAUSE:

WHERE
DATEDIFF(DAY, transaction_post_date,  Cast(Month(GetDate()) as varchar) +
'/' + Cast(Day(GetDate()) as varchar) + '/' + CAST(Case WHEN 
MONTH(transaction_post_date) > 6 then YEAR(transaction_post_date) + 1 else 
Year(transaction_post_date) end as varchar)) >=0

GROUP BY CLAUSE:

GROUP BY CASE WHEN MONTH(transaction_post_date) > 6 then 
Year(transaction_post_date) + 1 else YEAR(transaction_post_date) end