本文介绍了如何从datetime获取Quarter的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
I have data like :
My table
Month Nr of Projects Amount Ab-,Zugang
2014-05-01 8 3004 Abgang
2014-02-01 5 2314 Abgang
2014-11-01 10 1366 Zugang
2014-01-01 1 37443 Zugang
2014-07-01 7 18400 Zugang
2014-12-01 3 1500 Abgang
2014-06-01 11 2000 Zugang
2014-09-01 9 8000 Zugang
2014-03-01 5 2555 Zugang
2014-01-01 2 5000 Zugang
2014-03-01 7 7000 Zugang
My final results should be like
Month Ab-,Zugang[Zugang] Ab-,Zugang[Abgang]
January 2 0
Febuary 1 1
March 2 0
….
Month[Quarter] Ab-,Zugang[Zugang] Ab-,Zugang[Abgang]
Q1 4 1
Q2 1 1
Q3 2 0
Q4 1 1
How can I go about it? I have no idea how
我尝试过这样的事情,但我得错了记录
I have tried something like this but I'm getting the wrong records
SELECT CASE WHEN [DATEPART] IS NULL THEN 'Quarter' + CONVERT(VARCHAR(10),QQ)
ELSE [DATEPART] END [Month], [Zugänge]
FROM (
SELECT DATENAME(mm, Month ) [DATEPART],DATENAME(qq, Month ) [QQ] , COUNT([Zu-, Abgang]) [Zugänge]
FROM Table
WHERE [Ab-, Zugange] = 'Zugange'
GROUP BY DATENAME(qq, Month ), DATENAME(mm, Month ) WITH ROLLUP )A
WHERE ( [DATEPART] IS NOT NULL OR QQ IS NOT NULL )
推荐答案
SELECT DATEPART(q,GETDATE())
[]
希望这会对你有所帮助。
干杯
Datepart[^]
Hope this will help you.
Cheers
SELECT [Month],COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang
FROM
(
SELECT DATENAME(MONTH,MonthCol) [Month],
DATEPART(MONTH,MonthCol) [MonthNo],
Ab_Zugang,
Count(NoProjects) NoProjects
FROM #ProjectData
GROUP BY DATENAME(MONTH,MonthCol),DATEPART(MONTH,MonthCol), Ab_Zugang
) proj
PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
ORDER BY MonthNo
输出结果是
The output is
Month Zugang Abgang
January 2 0
February 0 1
March 2 0
May 0 1
June 1 0
July 1 0
September 1 0
November 1 0
December 0 1
这是按季度计算的。
This is for Quarterly one.
SELECT 'Q'+cast([Month_Quarter] as varchar) Month_Quarter,COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang
FROM
(
SELECT DATEPART(QUARTER,MonthCol) [Month_Quarter],
Ab_Zugang,
Count(NoProjects) NoProjects
FROM #ProjectData
GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
) proj
PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
ORDER BY Month_QuarterThe output is
输出为
The output is
Month_Quarter Zugang Abgang
Q1 4 1
Q2 1 1
Q3 2 0
Q4 1 1
这篇关于如何从datetime获取Quarter的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!