question

MayBorn5th avatar image
MayBorn5th asked

CrossTab Query In SQL 2008

Hi Experts, I have a calling details table which contains AgentId, CallDate, RespType etc. Now I want a crosstab query to get AgentId as row and CallDate as column header. Also want to show total number of calls for last 7 days. Let me clear further: lets say if i select CallDate=07-Nov-2010, then result should look like this: AgentId 07-Nov 06-Nov 05-Nov 04-Nov 03-Nov 02-Nov 01-Nov 1111 24 10 32 15 16 52 65 2222 20 25 50 57 61 25 51 Can any body help me with a sql query for the same.
sql-server-2008t-sqlpivotcross-tab
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andomar avatar image
Andomar answered
You can use a sum() and a case to pivot calls per agent per day: select AgentId , sum(case when CallDate > '2010-11-09' then 1 end) as Today , sum(case when CallDate between '2010-11-09' and '2010-11-08' then 1 end) as Yesterday from YourTable group by AgentId Instead of hard coding the date, you can retrieve this morning's timestamp like: select dateadd(day, datediff(day, 0, getdate()), 0) And for yesterday: select dateadd(day, datediff(day, 1, getdate()), 0)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Andomar....though it did not solved my problem but helped me a lot...thanks a lot
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
Another option is using dynamic pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
What is the use for this data? If you are passing the data to something like reporting services then it is far easier(and more effective) to handle a cross-tab in a report using a matrix object in the report designer.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

actually i want to display this in a aspx ( asp.net) page as a report. i don know is there any report option is available in asp.net or not.
0 Likes 0 ·
for sure, you can use the reporting objects in .Net. Simply build a report in the layout that you want and then drop a report object into your .Net application and set it to be the rdl file on your report server. You should take a look here http://msdn.microsoft.com/en-us/library/aa964126(SQL.90).aspx
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.