I created a query which produced four column result, I wanted to display result in which the Outstanding column > 0. My query below:
SELECT
CAST(xx_master_tickets.CreatedMoment AS DATE) TicketDate,
Outstanding = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End),
Closed = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 2 Then 1 End),
Cancelled = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 3 Then 1 End)
FROM XX_MASTER_TICKETS INNER JOIN
XX_DEF_ATM_LIST ON XX_MASTER_TICKETS.MachineID = XX_DEF_Machine_LIST.MachineID INNER JOIN
XX_DEF_BRANCHES ON XX_DEF_Machine_LIST.BranchID = XX_DEF_BRANCHES.BranchID
WHERE (XX_MASTER_TICKETS.CreatedMoment BETWEEN '01-01-2014' AND GETDATE()) and (XX_DEF_BRANCHES.BranchName = 'New York')
Group by CAST(xx_master_tickets.CreatedMoment AS DATE)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)
Result below:
TicketDate Outstanding Closed Cancelled
8/24/2014 0 65 1
8/25/2014 1 83 3
8/26/2014 0 77 3
8/27/2014 1 70 0
8/28/2014 4 88 1
8/29/2014 1 35 3
8/30/2014 2 65 2
8/31/2014 7 92 4
9/1/2014 0 77 2
9/2/2014 0 79 1
9/3/2014 39 44 2
9/4/2014 18 17 0
Result i wanted is below:
TicketDate Outstanding Closed Cancelled
8/25/2014 1 83 3
8/27/2014 1 70 0
8/28/2014 4 88 1
8/29/2014 1 35 3
8/30/2014 2 65 2
8/31/2014 7 92 4
9/3/2014 39 44 2
9/4/2014 18 17 0
Basically tracking dates with only outstanding values.