Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    9

    Unanswered: SQL Query Help Please !

    Hi all

    I am using the following query for data to be displayed in crosstab :

    SELECT closedate,status,
    SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
    SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
    SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
    SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
    FROM dbcleaning
    WHERE status = 'Fixed'
    AND empcode IN
    ('NT-028',
    'NT-031',
    'NT-050',
    'NT-062')
    GROUP BY closedate, status

    Now i need a column in the same result of the query which shows the difference between the two columns .

    For Example :

    the result shoud be

    Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031

    Please note the last column in bold, i need the difference .

    Any help will be highly appreciated

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please remember to use [code] tags when posting SQL statements, it makes things a lot easier to read.

    The following should give you what you want:
    Code:
    SELECT closedate, 
           status, 
           NT028, 
           NT031,
           NT050,
           NT062,
           NT028-NT031 as difference 
    FROM (
      SELECT closedate,
             status,
             SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
             SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
             SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
             SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
      FROM dbcleaning
      WHERE status = 'Fixed'
            AND empcode IN ('NT-028','NT-031','NT-050','NT-062')
      GROUP BY closedate, status
    ) t
    Last edited by shammat; 07-06-10 at 06:02.

  3. #3
    Join Date
    Jul 2010
    Posts
    9
    Dear Shammat

    I really thank you for providing me the sql query. It helped me a lot

    Thanks You

  4. #4
    Join Date
    Jul 2010
    Posts
    9
    Dear Shammat

    A little more help from you !

    I want to get the total of each column in a row, except for closedate and status. Could you please help me.

    Thank You

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by shaik56 View Post
    I want to get the total of each column in a row, except for closedate and status. Could you please help me
    Sorry, I don't understand you.

    Please give some sample input data and the expected output from that.

  6. #6
    Join Date
    Jul 2010
    Posts
    9
    Dear Shammat

    The output of the existing query which you had posted like :


    Code:
    closedate status  NT028  NT031  NT050   NT062 NT028-NT031
    20-10-2009  F      10       10       10     10         0
    20-11-2009  F      5        10       10     10        5
                       15       20       20      20        5 
    Notice the last row, i need the total

    I hope i have explained properly

    Thanks

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    The best way to do this will be in your application code, add it up as you fetch rows. The SQL to do this would be ugly and underperforming.
    Dave

Posting Permissions

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