Need Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Query

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2008-03-04 : 09:29:14

have a table with 3 fields - work type, status,date.

we need different worktypes as one column, count of status=pending as second column,count of status=disposed
as third column. second and third columns should be grouped by work type.
all this information is to be given for a particular period i.e b/w two dates.

plz help me to write the sql query

thanks
csk

Table and sample data

Create Table Work ( Wtype Varchar(10),
Status Varchar(10),
Wdate Datetime)

Insert into Work
Select 'A','P',Getdate()+1 Union all
Select 'B','P',Getdate() Union all
Select 'C','D',Getdate()-1 Union all
Select 'D','D',Getdate()+1 Union all
Select 'E','A',Getdate()+1 Union all
Select 'F','A',Getdate()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 09:31:42

[code]SELECT WorkType,
SUM(CASE WHEN status='P' THEN 1 ELSE 0 END) AS PendingCount,
SUM(CASE WHEN status='D' THEN 1 ELSE 0 END) AS DisposedCount
FROM Work
Where WDate BETWEEN @Date1 AND @Date2
GROUP BY WorkType[/code]

@Date1 and @Date2 are parameters holding date values