# Problem z query SQL

0

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``````
0

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]``````
0
##### 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 :)