SQL Server Performance Forum – Threads Archive

cross tab reports – grouping 2 times

I would like to have the report result as follows: For each employee in the list, for current year, and lastyear – display all the Medical, Dental claims. How do I write a sql query that brings results that is grouped by year, and by count(claims) in one row for each employee? Google search or SQL server online help file only shows examples for grouping at one level. EmployeeName 2006 …………. 2005
………… Dental Medical …Dental Medical
Tom…………. 3…… 5 ……4 ……10
John………….1…… 10 ……9 ……20 Thank you.

---

Please post table structures. It makes things easier. John

---

Here are the tables and data in the tables. Employee
Id…….Name
1 …….John
2 …….Tom Claim
Id……EmployeeId……..claimDate………..claimType
1……..1……………..05/05/06………..Medical
2……..1……………..09/09/05………..Dental
3……..2……………..04/10/06………..Medical
4……..1……………..02/01/05………..Dental
5……..2……………..03/12/06………..Dental Any more questions, please let me know. Thanks.

---

Can someone provide a sql query to this scenario? Thanks.

---

select e.Name,
count(case when year(c.claimDate) = 2005 and c.claimType = ‘Medical’ then 1 end) as [2005_Medical],
count(case when year(c.claimDate) = 2005 and c.claimType = ‘Dental’ then 1 end) as [2005_Dental],
count(case when year(c.claimDate) = 2006 and c.claimType = ‘Medical’ then 1 end) as [2006_Medical],
count(case when year(c.claimDate) = 2006 and c.claimType = ‘Dental’ then 1 end) as [2006_Dental]
from Employee e inner join Claim c
on e.id = c.id
group by e.Name
KH

---

As in the subject of this topic, read about cross-tab reports in sql server help file Madhivanan Failing to plan is Planning to fail

---

Thank you, KH Tan. The sql query works fine.

---

Also read my previous reply to know more informations Madhivanan Failing to plan is Planning to fail

---

]]>