jnoel10 jnoel10 - 1 month ago 8
MySQL Question

SQL how group by fiscal quarter and year with a date field

Hello all and thank you for your time.

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.

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:

A. 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

B. The fiscal quarter Nov1- Jan31 , Feb1 - Apr30, May1-July31, Aug1-Oct31

Here is the code I already have

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] < 0) THEN [Qty Received] ELSE 0 END) / SUM([Qty Received])) * 100 AS 'Early %',
(SUM(CASE WHEN ([Days Past Due] > 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])


Any help would be greatly appreciated and if you have any helpful pointers to create a nice query it would also be appreciated .

Thank you.

Edit**
The structure of the data_view is as follows

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 <> 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

Answer

Something like this should work:

SELECT 
    YEAR([Expected Arrival Date]) + CASE
        WHEN MONTH([Expected Arrival Date]) IN (11, 12) THEN 1
        ELSE 0
    END As FiscalYear,
    CASE
        WHEN MONTH([Expected Arrival Date]) IN (11, 12, 1) THEN 1
        WHEN MONTH([Expected Arrival Date]) IN (2, 3, 4) THEN 2
        WHEN MONTH([Expected Arrival Date]) IN (5, 6, 7) THEN 3
        ELSE 4
    END As FiscalQuarter,
    ...
GROUP BY
    YEAR([Expected Arrival Date]) + CASE
        WHEN MONTH([Expected Arrival Date]) IN (11, 12) THEN 1
        ELSE 0
    END,
    CASE
        WHEN MONTH([Expected Arrival Date]) IN (11, 12, 1) THEN 1
        WHEN MONTH([Expected Arrival Date]) IN (2, 3, 4) THEN 2
        WHEN MONTH([Expected Arrival Date]) IN (5, 6, 7) THEN 3
        ELSE 4
    END,
    [Vendor Name]
ORDER BY
    FiscalYear,
    FiscalQuarter
;
Comments