Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Stored Procedures?

    My goal is to send 2 dates (11/1/02 & 11/15/02) to some type of function / procedure / etc. and have calculations done before sending the data to the browser.

    Here are the fields in my current queries
    EmpID - unique ID
    Name - obviously the name
    Date - Date of Call or Problem
    Type - Call or Problem

    A sample might look like this....
    10, John Doe, 11/1/02, Call
    10, John Doe, 11/2/02, Call
    10, John Doe, 11/2/02, Problem
    32, Tom Jones, 11/5/02, Call
    32, Tom Jones, 11/13/02, Call
    44, Elvis Presley, 11/3/02, Call
    44, Elvis Presley, 11/8/02, Problem
    44, Elvis Presley, 11/9/02, Problem

    I would like to do a count of all Calls, Count of all Problems, and a percentage of problems per calls.

    ie -
    10, John Doe, 2, Call
    10, John Doe, 1, Problem
    32, Tom Jones, 2, Call
    44, Elvis Presley, 1, Call
    44, Elvis Presley, 2, Problem

    From there I would like to produce 1 recordset with the EmpID, Name, Calls, Problems, Percentage.

    ie -
    10, John Doe, 2, 1, 50%
    32, Tom Jones, 2, 0, 0%
    44, Elvis Presley, 1, 2, 200%

    Does that make sense? Is there something available for that?

    Thank you for your help.

    SQL Server 2000
    ASP - Server Side Scripting

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Stored Procedures?

    If SQL Server supports the CASE construct you can:

    SELECT empid, name, calls, problems,
    CASE WHEN calls = 0 THEN NULL ELSE problems/calls*100 END percentage
    FROM
    (
    SELECT empid, name,
    SUM( CASE WHEN type = 'call' THEN 1 ELSE 0 END ) calls,
    SUM( CASE WHEN type = 'problem' THEN 1 ELSE 0 END ) problems
    FROM table
    WHERE ...
    GROUP BY empid, name
    );

    The above works in Oracle, but as far as I know doesn't use anything proprietory to oracle.

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    Thank you.

    I am getting this error:

    Server: Msg 170, Level 15, State 1, Line 12
    Line 12: Incorrect syntax near ')'.

    Here's the exact code I'm trying to use.
    Code:
    USE Boomerang
    GO
    
    SELECT RepID, Name, Calls, Tickets,
    CASE WHEN Calls = 0 THEN NULL ELSE Tickets/Calls*100 END
    FROM
    (
    SELECT RepID, Name,
    	SUM(CASE Type WHEN 'CMAHRS' THEN intRecords ELSE 0 END) AS Calls,
    	SUM(CASE Type WHEN 'RRS' THEN intRecords ELSE 0 END) AS Tickets
    FROM vw_CallsRRSRatio
    WHERE [Date] BETWEEN '11/1/02' AND '11/5/02'
    GROUP BY RepID, [Name]
    )
    Thanks for any help.

  4. #4
    Radzi Guest

    Re: Stored Procedures?

    You need to have a name to the outermost subquery....

    select ..... from ( select ... ) A



    "artsapimp" wrote in message
    news:2429529.1043352738@dbforums.com...
      > I am getting this error:
      > Server: Msg 170, Level 15, State 1, Line 12
      > Line 12: Incorrect syntax near ')'.
      > Here's the exact code I'm trying to use.
      > USE Boomerang
      > GO
      > SELECT RepID, Name, Calls, Tickets,
      > CASE WHEN Calls = 0 THEN NULL ELSE Tickets/Calls*100 END
      > FROM
      > (
      > SELECT RepID, Name,
      > SUM(CASE Type WHEN 'CMAHRS' THEN intRecords ELSE 0 END) AS Calls,
      > SUM(CASE Type WHEN 'RRS' THEN intRecords ELSE 0 END) AS Tickets
      > FROM vw_CallsRRSRatio
      > WHERE [Date] BETWEEN '11/1/02' AND '11/5/02'
      > GROUP BY RepID, [Name]
      > )
      > Thanks for any help.
      > --
      > Posted via http://dbforums.com

  5. #5
    Join Date
    Jan 2003
    Posts
    3

    Thank you.

    That was the fix, thank you.

    How would I now format that column to allow 2 decimal places? It keeps giving me either a 0 or NULL for what I wrote, and I'm sure that's because it is rounding it to the nearest whole number (right?).

    Thanks for your help.

Posting Permissions

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