Help with query

  • I have tried to figure this out with no luck.

    Here is the deal.

    I have a table of possible week begin dates; tblPossibleDates; PossibleDate

    I have a table of Customer Orders; tblOrders.  This table has the fields WeekDate, CustomerID, and OrderQty.  Assume the tblOrders.WeekDate is the week begin date of when the order was placed.

    What I need is a query that will return a resultset that looks like the following.

    PossibleDate                    CustomerID           WeekDate       OrderQty

    2006-02-26                     1                         2006-02-26     100

    2006-03-05                     1                         2006-03-05     25 

    2006-03-12                     1                         Null                Null

    2006-03-19                     1                         2006-03-19     33 

    As you can tell my example resultset, the PossibleDate of 2006-03-12, there would not be a record in tblOrders.  But I still need to return a CustomerID so that I know CustomerID 1 does not have an order for the PossibleDate.

    If you have any ideas how to do this, I would appreciate it!

  • Try something like this using a derived table:

    select

        dt.PossibleDate,

        dt.CustomerId,

        or.WeekDate,

        or.OrderQty

    from

        (select

            pd.PossibleDate,

            o.CustomerId

         from

            dbo.tblPossibleDates pd

            cross join dbo.tblOrders o) dt

        left outer join dbo.tblOrders or

            on (dt.PossibleDate = or.Weekdate

                and dt.CustomerId = or.CustomerId)

  • Lynn, you rock!  Thank you so much!

  • Lee,

    Lynn is absolutely spot on... the only thing is that the necessary cross join will spawn a huge number of interim rows even if you add a DISTINCT to the outer query... make the small change to her query I've highlighted below and it'll continue to work great with much larger record sets...

    --===== Create some test data...

     CREATE TABLE #tblPossibleDates (PossibleDate DATETIME)

     INSERT INTO #tblPossibleDates (PossibleDate)

     SELECT '2006-02-26' UNION ALL

     SELECT '2006-03-05' UNION ALL

     SELECT '2006-03-12' UNION ALL

     SELECT '2006-03-19'

     CREATE TABLE #tblOrders (CustomerID INT, WeekDate DATETIME, OrderQty INT)

     INSERT INTO #tblOrders (CustomerID, WeekDate, OrderQty)

     SELECT 1,'2006-02-26',100 UNION ALL

     SELECT 1,'2006-03-05', 25 UNION ALL

     SELECT 1,'2006-03-19', 33 UNION ALL

     SELECT 2,'2006-02-26',100 UNION ALL

     SELECT 2,'2006-03-05', 25 UNION ALL

     SELECT 3,'2006-03-19', 33 UNION ALL

     SELECT 4,'2006-02-26',100 UNION ALL

     SELECT 4,'2006-03-12', 25 UNION ALL

     SELECT 4,'2006-03-19', 33

     SELECT dt.PossibleDate,

            dt.CustomerId,

            [or].WeekDate,

            [or].OrderQty

       FROM

            (

             SELECT pd.PossibleDate,

                    o.CustomerId

               FROM #tblPossibleDates pd

              CROSS JOIN

                    (SELECT DISTINCT CustomerId FROM #tblOrders) o

            ) dt

       LEFT OUTER JOIN

            #tblOrders [or]

         ON dt.PossibleDate = [or].Weekdate

        AND dt.CustomerId = [or].CustomerId

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thanks for your reply.  I noticed this too and put a distinct where you mentioned.  Thanks again for the replies folks!

  • Actually, not having put any test code together, I didn't think of making sure about only selecting disinct Customer Id's.  I just looked at what it would take to create the desired output based on the single customer id in the sample.  Good catch!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply