Calculating Survey Scores by Month and YTD

Transact-SQL
1

I need some help on calculating survey scores by account & month, with a YTD figure for each account each month as well. Similar to NPS, the score is calculated as: Green ratings as % of total responses for account in period - Red ratings as % of total responses for account in period . Amber ratings are included the total response number part of the calculation.

The other requirement is that if an account appears anywhere in the data, then that account must be shown and scored in all months even if there are no responses for them in one or more months - they would be shown as a score of 0 for that month, but they might have a YTD score if there was data for them in previous months.

The data is received monthly from the start of the financial year, and the file always includes the previous months data back to the start of the FY. Responses are always dated the last day of the month they are received in.

The data looks like this:

+------------+---------+-----+-------+-------+-----------+
|    Date    | Account | Red | Amber | Green | Responses |
+------------+---------+-----+-------+-------+-----------+
| 30/04/2024 |     200 |   0 |     0 |     1 |         1 |
| 30/04/2024 |     200 |   0 |     0 |     1 |         1 |
| 30/04/2024 |     201 |   0 |     0 |     1 |         1 |
| 30/04/2024 |     201 |   1 |     0 |     0 |         1 |
| 31/05/2024 |     201 |   0 |     0 |     1 |         1 |
| 31/05/2024 |     201 |   0 |     0 |     1 |         1 |
| 31/05/2024 |     201 |   0 |     1 |     0 |         1 |
| 31/05/2024 |     201 |   1 |     0 |     0 |         1 |
| 31/05/2024 |     202 |   0 |     0 |     1 |         1 |
| 30/06/2024 |     200 |   0 |     0 |     1 |         1 |
| 30/06/2024 |     200 |   1 |     0 |     0 |         1 |
| 30/06/2024 |     200 |   0 |     1 |     0 |         1 |
| 30/06/2024 |     202 |   0 |     0 |     1 |         1 |
| 30/06/2024 |     202 |   0 |     0 |     1 |         1 |
+------------+---------+-----+-------+-------+-----------+

The result I'm looking to achieve is:

+------------+---------+-----------+---------------+--------+------------+
|    Date    | Account | Responses | Responses_YTD | Rating | Rating_YTD |
+------------+---------+-----------+---------------+--------+------------+
| 30/04/2024 |     200 |         2 |             2 |    100 | 100        |
| 30/04/2024 |     201 |         2 |             2 |     50 | 50         |
| 30/04/2024 |     202 |         0 |             0 |      0 | 0          |
| 31/05/2024 |     200 |         0 |             2 |      0 | 100        |
| 31/05/2024 |     201 |         4 |             6 |     25 | 16.7       |
| 31/05/2024 |     202 |         1 |             1 |    100 | 100        |
| 30/06/2024 |     200 |         3 |             5 |     50 | 40         |
| 30/06/2024 |     201 |         0 |             6 |      0 | 16.7       |
| 30/06/2024 |     202 |         2 |             3 |    100 | 100        |
+------------+---------+-----------+---------------+--------+------------+

Here to create the sample table & data:

CREATE TABLE #Ratings(
	[Date] [date] NULL,
	[Account] [varchar](10) NULL,
	[Red] [int] NULL,
	[Amber] [int] NULL,
	[Green] [int] NULL,
	[Responses] [int] NULL
) 

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-04-30' AS Date), N'200', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-04-30' AS Date), N'200', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-04-30' AS Date), N'201', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-04-30' AS Date), N'201', 1, 0, 0, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-05-31' AS Date), N'201', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-05-31' AS Date), N'201', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-05-31' AS Date), N'201', 0, 1, 0, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-05-31' AS Date), N'201', 1, 0, 0, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-05-31' AS Date), N'202', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-06-30' AS Date), N'200', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-06-30' AS Date), N'200', 1, 0, 0, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-06-30' AS Date), N'200', 0, 1, 0, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-06-30' AS Date), N'202', 0, 0, 1, 1)

INSERT #Ratings ([Date], [Account], [Red], [Amber], [Green], [Responses]) VALUES (CAST(N'2024-06-30' AS Date), N'202', 0, 0, 1, 1)

Any assistance would be appreciated.

Many thanks
Martyn

2

hi

hope this helps

