Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL Query and SSRS Help

    Hi

    I have the following table:


    tbl_tickets
    ticket_id - int primary key
    service_id - int foreign key
    date_created - datetime


    tbl_service
    service_id - int primary key
    service_name - varchar


    I use the following SQL to list the volume of tickets isssued over the last 5 days grouped by service and date:

    Code:
    select count(*) as ticket_issued
           ,date_created
           ,service_name
    From tbl_tickets t
     INNER JOIN tbl_service s ON t.service_id = s.service_id
    WHERE ((date_created = @Date) OR
          (date_created = DATEADD(d, -1, @Date)) OR
          (date_created = DATEADD(d, -2, @Date)) OR
          (date_created = DATEADD(d, -3, @Date)) OR
          (date_created = DATEADD(d, -4, @Date)) OR)
    GROUP BY service_name, date_created
    this would produce something like:

    Code:
    serivce_name | date_created | ticket_issued
    -------------------------------------------
    Refuse       |  2010-08-23  |   10
    Refuse       |  2010-08-24  |   11
    Refuse       |  2010-08-25  |   18
    Refuse       |  2010-08-26  |   15
    Refuse       |  2010-08-27  |   10
    Repairs      |  2010-08-23  |   20
    Repairs      |  2010-08-24  |   10
    Repairs      |  2010-08-25  |   12
    Repairs      |  2010-08-26  |   14
    Repairs      |  2010-08-27  |   20
    How can you devise the query so that you can add an addional column to give you the year to date total ticket_issued as follows:

    Code:
    serivce_name | date_created | ticket_issued | ytd_total_issued
    --------------------------------------------------------------
    Refuse       |  2010-08-23  |   10          |      500
    Refuse       |  2010-08-24  |   11          |      500
    Refuse       |  2010-08-25  |   18          |      500
    Refuse       |  2010-08-26  |   15          |      500
    Refuse       |  2010-08-27  |   10          |      500
    Repairs      |  2010-08-23  |   20          |      870      
    Repairs      |  2010-08-24  |   10          |      870
    Repairs      |  2010-08-25  |   12          |      870
    Repairs      |  2010-08-26  |   14          |      870
    Repairs      |  2010-08-27  |   20          |      870
    I want to display this in ssrs. Currently I use a matrix report to display it as follows:

    Code:
    --------------------------------------------------------------------------------------------
                             | 2010-08-23 | 2010-08-24 | 2010-08-25 | 2010-08-26 | 2010-08-27
    --------------------------------------------------------------------------------------------
    Refuse   | Ticket Issued |     10     |     11     |     18     |      15    |     10
    --------------------------------------------------------------------------------------------
    Repairs  | Ticket Issued |     20     |     10     |     12     |      14    |     20
    --------------------------------------------------------------------------------------------
    Total                    |     30     |     21     |     30     |      29    |     30
    But I want to add an extra column at the end to give the year to date total for each service as follows:

    Code:
    ----------------------------------------------------------------------------------------------------------
                             | 2010-08-23 | 2010-08-24 | 2010-08-25 | 2010-08-26 | 2010-08-27   | Year To Date
    ----------------------------------------------------------------------------------------------------------
    Refuse   | Ticket Issued |     10     |     11     |     18     |      15    |     10       |   500
    ----------------------------------------------------------------------------------------------------------
    Repairs  | Ticket Issued |     20     |     10     |     12     |      14    |     20       |   870
    ----------------------------------------------------------------------------------------------------------
    Total                    |     30     |     21     |     30     |      29    |     30       |   1370
    Is there any other you can do with SQL and SSRS?

    Any help guidance would be appreciated.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    212
    ok i think i may have figured this one if i use a query like this:


    Code:
    select  service_name as service_name
           ,date_created as date_created
           ,count(*) as ticket_issued
    From tbl_tickets t
     INNER JOIN tbl_service s ON t.service_id = s.service_id
    WHERE ((date_created = @Date) OR
          (date_created = DATEADD(d, -1, @Date)) OR
          (date_created = DATEADD(d, -2, @Date)) OR
          (date_created = DATEADD(d, -3, @Date)) OR
          (date_created = DATEADD(d, -4, @Date)) OR)
    GROUP BY service_name, date_created
    UNION
    select  service_name as service_name
           ,'YTD Total' as date_created
           ,count(*) as ticket_issued
    From tbl_tickets t
     INNER JOIN tbl_service s ON t.service_id = s.service_id
    WHERE (date_created between '2010-01-01' AND @Date)
    GROUP BY service_name, date_created
    Which brings me to second question. How can you write a query that say if the date selected is Monday then only details for Monday are retrieved if the date selected is Tuesday then only details for Monday and Tuesday are retrieved etc. Also instead of the YTD total I need to retrieve the weekly cumulative instead?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •