Filtering in Pivot table - 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)

Filtering in Pivot table

Author

Topic

pandiyan
Starting Member

1 Post

Posted - 2009-08-20 : 02:33:04

Hi ,

I have a table(Office) which had a column (created by).I need a report based on the Created by column.(i.e) i need to show the number of offices created by specific person for a specific date.I write a pivot query to accomplish this task.Now, the problem is i am getting zero values in the result set .

declare @Date as datetime
set @Date = getdate()

select * from
(
select officeId,convert(VARCHAR(7), DataEntryDate, 120) as DataEntryDate,createdBy from Office
where Convert(varchar,dataentrydate,101)=Convert(varchar,@Date,101)

) as result
PIVOT

(
Count(officeId)
FOR createdBy
IN (creater1,creator2,creator3)
) as pivotTable

i am getting the following (ex) as output

creator1 - creator2 - creator3
10 - 13 - 0

The above mentioned query is dynamically created .The office table is the transaction table and my query is how to filter the Columns of pivot table based on condition(say Count(officeId) > 0 for a specific user for specific date).

pandiyan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 02:40:39

do the required filtering in the result query


select * from
(
    select officeId,convert(VARCHAR(7), DataEntryDate, 120) as DataEntryDate,createdBy from Office
    where Convert(varchar,dataentrydate,101)=Convert(varchar,@Date,101)
) as result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources