#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2007
    Posts
    11
    Rep Power
    0

    TSQL hell - brain teaser


    Ok I have tsql brain teaser

    I have 2 tables that I need to do a count on, and those table I need to join and then divide the counts

    Keep in mind the counts will return multiple rows

    Data might look like this
    UserLogin Case corr avg
    test1 15 48 30
    test2 54 68 1.2

    So something like this you'd think might work:

    Select Agent_Login, COUNT(Agent_Login)as cases, userlogin, COUNT(userLogin) as Correspondences, cast(userLogin as float)/ Agent_Login as 'corr_Avg'
    from CaseDetails_AllReports
    inner join Correspondences on Agent_Login=userlogin
    group by Agent_Login, userlogin

    But because the group appears to be executed first it multiples the numbers of the count - this sucks

    So I was able to come up with this query to join the tables with out screwing the data, but there is nowhere I can seem to divide the count values

    SELECT
    ISNULL(ca2.Agent_Login, cr2.userLogin) as userlogin,
    ISNULL(ca2.cases, 0) as cases,
    ISNULL(cr2.Correspondence, 0) as Correspondence

    FROM
    (
    SELECT ca.Agent_Login, COUNT(Agent_Login)as cases
    FROM CaseDetails_AllReports ca
    GROUP BY Agent_Login
    ) ca2
    FULL OUTER JOIN
    (
    SELECT cr.userLogin, COUNT(userLogin) as Correspondence

    FROM Correspondences cr
    GROUP BY cr.userLogin
    ) cr2 ON ca2.Agent_Login = cr2.userLogin


    any gurus ?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by 2revup
    any gurus ?
    any reason why you think you have to join those tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo