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.
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)
Thanks Andomar....though it did not solved my problem but helped me a lot...thanks a lot
Another option is using dynamic pivot
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.
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
