How to Count tickets as DateWise

I have a query regarding my report, the report format is as under

**Date**           **Received**        **Closed**        **Pending**  
12/01/10              1000                 900               100
12/02/10              2000                1000              1000
12/03/10              1500                1300               200

The above report shows the Help Desk tickets Received, Closed, Pending Count as date wise.

How can I create a dynamic SQL query to show above result?

• Received Tickets is calculated on SubmitedDate
• Closed Tickets is calculated on ClosedDate with status "Closed"
• same for Pending Tickets whose status is "Pending".

Please provide me the idea or some sample开发者_运维技巧 SQL queries.

Write the received, closed, and pending queries separately, and then join them together like this:

SELECT r.[Date], r.Count As Received, c.Count As Closed, p.Count AS Pending
FROM
   ( /* Received query here */ ) r
FULL JOIN 
   ( /* Closed query here */) c ON c.[Date] = r.[Date]
FULL JOIN
   ( /* Pending query here */) p ON p.[Date] = r.[Date]

I chose a full join because you wouldn't want a zero-result at any point to ever force a row to be culled from the results.

Use:

  SELECT CONVERT(VARCHAR, t.submitteddate, 101) AS [date].
         COUNT(t.submitteddate) AS received
         SUM(CASE WHEN t.status = 'closed' THEN 1 ELSE 0 END) AS closed,
         SUM(CASE WHEN t.status = 'pending' THEN 1 ELSE 0 END) AS pending
    FROM YOUR_TABLE t
GROUP BY CONVERT(VARCHAR, t.submitteddate, 101)
ORDER BY [date]

If you want to see dates where none were sold, you're going to have to derive a table of dates and then LEFT JOIN the query above to that based on the date.