Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Problem with Self Join

    I am attempting to do a simple self join but I am drawing a blank on completing it. Please assist with the following issue:

    I have one table where I need to create a query that combines aggregated columns that use two different where clauses.

    Here is where I am at:

    Code:
    SELECT a.Total_WO, a.primaryCraftsman, b.Total_Calls
    FROM [HMSQL].[dbo].[WorkOrder] a
    a(SELECT  COUNT([workOrderPhase]) as Total_WO, [primaryCraftsman], [primaryCraftsmanId]
    FROM [HMSQL].[dbo].[WorkOrder]
    WHERE (Year([CreateDate])=2010) and ([primaryCraftsmanId] <> '') and ([primaryCraftsman] <> '')
     and ([workOrderPhase] = 'Completed')
    GROUP BY [primaryCraftsman], [primaryCraftsmanId]
    ORDER BY [primaryCraftsman] ASC),
    b(SELECT SUM(CAST([callsTakenToday] as INT)) as Total_Calls, [primaryCraftsman], [primaryCraftsmanId]
    FROM [HMSQL].[dbo].[WorkOrder]
    WHERE (Year([CreateDate])=2010) and ([primaryCraftsmanId] <> '') and ([primaryCraftsman] <> '')
    GROUP BY [primaryCraftsman], [primaryCraftsmanId]
    ORDER BY [primaryCraftsman] ASC)
    I know I have to join it, but this is where I am losing it

    Please help...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Haven't tested it, but this is what I think you want:
    Code:
    SELECT a.Total_WO, a.primaryCraftsman, b.Total_Calls
    FROM (SELECT  COUNT([workOrderPhase]) as Total_WO, [primaryCraftsman], [primaryCraftsmanId]
    FROM [HMSQL].[dbo].[WorkOrder]
    WHERE (Year([CreateDate])=2010) and ([primaryCraftsmanId] <> '') and ([primaryCraftsman] <> '')
     and ([workOrderPhase] = 'Completed')
    GROUP BY [primaryCraftsman], [primaryCraftsmanId]) a
    inner join (SELECT SUM(CAST([callsTakenToday] as INT)) as Total_Calls, [primaryCraftsman], [primaryCraftsmanId]
    FROM [HMSQL].[dbo].[WorkOrder]
    WHERE (Year([CreateDate])=2010) and ([primaryCraftsmanId] <> '') and ([primaryCraftsman] <> '')
    GROUP BY [primaryCraftsman], [primaryCraftsmanId]) b
    on a.primaryCraftsmanId = b.primaryCraftsmanId
    ORDER BY a.primaryCraftsman ASC
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Conti2011, can this get you what you want without doing any join? I am not at a place where I can test this but I think it will do the same thing you want.
    Code:
    SELECT SUM(CASE WORKORDERPHASE
                      WHEN 'COMPLETED' THEN CAST(CALLSTAKENTODAY AS INT)
                                       ELSE 0
               ) AS TOTAL_W
         , PRIMARYCRAFTSMAN
         , COUNT(*) AS TOTAL_CALLS
    FROM HMSQL.DBO.WORKORDER
    WHERE YEAR(CREATEDATE) = 2010
      AND PRIMARYCRAFTSMANID <> ''
      AND PRIMARYCRAFTSMAN   <> ''
    GROUP BY PRIMARYCRAFTSMAN
    ORDER BY PRIMARYCRAFTSMAN

  4. #4
    Join Date
    Jan 2011
    Posts
    2
    Thank you very much!

    I knew that I went to the right people.....I was missing the INNER JOIN section...which was stupid of me. It has been a little while since I wrote any SQL.

    Again thanks!

Posting Permissions

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