title: Fiscal Year To-Date in Where Clause (T-SQL)
body: <p>Company's Fiscal Year: July 1 - June 30</p>
  
  <p>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.</p>
  
  <pre><code>    Mix of pseudocode and SQL:
      Where
        If datepart(mm,getdate()) - 1 &lt; 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())          
  </code></pre>
  
  <p>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?  </p>
  
  <p>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.)</p>
  
  <p>UPDATE, Fiscal year goes forward, so July 1, 2010, is Fiscal Year 2011.</p>
  
  <p>I think this works:</p>
  
  <pre><code>    Year(dateadd(month,6,htx.tx_post_date)) = Year(DateAdd(Month, 5, GetDate()))
  </code></pre>
  
  <p>Feeback?</p>
  
  <p>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 </p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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.  </p>
  
  <p>Thanks for all the previous help.  </p>