Results 1 to 4 of 4

Thread: Join

  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unanswered: Join

    Hi, I have written the following query, but for some reason, I have incorrect output.
    select Label.am_value1,
    Label.am_label1,
    ISNULL(Detail.am_value1, 0)
    from(
    select '1' as am_value1, 'Successful' as am_label1 union all
    select '2' as am_value1, 'Successful' as am_label1 union all
    select '3' as am_value1, 'Successful' as am_label1 union all
    select '4' as am_value1, 'Successful' as am_label1 union all
    select '5' as am_value1, 'Successful' as am_label1 union all
    select '6' as am_value1, 'Successful' as am_label1 union all
    select '7' as am_value1, 'Successful' as am_label1 union all
    select '8' as am_value1, 'Successful' as am_label1 union all
    select '9' as am_value1, 'Successful' as am_label1 union all
    select '10' as am_value1, 'Successful' as am_label1 union all
    select '11' as am_value1, 'Successful' as am_label1 union all
    select '12' as am_value1, 'Successful' as am_label1 union all

    select '1' as am_value1, 'Failed' as am_label1 union all
    select '2' as am_value1, 'Failed' as am_label1 union all
    select '3' as am_value1, 'Failed' as am_label1 union all
    select '4' as am_value1, 'Failed' as am_label1 union all
    select '5' as am_value1, 'Failed' as am_label1 union all
    select '6' as am_value1, 'Failed' as am_label1 union all
    select '7' as am_value1, 'Failed' as am_label1 union all
    select '8' as am_value1, 'Failed' as am_label1 union all
    select '9' as am_value1, 'Failed' as am_label1 union all
    select '10' as am_value1, 'Failed' as am_label1 union all
    select '11' as am_value1, 'Failed' as am_label1 union all
    select '12' as am_value1, 'Failed' as am_label1 union all

    select '1' as am_value1, 'Canceled' as am_label1 union all
    select '2' as am_value1, 'Canceled' as am_label1 union all
    select '3' as am_value1, 'Canceled' as am_label1 union all
    select '4' as am_value1, 'Canceled' as am_label1 union all
    select '5' as am_value1, 'Canceled' as am_label1 union all
    select '6' as am_value1, 'Canceled' as am_label1 union all
    select '7' as am_value1, 'Canceled' as am_label1 union all
    select '8' as am_value1, 'Canceled' as am_label1 union all
    select '9' as am_value1, 'Canceled' as am_label1 union all
    select '10' as am_value1, 'Canceled' as am_label1 union all
    select '11' as am_value1, 'Canceled' as am_label1 union all
    select '12' as am_value1, 'Canceled' as am_label1 union all

    select '1' as am_value1, 'SWP' as am_label1 union all
    select '2' as am_value1, 'SWP' as am_label1 union all
    select '3' as am_value1, 'SWP' as am_label1 union all
    select '4' as am_value1, 'SWP' as am_label1 union all
    select '5' as am_value1, 'SWP' as am_label1 union all
    select '6' as am_value1, 'SWP' as am_label1 union all
    select '7' as am_value1, 'SWP' as am_label1 union all
    select '8' as am_value1, 'SWP' as am_label1 union all
    select '9' as am_value1, 'SWP' as am_label1 union all
    select '10' as am_value1, 'SWP' as am_label1 union all
    select '11' as am_value1, 'SWP' as am_label1 union all
    select '12' as am_value1, 'SWP' as am_label1 union all

    select '1' as am_value1, 'Unsuccessful' as am_label1 union all
    select '2' as am_value1, 'Unsuccessful' as am_label1 union all
    select '3' as am_value1, 'Unsuccessful' as am_label1 union all
    select '4' as am_value1, 'Unsuccessful' as am_label1 union all
    select '5' as am_value1, 'Unsuccessful' as am_label1 union all
    select '6' as am_value1, 'Unsuccessful' as am_label1 union all
    select '7' as am_value1, 'Unsuccessful' as am_label1 union all
    select '8' as am_value1, 'Unsuccessful' as am_label1 union all
    select '9' as am_value1, 'Unsuccessful' as am_label1 union all
    select '10' as am_value1, 'Unsuccessful' as am_label1 union all
    select '11' as am_value1, 'Unsuccessful' as am_label1 union all
    select '12' as am_value1, 'Unsuccessful' as am_label1 ) AS Label
    LEFT OUTER JOIN
    (select
    (CASE when Status_Reason = 1000 then 'Successful'
    when Status_Reason = 2000 then 'Failed'
    when Status_Reason = 3000 then 'Canceled'
    when Status_Reason = 10000 then 'SWP'
    when Status_Reason = 11000 then 'Unsuccessful'
    else 'No Closure Stat'
    end) as am_label1,

    //CALCULATES PERCENTAGE OF EACH STATUS_REASON FOR ONE MONTH
    ((cast(sum((case when Status_Reason = 1000 then 1
    when Status_Reason = 2000 then 1
    when Status_Reason = 3000 then 1
    when Status_Reason = 11000 then 1
    when Status_Reason = 10000 then 1 else 0 end)) as float)*100)/(cast(sum(count(*)) as float))) as am_value1,

    month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM')) as myMonth

    from Task
    YEAR(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))=YEAR(getdate()) AND
    Product_Cat_Tier_1 IN ('Network') AND
    Product_Cat_Tier_2 IN ('RFC') AND
    Status = 6000 AND
    Product_Cat_Tier_3 NOT IN ('Voice AND Video', 'Installation')
    group by Status_Reason, month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))
    ) as Detail
    ON ((cast(Label.am_value1 as int) = Detail.myMonth) and (Detail.am_label1 = Label.am_label1) )
    -----------------------
    THE OUTPUT OF THAT QUERY:
    am_value1 am_label1
    --------- --------- --------------------
    1 Successful 100.0
    2 Successful 100.0
    3 Successful 100.0
    4 Successful 100.0
    5 Successful 100.0
    6 Successful 100.0
    7 Successful 100.0
    8 Successful 0.0
    9 Successful 0.0
    10 Successful 0.0
    11 Successful 100.0
    12 Successful 0.0
    1 Failed 100.0
    2 Failed 100.0
    3 Failed 100.0
    4 Failed 100.0
    5 Failed 100.0
    6 Failed 100.0
    7 Failed 100.0
    8 Failed 0.0
    9 Failed 0.0
    10 Failed 0.0
    11 Failed 0.0
    12 Failed 0.0
    1 Canceled 100.0
    2 Canceled 100.0
    3 Canceled 100.0
    4 Canceled 100.0
    5 Canceled 100.0
    6 Canceled 100.0
    7 Canceled 100.0
    8 Canceled 0.0
    9 Canceled 0.0
    10 Canceled 0.0
    11 Canceled 0.0
    12 Canceled 0.0
    1 SWP 0.0
    2 SWP 0.0
    3 SWP 0.0
    4 SWP 0.0
    5 SWP 0.0
    6 SWP 100.0
    7 SWP 100.0
    8 SWP 0.0
    9 SWP 0.0
    10 SWP 0.0
    11 SWP 0.0
    12 SWP 0.0
    1 Unsuccessful 0.0
    2 Unsuccessful 0.0
    3 Unsuccessful 0.0
    4 Unsuccessful 0.0
    5 Unsuccessful 100.0
    6 Unsuccessful 100.0
    7 Unsuccessful 100.0
    8 Unsuccessful 0.0
    9 Unsuccessful 0.0
    10 Unsuccessful 0.0
    11 Unsuccessful 0.0
    12 Unsuccessful 0.0

    ------------------------------------
    WHILE THE OUTPU OF THE SECOND PART OF THE QUERY, "Detail" part
    (select
    (CASE when Status_Reason = 1000 then 'Successful'
    when Status_Reason = 2000 then 'Failed'
    when Status_Reason = 3000 then 'Canceled'
    when Status_Reason = 10000 then 'SWP'
    when Status_Reason = 11000 then 'Unsuccessful'
    else 'No Closure Stat'
    end) as am_label1,

    //CALCULATES PERCENTAGE OF EACH STATUS_REASON FOR ONE MONTH
    ((cast(sum((case when Status_Reason = 1000 then 1
    when Status_Reason = 2000 then 1
    when Status_Reason = 3000 then 1
    when Status_Reason = 11000 then 1
    when Status_Reason = 10000 then 1 else 0 end)) as float)*100)/(cast(sum(count(*)) as float))) as am_value1,

    month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM')) as myMonth

    from Task
    YEAR(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))=YEAR(getdate()) AND
    Product_Cat_Tier_1 IN ('Network') AND
    Product_Cat_Tier_2 IN ('RFC') AND
    Status = 6000 AND
    Product_Cat_Tier_3 NOT IN ('Voice AND Video', 'Installation')
    group by Status_Reason, month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))
    ) as Detail

    returns the following results:

    am_label1 am_value1 myMonth
    --------- -------------------- -----------
    No Closure Stat 0.0 5
    Successful 13.318681318681319 1
    Successful 11.296703296703297 2
    Successful 11.956043956043956 3
    Successful 13.450549450549451 4
    Successful 12.307692307692308 5
    Successful 12.43956043956044 6
    Successful 6.197802197802198 7
    Successful .02197802197802198 11
    Failed .5714285714285714 1
    Failed .17582417582417584 2
    Failed .37362637362637363 3
    Failed .8571428571428571 4
    Failed .4175824175824176 5
    Failed .48351648351648352 6
    Failed .065934065934065936 7
    Canceled 2.9450549450549453 1
    Canceled 2.3736263736263736 2
    Canceled 2.6373626373626373 3
    Canceled 2.5494505494505493 4
    Canceled 2.197802197802198 5
    Canceled 1.7582417582417582 6
    Canceled .65934065934065933 7
    SWP .39560439560439559 6
    SWP .26373626373626374 7
    Unsuccessful .02197802197802198 5
    Unsuccessful .15384615384615385 6
    Unsuccessful .087912087912087919 7

    Thank you. I appreciate your help.

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I don't think it's possible for a query to return incorrect results.

    Perhaps unexpected results, but not incorrect ones.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jul 2008
    Posts
    2
    Quote Originally Posted by TallCowboy0614
    I don't think it's possible for a query to return incorrect results.

    Perhaps unexpected results, but not incorrect ones.

    I agree with you, but I can't understand what is the problem... You can see the result of the smaller query "Detail" part and what it returns, but for some reason when I do the LEFT JOIN, the result becomes meaningless...

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    It might be easier to understand what you are trying to do if you also show some sample data and the expected result using the sample data.
    Quote Originally Posted by mukhan85
    from Task
    YEAR(DATEADD...
    The word "where" is not in the code you posted so you are probably not telling us everything.

    Quote Originally Posted by mukhan85
    sum(count(*))
    Your grouping is inconsistent
    Although Sybase allow this non ANSI extension to quickly join your aggregate value with the rest of your data it can lead to mistakes.
    Insteaf of
    Code:
    select type,count(*),sum(count(*)) from syscolumns where id=1 group by type
    do this
    Code:
    select type,count(*),min(t)
    from syscolumns 
    ,(select count(*) t from syscolumns where id=1)a
    where id=1
    group by type
    Note: If you use functions on a columns in the where clause then a index on that column can't be used.
    Instead of
    Quote Originally Posted by mukhan85
    YEAR(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))=YEAR(getdate())
    Do this
    Code:
    where End_Date>=datediff(ss,'1969-12-31 8:00:00 PM' ,dateadd(yy, datediff(yy,'19700101',getdate())   ,'19700101'))
       and End_Date <datediff(ss,'1969-12-31 8:00:00 PM' ,dateadd(yy, datediff(yy,'19700101',getdate())+1 ,'19700101'))

Posting Permissions

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