Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Displaying results in an aggregate query without grouping everything?

    I had a hard time titling this thread, sorry. I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?

    SAMPLE:

    CustKey SwatchDate RugDate DateDiff
    109039 6312 6678 366
    109039 6312 6837 525
    109039 6312 6960 648
    109139 6228 6550 322
    111588 6193 6257 64
    112966 6456 6467 11
    114698 6229 6253 24
    114698 6229 6276 47
    114698 6229 6667 438
    114698 6656 6667 11
    114698 6624 6667 43



    DESIRED RESULTS:

    CustKey SwatchDate RugDate DateDiff
    109039 6312 6678 366
    111588 6193 6257 64
    112966 6456 6467 11
    114698 6656 6667 11

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT your_table.custkey
         , your_table.swatchdate
         , your_table****gdate
         , your_table.datediff
    FROM   your_table
     INNER
      JOIN (
            SELECT custkey
                 , Min(rugdate) As min_rugdate
            FROM   your_table
            GROUP
                BY custkey
           ) As x
        ON x.custkey = your_table.custkey
       AND x.min_rugdate= your_table****gdate
    The ****'s need to be replaced by . r u (minus spaces)
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    Code:
    SELECT your_table.custkey
         , your_table.swatchdate
         , your_table****gdate
         , your_table.datediff
    FROM   your_table
     INNER
      JOIN (
            SELECT custkey
                 , Min(rugdate) As min_rugdate
            FROM   your_table
            GROUP
                BY custkey
           ) As x
        ON x.custkey = your_table.custkey
       AND x.min_rugdate= your_table****gdate
    The ****'s need to be replaced by . r u (minus spaces)
    I think this is a little off because i am looking for the swatchdate and rugdate that is associated with the MIN(datediff). This gives me a good start though. i'll try adjusting the code a little...

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this (based on George's code)
    Code:
    SELECT custkey
    	, swatchdate
    	, rugdate
    	, datediff
    FROM (SELECT custkey
    	, swatchdate
    	, rugdate
    	, datediff
    	, ROW_NUMBER() OVER (PARTITION BY custkey ORDER BY datediff ASC) as RowNum
    	FROM   your_table) AS T
    WHERE RowNum = 1
    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

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    i just talked to the boss and he is wanting to change things. The original plan was to knock it down to one result per customer, but now they want it on an order level, meaning I have to associate swatch orders to each rug order if possible, using the smallest date diff..what a pain.

    So using the sample data above, customer 114698 would get 2 entries in the result set:

    CustKey SwatchDate RugDate DateDiff
    114698 6229 6253 24
    114698 6656 6667 11

    Because the 2nd swatch was ordered after the 1st rug and there is another rug after the 2nd swatch that it can be associated with... I think i may just quit...

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ok so my thinking is to first run a query to narrow it down by selecting the min(datediff) grouping by rugdate, then on that result set, selecting min(datediff) grouping by swatchdate. Problem is that the query I am using for that first part it taking forever:

    Code:
    Select vRugSwatchPurchaseCompare2010.custkey,
           vRugSwatchPurchaseCompare2010.swatchdate,
           vRugSwatchPurchaseCompare2010****gdate,
           vRugSwatchPurchaseCompare2010.[datediff]
    From analysttempdb.dbo.vRugSwatchPurchaseCompare2010
      INNER JOIN
        (Select custkey, min([datediff]) as min_datediff, rugdate From analysttempdb.dbo.vRugSwatchPurchaseCompare2010
        Group By custkey,rugdate) as x
      On x.custkey=vRugSwatchPurchaseCompare2010.custkey
      And x.min_datediff = vRugSwatchPurchaseCompare2010.[datediff]
       And x****gdate = vRugSwatchPurchaseCompare2010****gdate
    The ****'s need to be replaced by . r u (minus spaces)

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The simplest solution I can think of is to write one query for rugdate and one for swatchdate and then UNION ALL them together.
    George
    Home | Blog

  8. #8
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by gvee View Post
    The simplest solution I can think of is to write one query for rugdate and one for swatchdate and then UNION ALL them together.
    im not sure i follow. I understand unions, just not sure what individual queries I would run.

Posting Permissions

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