SQL Server 2008 – Display Query Result Without Null Values in One Column

sql-server-2008

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.

Best Answer

with adding 

Having (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)

Now try the 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)
Having  (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End) > 0)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)
Related Question