;WITH MonthlyData AS (
    SELECT 
        Date,
        Account,
        SUM(Red) AS RedCount,
        SUM(Amber) AS AmberCount,
        SUM(Green) AS GreenCount,
        SUM(Responses) AS TotalResponses
    FROM 
        SurveyData
    GROUP BY 
        Date, Account
),
AccountMonths AS (
    SELECT DISTINCT 
        Account
    FROM 
        SurveyData
),
DateMonths AS (
    SELECT DISTINCT 
        Date
    FROM 
        SurveyData
),
AllCombinations AS (
    SELECT 
        am.Account,
        dm.Date
    FROM 
        AccountMonths am
    CROSS JOIN DateMonths dm
),
MonthlyScores AS (
    SELECT 
        md.Date,
        md.Account,
        md.TotalResponses,
        (md.GreenCount * 1.0 / md.TotalResponses - md.RedCount * 1.0 / md.TotalResponses) AS MonthlyRating
    FROM 
        MonthlyData md
),
YTDData AS (
    SELECT 
        ac.Account,
        d.Date,
        SUM(m.TotalResponses) OVER (PARTITION BY ac.Account ORDER BY d.Date) AS ResponsesYTD,
        SUM(m.MonthlyRating) OVER (PARTITION BY ac.Account ORDER BY d.Date) / 
        SUM(m.TotalResponses) OVER (PARTITION BY ac.Account ORDER BY d.Date) * 100 AS RatingYTD
    FROM 
        AllCombinations ac
    LEFT JOIN MonthlyScores m ON ac.Account = m.Account AND ac.Date = m.Date
)
SELECT 
    Date,
    Account,
    COALESCE(TotalResponses, 0) AS Responses,
    COALESCE(ResponsesYTD, 0) AS Responses_YTD,
    COALESCE(Rating, 0) AS Rating,
    COALESCE(Rating_YTD, 0) AS Rating_YTD
FROM 
    YTDData
ORDER BY 
    Date, Account;
3

HI

Thanks but I get an error when running this against my data, I wasn't sure which cte the alias d was referring to:

Msg 4104, Level 16, State 1, Line 46
The multi-part identifier "d.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 47
The multi-part identifier "d.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 48
The multi-part identifier "d.Date" could not be bound.
Msg 4104, Level 16, State 1, Line 49
The multi-part identifier "d.Date" could not be bound.

4

change it to m.

5

OK, thanks - I've done that and also updated the final select statement but I think my joins are not correct as I have 3 null values for the months where 1 of the accounts have no responses:

image
image776×285 6.39 KB

Whereas those 3 rows with null date should be as below:

image

This is the code now:

;WITH MonthlyData AS (
SELECT
Date,
Account,
SUM(Red) AS RedCount,
SUM(Amber) AS AmberCount,
SUM(Green) AS GreenCount,
SUM(Responses) AS TotalResponses
FROM
SurveyData
GROUP BY
Date, Account
),
AccountMonths AS (
SELECT DISTINCT
Account
FROM
SurveyData
),
DateMonths AS (
SELECT DISTINCT
Date
FROM
SurveyData
),
AllCombinations AS (
SELECT
am.Account,
dm.Date
FROM
AccountMonths am
CROSS JOIN DateMonths dm
),
MonthlyScores AS (
SELECT
md.Date,
md.Account,
md.TotalResponses,
(md.GreenCount * 1.0 / md.TotalResponses - md.RedCount * 1.0 / md.TotalResponses) AS MonthlyRating
FROM
MonthlyData md
),
YTDData AS (
SELECT
ac.Account,
m.Date,
SUM(m.TotalResponses) OVER (PARTITION BY ac.Account ORDER BY m.Date) AS ResponsesYTD,
SUM(m.MonthlyRating) OVER (PARTITION BY ac.Account ORDER BY m.Date) /
SUM(m.TotalResponses) OVER (PARTITION BY ac.Account ORDER BY m.Date) * 100 AS RatingYTD
FROM
AllCombinations ac
LEFT JOIN MonthlyScores m ON ac.Account = m.Account AND ac.Date = m.Date
)

SELECT
y.Date,
y.Account,
COALESCE(ms.TotalResponses, 0) AS Responses,
COALESCE(y.ResponsesYTD, 0) AS Responses_YTD,
COALESCE(ms.MonthlyRating, 0) AS Rating,
COALESCE(y.RatingYTD, 0) AS Rating_YTD
FROM
YTDData y
LEFT OUTER JOIN MonthlyScores ms ON y.Account = ms.account AND y.Date = ms.date
ORDER BY
Date, Account;

