Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- TicketNo TicketDate StateID
- 1 01/01/2013 100
- 2 05/01/2013 100
- 3 09/01/2013 101
- 4 15/01/2013 101
- 5 17/01/2013 102
- 6 01/02/2013 100
- 7 05/02/2013 100
- 8 08/02/2013 102
- 9 22/02/2013 103
- 10 28/02/2013 103
- ...
- StateID FirstMonthCount SecondMonthCount Third...
- 100 2 2
- 101 2 0
- 102 1 1
- 103 0 2
- ...
- SELECT StateID, Count(StateID) AS Count, From Ticket
- WHERE TicketDate >= '01/01/2013' AND TicketDate <= '31/01/2013'
- Group By StateID Order By StateID
- SELECT
- StateID, [1] as FirstMonthCount, [2] as SecondMonthCount, ...
- FROM
- (
- SELECT DATEPART(m, TicketDate) TheMonth, StateID
- FROM tbl
- WHERE TicketDate >= '01/01/2013'
- AND TicketDate <= '31/12/2013'
- ) as src
- PIVOT
- (
- COUNT(TheMonth)
- FOR TheMonth IN ([1], [2], ..., [12])
- ) as pvt
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement