Awais Sharafat Awais Sharafat - 1 month ago 12
ASP.NET (C#) Question

CrossTab SQL Server query

My select query:

SELECT
COUNT(*) * 500 AS TotalFee,
ISNULL(Employee.EFName, '') + ' ' + ISNULL(Employee.EMName, '') + ' ' + ISNULL(Employee.ELName, '') as Name,
CAST(MONTH(sdatetime) AS int) as Months
FROM
Patient_Services
INNER JOIN
Employee ON Patient_Services.Doctor_ID = Employee.EmpID
WHERE
(Patient_Services.S_ID = 1606)
AND CAST(MONTH(sdatetime) AS INT) BETWEEN 1 AND 6
GROUP BY
CAST(MONTH(sdatetime) AS INT), Employee.EFName, Employee.EMName, Employee.ELName
ORDER BY
CAST(MONTH(Patient_Services.sdatetime) AS INT)


Result

TotalFee | Name | Months
38000 | AKIF DILSHAD | 1
1500 | MATEEN AKRAM | 1
1500 | AKIF DILSHAD | 2
2200 | AKIF DILSHAD | 3
1500 | NASERA BHATTI | 4
500 | NASERA BHATTI | 5
500 | NASERA BHATTI | 6
12000 | AKIF DILSHAD | 6


My desired results:

Name |Jan |Feb |Mar |Apr |May |June |Total |60% |40%
AKIF DILSHAD |38000 |1500 |2200 |0 |0 |12000 |53700 |32220 |21480
MATEEN AKRAM |1500 |0 |0 |0 |0 |0 |1500 |900 |600
NASERA BHATTI |0 |0 |0 |1500 |500 |500 |2500 |1500 |1000


Please help to convert my query to get my desired result.

Answer

Please try this:

WITH cte as(
SELECT        COUNT(*)*500 AS TotalFee, ISNULL(Employee.EFName,'') +' '+ ISNULL(Employee.EMName,'') +' '+ ISNULL(Employee.ELName,'') as Name,CAST(MONTH(sdatetime) AS int) as Months
FROM            Patient_Services INNER JOIN
                         Employee ON Patient_Services.Doctor_ID = Employee.EmpID
WHERE        (Patient_Services.S_ID = 1606) AND CAST(MONTH(sdatetime) AS int) Between 1 AND 6
GROUP BY CAST(MONTH(sdatetime) AS int),Employee.EFName, Employee.EMName, Employee.ELName
--order by CAST(MONTH(Patient_Services.sdatetime) AS int)
)

    SELECT [NAME],
        SUM(CASE Months WHEN 1 THEN TotalFee ELSE 0 End) as 'Jan' ,
        SUM(CASE Months WHEN 2 THEN TotalFee ELSE 0 End) as 'Feb' ,
        SUM(CASE Months WHEN 3 THEN TotalFee ELSE 0 End) as 'Mar' ,
        SUM(CASE Months WHEN 4 THEN TotalFee ELSE 0 End) as 'Apr' ,
        SUM(CASE Months WHEN 5 THEN TotalFee ELSE 0 End) as 'May' ,
        SUM(CASE Months WHEN 6 THEN TotalFee ELSE 0 End) as 'Jun' ,
        SUM(TotalFee) AS Total,
        SUM(TotalFee)*.6 as [60%],
        SUM(TotalFee)*.4 as [40%]
      FROM  CTE
      GROUP BY [Name]