How to get the sum of Quarters Amounts

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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

Month_Quarter	Zugang	Total_Amount_Zugang	 Abgang	  Total_Amount_Abgang
    Q1	          4	          51998	           1	      2314
    Q2	          1	          2000	           1	      3004
    Q3	          3	          26000            0	       0
    Q4	          1	          1366	           1	      1500

My Statement:

SQL

SELECT 'Q'+cast([Month_Quarter] as varchar) Month_Quarter,COALESCE([Zugang],0) Zugang, COALESCE([Abgang],0) Abgang,[Total Amount] 
FROM
(
   SELECT DATEPART(QUARTER,MonthCol) [Month_Quarter],
          Ab_Zugang,
          Count(NoProjects) NoProjects,
          sum([Amount]) AS [Total Amount]
   FROM tblProjectData
   GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
 
 ) proj
 PIVOT (SUM(NoProjects) FOR Ab_Zugang IN (Zugang, Abgang)) As pvt
 ORDER BY Month_Quarter

BUT This is what my results looks like with the query above:

Month_Quarter	Zugang	Abgang	Total_Amount
    Q1	          0	       1	  2314
    Q1	          4	       0	  51998
    Q2	          1	       0	  2000
    Q2	          0	       1	  3004
    Q3	          0	       0	  NULL
    Q3	          3	       0	  26000
    Q4	          1	       0	  1366
    Q4	          0	       1	  1500

What I'm trying to realize ist: I want to know how many Zugang or Abgang are in each Quarter and the total amount to them. How can I edit the query to have my expectation results?

Posted 22-Apr-15 21:43pm

mikybrain1

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-04-22T23:03:00

Try this:

SQL

DECLARE @tmp TABLE(MonthCol	DATE,  [NoProjects] INT, [Amount] INT, [Ab_Zugang] VARCHAR(30))

INSERT INTO @tmp
VALUES('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')

SELECT [Month_Quarter] AS [Quarter], COALESCE(SUM([Zugang]),0) Zugang, SUM([ZugangAmount]) [ZugangAmount], COALESCE(SUM([Abgang]),0) Abgang, SUM([AbgangAmount]) [AbgangAmount]
FROM
(
   SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) [Month_Quarter],
		CASE WHEN Ab_Zugang = 'Zugang' THEN COUNT(NoProjects) ELSE 0 END AS [Zugang], 
		CASE WHEN Ab_Zugang = 'Zugang' THEN SUM([Amount])  ELSE 0 END AS [ZugangAmount],
		CASE WHEN Ab_Zugang = 'Abgang' THEN COUNT(NoProjects) ELSE 0 END AS [Abgang], 
		CASE WHEN Ab_Zugang = 'Abgang' THEN SUM([Amount])  ELSE 0 END AS [AbgangAmount]
   FROM @tmp
   GROUP BY DATEPART(QUARTER,MonthCol), Ab_Zugang
 ) dt
 GROUP BY [Month_Quarter]

Result:

Quarter	Zugang	ZugangAmount	Abgang	AbgangAmount
Q1		4		51998			1		2314
Q2		1		2000			1		3004
Q3		2		26400			0		0
Q4		1		1366			1		1500

[EDIT]

The same result you can achieve by joining two pivots:

SQL

SELECT t1.[Quarter], t1.Zugang, t2.Zugang AS [ZugangAmount], t1.Abgang, t2.Abgang AS  [AbgangAmount]
FROM(
    SELECT [Quarter], [Abgang], [Zugang]
    FROM
    (
       SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) [Quarter], [Ab_Zugang], NoProjects
       FROM @tmp
     ) dt1
     PIVOT(COUNT(NoProjects) FOR [Ab_Zugang] IN ([Abgang], [Zugang])) AS pvt1
) AS t1 INNER JOIN
    (
     SELECT [Quarter], [Abgang], [Zugang]
     FROM (
        SELECT 'Q'+CONVERT(VARCHAR(5), DATEPART(QUARTER,MonthCol)) AS [Quarter], [Ab_Zugang], [Amount]
        FROM @tmp
        ) dt2
     PIVOT(SUM([Amount]) FOR [Ab_Zugang] IN ([Abgang], [Zugang])) pvt2
     ) AS t2 ON t1.[Quarter] = t2.[Quarter]