Advertisement
Guest User

Untitled

a guest
Apr 24th, 2014
46
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.95 KB | None | 0 0
  1. TicketNo TicketDate StateID
  2. 1 01/01/2013 100
  3. 2 05/01/2013 100
  4. 3 09/01/2013 101
  5. 4 15/01/2013 101
  6. 5 17/01/2013 102
  7. 6 01/02/2013 100
  8. 7 05/02/2013 100
  9. 8 08/02/2013 102
  10. 9 22/02/2013 103
  11. 10 28/02/2013 103
  12. ...
  13.  
  14. StateID FirstMonthCount SecondMonthCount Third...
  15. 100 2 2
  16. 101 2 0
  17. 102 1 1
  18. 103 0 2
  19. ...
  20.  
  21. SELECT StateID, Count(StateID) AS Count, From Ticket
  22. WHERE TicketDate >= '01/01/2013' AND TicketDate <= '31/01/2013'
  23. Group By StateID Order By StateID
  24.  
  25. SELECT
  26. StateID, [1] as FirstMonthCount, [2] as SecondMonthCount, ...
  27. FROM
  28. (
  29. SELECT DATEPART(m, TicketDate) TheMonth, StateID
  30. FROM tbl
  31. WHERE TicketDate >= '01/01/2013'
  32. AND TicketDate <= '31/12/2013'
  33. ) as src
  34. PIVOT
  35. (
  36. COUNT(TheMonth)
  37. FOR TheMonth IN ([1], [2], ..., [12])
  38. ) as pvt
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement