title: SQL how group by fiscal quarter and year with a date field
body: <p>I am running SQL 2008, I have been tasked to create a report that groups 'vendor name' and certain information together, furthermore group it by fiscal year and their quarters. </p>
  
  <p>It went well up the the grouping by year, except that our fiscal start isnt on January but instead November. I have a date field called 'Expected Arrival Date' that is formatted as 'yyyy-mm-dd hh:mm;ss.mmm' I am not sure how i can group it to associate: </p>
  
  <p><strong>A.</strong> the fiscal year starting with November year before to the rest of the fiscal year ending at October (example fiscal year 2012 = beginning November 2011 -  End October 2012</p>
  
  <p><strong>B.</strong> The fiscal quarter Nov1- Jan31 , Feb1 - Apr30, May1-July31, Aug1-Oct31</p>
  
  <p>Here is the code I already have </p>
  
  <pre><code>SELECT 
      YEAR([Expected Arrival Date])as 'Year',
      MONTH([Expected Arrival Date]) as 'Month',
      [Vendor Name], 
      (SUM(CASE WHEN ([Days Past Due] = 0) THEN [Qty Received] ELSE 0 END) / SUM([Qty Received])) * 100  AS 'On-Time %',
      (SUM(CASE WHEN ([Days Past Due] &lt; 0) THEN [Qty Received] ELSE 0 END) / SUM([Qty Received])) * 100  AS 'Early %',
      (SUM(CASE WHEN ([Days Past Due] &gt; 0) THEN [Qty Received] ELSE 0 END) / SUM([Qty Received])) * 100  AS 'Late %',
      SUM([Qty Received]) aS 'TOTAL'
  
  FROM data_view 
  GROUP BY YEAR([Expected Arrival Date]),MONTH([Expected Arrival Date]),[Vendor Name]
  ORDER by YEAR([Expected Arrival Date]),MONTH([Expected Arrival Date])
  </code></pre>
  
  <p>If you have any helpful pointers to create a nice query it would also be appreciated. </p>
  
  <p>Edit**
  The structure of the data_view is as follows</p>
  
  <pre><code>    SELECT        [Receipt #], [Vendor Code], [Vendor Name], [PO Applied], [PO Line #], [Item Code], [Item Description], [Expected Arrival Date], [Receiving Date], [Receiving Date Filter], 
                           [Qty Received], [Qty Ordered], [% Received], [PO Date], [Receipt User], DATEDIFF(dd, CAST(CAST([Expected Arrival Date] AS CHAR) AS DATETIME), 
                           CAST(CAST([Receiving Date] AS CHAR) AS DATETIME)) AS [Days Past Due]
  FROM            (SELECT        T1.RCPNUMBER AS [Receipt #], T1.VDCODE AS [Vendor Code], T1.VDNAME AS [Vendor Name], T4.PONUMBER AS [PO Applied], 
                                                      T3.DETAILNUM AS [PO Line #], T2.ITEMNO AS [Item Code], T2.ITEMDESC AS [Item Description], 
                                                      CASE WHEN T3.EXPARRIVAL = 0 THEN CASE WHEN T4.EXPARRIVAL = 0 THEN CAST(CAST(T4.DATE AS CHAR) AS DATETIME) 
                                                      ELSE CAST(CAST(T4.EXPARRIVAL AS CHAR) AS DATETIME) END ELSE CAST(CAST(T3.EXPARRIVAL AS CHAR) AS DATETIME) 
                                                      END AS [Expected Arrival Date], CAST(CAST(T1.DATE AS CHAR) AS DATETIME) AS [Receiving Date], CAST(CAST(T1.DATE AS CHAR) AS DATETIME) 
                                                      AS [Receiving Date Filter], T2.RQRECEIVED AS [Qty Received], T3.OQORDERED AS [Qty Ordered], 
                                                      ROUND(CASE WHEN T2.OQORDERED &lt;&gt; 0 THEN (T2.RQRECEIVED / T2.OQORDERED) * 100 ELSE 100 END, 2) AS [% Received], 
                                                      CAST(CAST(T4.DATE AS CHAR) AS DATETIME) AS [PO Date], T5.ENTEREDBY AS [Receipt User]
                            FROM            canada.dbo.PORCPH1 AS T1 INNER JOIN
                                                      canada.dbo.PORCPH2 AS T5 ON T1.RCPHSEQ = T5.RCPHSEQ INNER JOIN
                                                      canada.dbo.PORCPL AS T2 ON T2.RCPHSEQ = T1.RCPHSEQ INNER JOIN
                                                      canada.dbo.POPORL AS T3 ON T3.PORHSEQ = T2.PORHSEQ AND T3.PORLSEQ = T2.PORLSEQ INNER JOIN
                                                      canada.dbo.POPORH1 AS T4 ON T4.PORHSEQ = T3.PORHSEQ) AS tblReceips
  </code></pre>