0

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.

1 Answer 1

0

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)
3
  • Getting Invalid Column Name in Outstanding
    – Arvin
    Commented Sep 4, 2014 at 9:18
  • Got it substituted the whole Outstanding with the actual command.
    – Arvin
    Commented Sep 4, 2014 at 9:21
  • @Arvin you can use (COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End)) instead of Outstanding in having clause
    – vijayp
    Commented Sep 4, 2014 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.