6

I don't fully follow your stated logic on this:

"the score is calculated as: Green ratings as % of total responses for account in period - Red ratings as % of total responses for account in period."

I'll assume "score" is the same as "Rating"? [keep in mind, we know NOTHING about your data and the terms you are used to using to refer to it].
Then, taking the exact calc above, the Rating for 30 Apr 2014 of 201 would be 0 (1/2 - 1/2 = 0). But you have it listed as 50. So, is Rating a straight % of Green vs total?

Also, you cannot add ratios and get a good result. Thus, you must recalc the ytd totals not just SUM them.

7

You are correct. We refer to the how the customer rated our services in this transaction as a Rating(Red, Amber, Green). The score is the % of total responses that are Green less the % of total responses which are Red. Apologies for not making this clear. And the score for 201 30/04/2024 month & ytd is incorrect, it should indeed be 0.

8

Great, thanks for that follow up. I'll put some code together as soon as I get a chance.

9

I think this should at least be very close:


;WITH cteAllDates AS (
    SELECT DISTINCT Date
    FROM #Ratings
),
cteAllAccounts AS (
    SELECT DISTINCT Account
    FROM #Ratings
),
cteAllCombinations AS (
    SELECT Date, Account
    FROM cteAllDates
    CROSS JOIN cteAllAccounts
),
cteMonthlyTotals AS (
    SELECT 
        Date, Account,
        SUM(Red) AS Reds,
        SUM(Amber) AS Ambers,
        SUM(Green) AS Greens,
        1 AS Response,
        SUM(Responses) AS Responses,
        CAST((SUM(Green) * 100.0 / SUM(Responses)) - 
        (SUM(Red) * 100.0 / SUM(Responses)) AS decimal(4, 1)) AS Rating
    FROM #Ratings
    GROUP BY Account, Date
),
cteYTDTotals AS (
    SELECT ac.Date, ac.Account,
        mt.Reds, mt.Ambers, mt.Greens, mt.Responses, mt.Rating,
        SUM(mt.Reds) OVER(PARTITION BY ac.Account, YEAR(ac.Date) ORDER BY ac.Date) AS YTDReds,
        SUM(mt.Ambers) OVER(PARTITION BY ac.Account, YEAR(ac.Date) ORDER BY ac.Date) AS YTDAmbers,
        SUM(mt.Greens) OVER(PARTITION BY ac.Account, YEAR(ac.Date) ORDER BY ac.Date) AS YTDGreens,
        SUM(mt.Reds + mt.Ambers + mt.Greens) OVER(PARTITION BY ac.Account, YEAR(ac.Date) ORDER BY ac.Date) AS YTDResponses
    FROM cteAllCombinations ac
    LEFT OUTER JOIN cteMonthlyTotals mt ON mt.Account = ac.Account AND mt.Date = ac.Date
)
SELECT yt.Date, yt.Account,
    ISNULL(yt.Responses, 0) AS Responses,
    ISNULL(yt.YTDResponses, 0) AS YTDResponses,
    CASE WHEN ISNULL(yt.Responses, 0) = 0 THEN 0 ELSE COALESCE(yt.Rating, ca1.YTDRating, 0) END AS Rating,
    ISNULL(CAST((yt.YTDGreens * 100.0 / yt.YTDResponses) - 
         (yt.YTDReds * 100.0 / yt.YTDResponses) AS decimal(4, 1)), 0) AS YTDRating
FROM cteYTDTotals yt
CROSS APPLY (
    SELECT ISNULL(CAST((YTDGreens * 100.0 / YTDResponses) - (YTDReds * 100.0 / YTDResponses) AS decimal(4, 1)), 0) AS YTDRating
) AS ca1
ORDER BY yt.Date, yt.Account
1 Like
10

Thank you Scott.

We unfortunately had a bereavement in the family overnight and I am just back from the hospital. But I promise I will look at it as soon as I can and come back to you.

11

So sorry for your loss.

12

Thank you Scott.

I have been able to test this against some of our data and it looks good. I had to amend slightly as our financial year is April to March but otherwise seems to work well, so I'm going to mark this one as the answer.

I appreciate your help.

Kind regards
Martyn