Problem z query SQL

Czesc,

Dopiero sie ucze SQL i chcialbym sie dowiedziec jak poprawic ten kod SQl aby dzialal poprawnie.

Chce obliczyc % promotorow i detraktorow dla kazdego miesiaca.

Napisalem query ale dziala tylko dla pojedynczych miesiecy natomiast jak chce dla 2 lub wiecej pokazuje mi po 4 linie dla kazdego miesiaca?

Poprawny wynik:

67, January, 1, 2018 
71, February,2, 2018

Zly wynik:

SUMMARY	MONTH NAME	MONTH	YEAR
67	January	1	2018
71	January	1	2018
63	January	1	2018
68	January	1	2018
70	February	2	2018
75	February	2	2018
67	February	2	2018
71	February	2	2018

Dziekuje za pomoc,

SELECT ROUND (100*(T2.[FCRR]-T1.[FCRR])/T3.[FCRR],0) AS [SUMMARY], T2.[MONTH_NAME] as [MONTH NAME], T1.[MONTH_NUMBER] as [MONTH], T3.[YEARS] as [YEAR]

FROM

--DETRACTORS
    (SELECT cast(count(*) AS FLOAT) AS [FCRR]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM dbo.[PL_Survey]
    WHERE MONTH([TICKET_CLOSED]) IN ('1','2') and [QUESTION_3_SCORE] <7 AND SUPPORT_GROUP IN ('PEOPLELINK_DACH')
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])) AS T1,


 --PROMOTORS 
      (SELECT cast(count(*) AS FLOAT) AS [FCRR]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM dbo.[PL_Survey]
    WHERE MONTH([TICKET_CLOSED]) IN ('1','2') AND SUPPORT_GROUP IN ('PEOPLELINK_DACH') and [QUESTION_3_SCORE] > 8
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])) AS T2,


--CASES COUNT
      (SELECT cast(count(*) AS FLOAT) AS [FCRR]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM dbo.[PL_Survey]
    WHERE MONTH([TICKET_CLOSED]) IN ('1','2') AND SUPPORT_GROUP IN ('PEOPLELINK_DACH')
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])) AS T3

Nie ma sensu brać trzech podzapytań do policzenia wystąpień rekardów, dodatkowo robisz cross joina tych 3 tabel
Zacznij może od poprawienia zapytania:

SELECT 
    DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
    ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
    ,YEAR([TICKET_CLOSED]) AS [YEARS]
	,sum(case when SUPPORT_GROUP IN ('PEOPLELINK_DACH') then 1 else 0 end) AS [CASES COUNT]
	,SUM(case when [QUESTION_3_SCORE] <7 AND SUPPORT_GROUP IN ('ddd_DACH') then 1 else 0 end) [DETRACTORS]
	,SUM(case when [QUESTION_3_SCORE] <7 AND SUPPORT_GROUP IN ('ddd_DACH') then 1 else 0 end) [PROMOTORS]
FROM 
	dbo.[PL_Survey]
WHERE 
	MONTH([TICKET_CLOSED]) IN ('1','2')  
 GROUP BY 
	Month([TICKET_CLOSED])
    ,YEAR([TICKET_CLOSED])
    ,DATENAME(MONTH, [TICKET_CLOSED]

Panczo napisał(a):

Nie ma sensu brać trzech podzapytań do policzenia wystąpień rekardów, dodatkowo robisz cross joina tych 3 tabel
Zacznij może od poprawienia zapytania:

SELECT 
    DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
    ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
    ,YEAR([TICKET_CLOSED]) AS [YEARS]
	,sum(case when SUPPORT_GROUP IN ('PEOPLELINK_DACH') then 1 else 0 end) AS [CASES COUNT]
	,SUM(case when [QUESTION_3_SCORE] <7 AND SUPPORT_GROUP IN ('ddd_DACH') then 1 else 0 end) [DETRACTORS]
	,SUM(case when [QUESTION_3_SCORE] <7 AND SUPPORT_GROUP IN ('ddd_DACH') then 1 else 0 end) [PROMOTORS]
FROM 
	dbo.[PL_Survey]
WHERE 
	MONTH([TICKET_CLOSED]) IN ('1','2')  
 GROUP BY 
	Month([TICKET_CLOSED])
    ,YEAR([TICKET_CLOSED])
    ,DATENAME(MONTH, [TICKET_CLOSED]

Dzieki, masz racje:)

Pomoglo i dziala :)