Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: checking 2 sql columns and displaying result in 1 column

    ok..i have 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up). So if this is possible..let me know please.. i will also paste the exact code i have if you feel like seeing the real life code..i just tried to simplify it to make it easier to understand.
    i'm going to post some images to hopefully explain it a little better

    image 1 is what is currently set up
    imgur: the simple image sharer
    top part is what is stored in tables..bottom is more of the result
    it basically runs this code to get the bottom


    DECLARE @id INT;
    DECLARE @invest nvarchar(50);
    SET @id = '7633';
    SET @invest = '';

    SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
    FROM orderheader, orderaudits, orderfindings
    WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
    AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
    GROUP BY orderfindings.risk_rating

    If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it
    image 2- the table at the bottom is more what i'm looking for..it combines them both into 1
    imgur: the simple image sharer
    if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..any help is appreciated ..i'll clarify what i can if you dont understand

    Edit:
    i've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...didnt really work..any advice?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Union?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2012
    Posts
    7
    i actually have tried union..the sql statement i put in..copied it..changed it to agencies..and stuck a union all inbetween..it it doesnt combine them though.. it gives me.. a1 = 5, a2 =2, a2 =3 a2 = 3...which is still wrong...cuz in reality..a1 should be 5, and a2 4...i dont want to add them..or do them seperately..i need the statement to check and see if either of them are true..if yes..add 1..elso..nothing..and go through all the orders..does that help?

  4. #4
    Join Date
    Jan 2012
    Posts
    7
    been working more with it..my big problem now is tallying up the scores..i can only get one column ..anyone know of a trick to get the right numbers out of this
    --------------------new sql statement--------------
    select 'a' + convert(nvarchar, risk_rating) as cat,
    count(distinct orderfindings.prnt_id) as stat

    from orderheader, orderaudits, orderfindings, orderagencies
    where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderfindings.prnt_id )
    and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
    group by risk_rating
    having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Show me five sample rows from your table, and the results that you'd like to see from those five sample rows.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jan 2012
    Posts
    7
    best way i can explain is is by a picture
    imgur: the simple image sharer

  7. #7
    Join Date
    Jan 2012
    Posts
    7
    also..someone else on another forum asked for code to recreate tables..i think this will work :/

    [SQL] structure - Pastebin.com

  8. #8
    Join Date
    Jan 2012
    Posts
    7
    small edit..mistyped something

    --------------------new sql statement--------------
    select 'a' + convert(nvarchar, risk_rating) as cat,
    count(distinct orderfindings.prnt_id) as stat

    from orderheader, orderaudits, orderfindings, orderagencies
    where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderagencies.prnt_id )
    and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
    group by risk_rating
    having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0

Posting Permissions

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