Results 1 to 6 of 6

Thread: Sql help

  1. #1
    Join Date
    Dec 2014
    Posts
    4

    Unanswered: Sql help

    I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error coverting data type varchar to numeric".
    Any help is appreciated.



    Code:
    select DEPARTMENT,
           CASE 
             WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN  (cast(sum(Amount) as decimal(10,2)))
                 ELSE '' END AS 'JUNE',
           CASE
             WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN  (cast(sum(Amount) as decimal(10,2)))
                 ELSE '' END AS 'JULY'
      from L27_PHAROS_DETAIL_DATA 
      WHERE Department = 'TECHNOLOGY SERVICES' 
      GROUP BY DEPARTMENT, DATE
      ORDER BY  DEPARTMENT

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I hope the column isn't really called date... that's a reserved word!

    Also, when writing dates ALWAYS use the format YYYY-MM-DD. This way there can be no confusion as to what it means e.g. UK vs US formats.

    In your case statement you are mixing data types a fair bit... decimal in one part and a string value in the else condition?
    Code:
    SELECT department
         , Sum(CASE WHEN "date" >= '2014-06-01' AND "date" < '2014-07-01' THEN amount ELSE 0 END) As june
         , Sum(CASE WHEN "date" >= '2014-07-01' AND "date" < '2014-08-01' THEN amount ELSE 0 END) As july
    FROM   L27_PHAROS_DETAIL_DATA 
    WHERE  department = 'TECHNOLOGY SERVICES' 
    GROUP
        BY department
    ORDER
        BY department
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2014
    Posts
    4

    More sql help please

    Thanks for your help Gvee....I understand what you did in your code. I have one other question please.

    I have the same similar code:

    Code:
    select distinct Department, Name, [Transaction Type], sum(quantity) as Quantity, [Quantity Name], (cast(sum(Amount) as decimal(10,2))) as Amount
     from L27_PHAROS_DETAIL_DATA
     WHERE DATE1 >= '2014-06-01' AND DATE1 < '2014-07-01'
     AND Department = 'Technology Services'
     group by Department, Name,  [Transaction Type],[Quantity Name]
    order by Department, Name, [Transaction Type]
    --------------------
    The output from the above code looks like:

    Department Name Transaction Type Quantity Quantity Name Amount
    TECHNOLOGY SERVICES User1 Print 709 Pages 32.76
    TECHNOLOGY SERVICES User1 Copy 22 Prints 0.88
    TECHNOLOGY SERVICES User1 Scan 260 Pages 0
    TECHNOLOGY SERVICES User2 Print 515 Pages 33.48
    TECHNOLOGY SERVICES User2 Copy 62 Prints 4.32
    TECHNOLOGY SERVICES User2 Scan 46 Pages 0

    Is there a way using sql or plsql to get the results to look something like:

    TECHNOLOGY SERVICES
    User1 Print 709 Pages 32.76
    User1 Copy 22 Prints 0.88
    User1 Scan 260 Pages 0

    User2 Print 515 Pages 33.48
    user2 Copy 62 Prints 4.32
    User2 Scan 46 Pages 0

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Another clean-up

    Please follow basic Netiquette and post the DDL we need to answer this. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. Avoid dialect in favor of ANSI/ISO Standard SQL. We need to know the data types, keys and constraints on the table.

    DATE is a data type in SQL, and you can use BETWEEN with it. We do not use CAST to get display formats in SQL – that was COBOL. We never start a data element name with a digit – that is ISO Standards and common sense, and we do not add “data” to a table (what is always in a table?)

    I think this is what you meant:

    SELECT dept_name, foobar_date,
    SUM(CASE
    WHEN foobar_date BETWEEN '2014-06-01 AND '2014-06-30'
    THEN foobar_amt) ELSE 0.00 END AS june_foobar_tot,
    SUM(CASE
    WHEN foobar_date BETWEEN '2014-07-01 AND '2014-07-30'
    THEN foobar_amt) ELSE 0.00 END AS july_foobar_tot
    FROM Pharos_Details_127
    WHERE dept_name = 'technology services'
    GROUP BY dept_name, foobar_date;

    Today, we do not use ORDER BY so much in serious apps. The results get passed to a presentation layer server and it does any sorting. The database server is kept as clean and fast as possible.

    The totals are now numeric, not COBOL strings. If you wish, you can make a zero into a NULL, but that seems wrong on the face of it.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQL is for data storage/retrieval, not for presentation.

    You'll want to format your data like that in your report/form/webpage/etc, not in SQL.
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2014
    Posts
    4

    sql help

    Ok, thank you.

Posting Permissions

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