如何从datetime获取Quarter [英] How to get Quarter from datetime

问题描述

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 )

推荐答案

检查这个...

Hi,

Check this...

SELECT DATEPART(q,GETDATE())

Datepart [ ^ ]



希望这会对你有所帮助。



干杯

Datepart[^]

Hope this will help you.

Cheers

这是每月生产一次。我正在开发季刊，并尽快更新（我已在下面更新）。我正在使用一个临时表，你必须改变你的表和列。

This is to produce monthly one. I am working on Quarterly one and update you soon ( I have updated it below). I am using a temporary table that you have to change to your table and columns.

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！