Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Group by 2 Tables

    I have 2 tables

    Table Container
    - ContainerID (PK)
    - ContainerStartYear
    - ContainerStartMonth

    Table Item
    - ITEMID (PK)
    - VendorPartNumber
    - ContainerID (FK)
    - ITEMStartYear
    - ITEMENDMonth
    - PRICE

    ----

    The idea is the you have many items in a container but you do not always have an item start date or container start date.

    My goal is to get the average price of a vendorpart in a month of the year.

    So now I group all ITEMS by VendorPartNumber, ITEMStartYear, ITEMStartMonth. Averaging the PRICE of this group... BUT Now the hard part is that if the ITEMStartYEAR or ITEMStartMonth is null then we must group the item via the Containers Month. BUT in the case where we are missing a date on the Item and its container, we will omit this set from the query.

    In concept it seems simple... but I can not see how this translates into a query.

    Any insight would be appreciated.
    Last edited by Eric the Red; 11-09-11 at 01:09.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    BUT Now the hard part is that if the ITEMStartYEAR or ITEMStartMonth is null then we must group the item via the Containers Month.
    Code:
    GROUP BY CASE WHEN ITEMStartYEAR IS NULL OR ITEMStartMonth IS NULL 
       THEN ContainerStartMonth
       ELSE ITEMStartMonth ?? / ITEMStartYEAR  ??
    END
    You must specify what to take when ITEMStartMonth IS NOT NULL OR ITEMStartYEAR IS NOT NULL

    BUT in the case where we are missing a date on the Item and its container, we will omit this set from the query.
    Code:
    WHERE NOT (ContainerStartYear IS NULL AND
       ContainerStartMonth IS NULL AND
       ITEMStartYear IS NULL AND
       ITEMENDMonth IS NULL)
    I presume that "a date on" means both StartYear and StartMonth are missing.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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