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)
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.