SQL如何按会计季度和年份按日期字段分组 [英] SQL how group by fiscal quarter and year with a date field
问题描述
我正在运行SQL 2008,我的任务是创建一个将供应商名称"和某些信息分组在一起的报告,然后按会计年度和季度对其进行分组.
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.
按年份分组的进展很好,除了我们的财政开始日期不是1月而是11月.我有一个名为预计到达日期"的日期字段,其格式为"yyyy-mm-dd hh:mm; ss.mmm",我不确定如何将其分组以进行关联:
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.会计年度,从11月开始,到10月结束的其余财政年度(例如2012会计年度= 2011年11月开始-2012年10月结束)
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..财政季度11月1日至1月31日,2月1日至4月30日,5月1日至7月31日,8月1日至10月31日
B. The fiscal quarter Nov1- Jan31 , Feb1 - Apr30, May1-July31, Aug1-Oct31
这是我已经拥有的代码
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])
如果您有任何有用的指针来创建一个不错的查询,也将不胜感激.
If you have any helpful pointers to create a nice query it would also be appreciated.
编辑** data_view的结构如下
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
推荐答案
类似的方法应该起作用:
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
;
这篇关于SQL如何按会计季度和年份按日期字段分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!