How to use data to generate a report

Hi,
I have one problem in my project. Generating the report plz help me.

I have one table called Emp, which consists of fields, empid,tdate,attn,reason.

Emp
-
empid | tdate | attn | reason

2281 6/3/2006 Present null
2282 6/3/2006 Tour Hyderabad

2283 6/3/2006 Present null
2281 6/4/2006 Present null
2282 6/4/2006 Present null
2283 6/4/2006 Tour null

I want to generate a report as given below based on the date submitted by the user.

Frm Date: 6/3/2006 To Date: 6/4/2006

empid Present Tour Absent No.of working days
2281 2 2
2282 1 1 1
2283 1 1 1

Please write the query and reply back to me. ASAP.

Thanx in advance.

This is typically called crosstab reports. Here is how you will do it SQL 2000.

Select EmpId,
Sum(Case When Attn = 'Present' Then 1 ELSE 0 END) As "Present",
Sum(Case When Attn = 'Tour' Then 1 ELSE 0 END) As "Tour",
Sum(Case When Attn = 'Absent' Then 1 ELSE 0 END) As "Absent",
FROM Emp
WHERE tDate >= '3 Jun 2006' AND tDate <= '4 Jun 2006'
GROUP BY EmpId
ORDER BY EmpId

The key is the SUM(CASE..END) section. The CASE statement checks if the record's Attn field contains the given value. If it does then 1 is added to the sum, otherwise 0 is added. So for your data...

2281 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/3/2006 Tour 0 for present, 1 for tour and 0 for absent
2283 6/3/2006 Present 1 for present, 0 for tour and 0 for absent
2281 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2282 6/4/2006 Present 1 for present, 0 for tour and 0 for absent
2283 6/4/2006 Tour 0 for present, 1 for tour and 0 for absent

So, when you start aggregating the data on emp id, you will get the data you need. However, this type of solution is suitable for known columns, if you dynamic columns then you will have to check for other alternatives.

SQL 2005 has new PIVOT predicates that you can explore in books online. It simplifies the SQL a lot.

|||

Hi Badri Narayanan,

I'm glad that you have answered my question and suggested the PIVOT predicates method to me. I will learn PIVOT predicates and try to solve my problem in easy way. If you have any suggestions plz forward to me.

Thanx for response.

Take care, Bye.

Regards

Ram