Thread: SQL Query Help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

    SQL Query Help


    I am new to SQL and having some trouble. I am trying to find the total number of tickets open for each day.

    The query I am using will return something like this:

    Ticket# DateOpened DateClosed #ofDaysOpen
    ----------- -------------------- -------------------- -----------
    528 09/19/08 09/25/08 6
    529 09/24/08 09/30/08 6
    530 09/24/08 09/25/08 1
    673 09/27/08 09/30/08 3
    910 09/29/08 11/05/08 37
    911 09/29/08 10/08/08 9
    925 09/29/08 10/22/08 23
    927 09/29/08 09/30/08 1
    1014 09/30/08 11/06/08 37
    1015 09/30/08 10/10/08 10
    1037 09/30/08 10/08/08 8

    What I am trying to get is the number of tickets that are still open for each day.

    something like

    Date #ofTicketsOpen
    02/22/2013 3

    the Query that I used to get the above data is:

    Select
    SR_Service_RecID as 'Ticket#'
    ,CONVERT(VARCHAR(20), date_entered, 1) as 'DateOpened'
    ,CONVERT(VARCHAR(20), Date_Closed, 1) as 'DateClosed'
    ,DATEDIFF(DAY,Date_Entered,Date_Closed) as '#ofDaysOpen'
    from v_cbi_tracked_tickets
    where board_name IN ('Help Desk')
    and sr_status NOT IN ('Completed','On-Hold','Duplicate','Child Ticket')
    Order by Ticket#
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    Try this:
    Code:
    select convert( varchar( 10 ), date_entered, 1 ) as 'DateOpened', count(*) as '#ofTicketsOpen',
     from  v_cbi_tracked_tickets
    where  board_name in( 'Help Desk' )
     and   sr_status  =  'Open'
    group by convert( varchar( 10 ), date_entered, 1 )
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    Thank You for your quick reply, however, that will only return the total number of tickets that was opened on that day. What I am trying to do is count the total number of tickets that are still open. So if 5 were opened on Monday and I only closed 3 of them it would leave 2 still open. Then on Tuesday 5 more were opened and I did not close any the total number of tickets left open should be 7. I hope that makes sense.

IMN logo majestic logo threadwatch logo seochat tools logo