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

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0