CODEWITHSUNDEEP
sqlsql-server
Muhammad Mobeen Qureshi
Muhammad Mobeen Qureshi

Reputation: 1182

Getting percentages on the basis of Users and drill down by months

I want to print a report in this format: (these are sample values)

Desired Result

So far, I am able to achieve this:

What I achieved

Here is my query:

SELECT t.DisplayName,
        ROUND((CAST(t.MarksObt AS FLOAT)  / CAST(t.TotalMarks AS FLOAT) * 100), 0) AS Percentage,
        t.EvaluationDate
FROM (SELECT u.DisplayName, 
        e.Id,
        SUM(ea.QuestionMaxMarks) AS TotalMarks, 
        SUM(CASE WHEN ea.Achieve = 1 THEN ea.QuestionMaxMarks ELSE 0 END) AS MarksObt,
        e.EvaluationDate
FROM Users u
JOIN Evaluation e
ON u.UserID = e.PhoneBankerId
JOIN EvaluationAnswer ea
ON e.Id = ea.EvaluationId
JOIN Question q
ON ea.QuestionId = q.Id
JOIN QuestionSection qs
ON q.SectionId = qs.Id
WHERE qs.[Type] = 1
AND e.Id > 31
AND ea.Achieve <> 3
GROUP BY e.Id, u.DisplayName, e.EvaluationDate) As t

Any help is much appreciated. Thanks

Upvotes: 0

Views: 43

Answers (2)

M.Ali
M.Ali

Reputation: 69574

Also use DECIMAL instead of FLOAT, float data type may give you unexpected results as it is an approximate data type and should only be used when working with approximate values like value of Pi and mass of earth etc. 

SELECT DisplayName
      , CAST([JAN] AS VARCHAR(10))+ '%' AS [JAN]
      , CAST([FEB] AS VARCHAR(10))+ '%' AS [FEB]
      , CAST([MAR] AS VARCHAR(10))+ '%' AS [MAR]
      , CAST([APR] AS VARCHAR(10))+ '%' AS [APR]
      , CAST([MAY] AS VARCHAR(10))+ '%' AS [MAY]
      , CAST([JUN] AS VARCHAR(10))+ '%' AS [JUN]
      , CAST([JUL] AS VARCHAR(10))+ '%' AS [JUL]
      , CAST([AUG] AS VARCHAR(10))+ '%' AS [AUG]
      , CAST([SEP] AS VARCHAR(10))+ '%' AS [SEP]
      , CAST([OCT] AS VARCHAR(10))+ '%' AS [OCT]
      , CAST([NOV] AS VARCHAR(10))+ '%' AS [NOV]
      , CAST([DEC] AS VARCHAR(10))+ '%' AS [DEC]
FROM
 ( 
   -- Your query here but get data in following format
    SELECT DisplayName
          ,Percentage 
          ,UPPER(LEFT(DATENAME(MONTH, EvaluationDate), 3)) AS EvaluationDate
    FROM @TABLE
 )t
PIVOT (AVG(Percentage)
       FOR EvaluationDate
       IN ([JAN],[FEB],[MAR],[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])
      )p

Upvotes: 1

Michał Komorowski
Michał Komorowski

Reputation: 6238

You need to use a PIVOT operator. Here is an example based on Northwind database which returns information about total value of orders for each client in every month:

;WITH PivotData AS ( SELECT CustomerID, DATENAME(M,OrderDate) as Month, Freight FROM Orders)
SELECT CustomerID,
    January,February,March,
    April,May,June,
    July,August,September,
    October,November,December
FROM PivotData
PIVOT( SUM(Freight) FOR Month IN 
    (January,February,March,
    April,May,June,
    July,August,September,
    October,November,December) ) AS P;

Here is a general structure of a query with PIVOT operator that was used by me:

WITH PivotData AS
(
    SELECT <grouping column>, <spreading column>, <aggregation column>
    FROM <source table>
)
SELECT <select list>
FROM PivotData
PIVOT( <aggregate function >(<aggregation column>)
FOR <spreading column> IN (<distinct spreading values>) ) AS P;

In your case DisplayName is a grouping column, a month part of EvaluationDate is a spreading column and Percentage is an aggregation column.

Upvotes: 1

Related Questions