locked
Group By Min Max RRS feed

  • Question

  • I've got a query and it goes through sales data by stores.  a store can report on a certain date and also on another date such that per store I need to bring back the maximum and minumum date that there were sales for that store and store that info in each of the store's rows.

    Select
     [Sale Report Date] as [Sale Date],
     [Store No],
     [UPC],
    SUM([Quantity Sold]) as [Quantity Sold],
      [Friendly Desc],
    [Vendor Item Number],
    MaxRptDate = convert(varchar(10), MAX([Sale Report Date]), 112),
    MinRptDate = convert(varchar(10), MIN([Sale Report Date]), 112)
    from @temp_NewsTranSaleRcpts
    WHERE [UPC] IS NOT NULL and [Vendor Number] = 943 
    GROUP BY
    [Friendly Desc],
    [Sale Report Date],
     [Store No],
     [UPC],
    [Vendor Item Number]
    ORDER BY [Store No]

    This brings back the Sale Report Date for each record instead of the MinRptDate and MaxRptDate for that stores record(s).

    Sale Date Store No   UPC                    Quantity Sold   Friendly Desc  Vendor Item Number      MaxRptDate MinRptDate
    20111119 3066         00795008003008 1                   20lb. Tank P   5013                             20111119   20111119
    20111119 3142         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111120 3144         00795008003008 1                   20lb. Tank P   5013                             20111120   20111120
    20111119 3159         00795008001004 1                   20lb.  Exchan   5015                             20111119   20111119
    20111120 3181         00795008001004 1                   20lb  Swap     1197                             20111120   20111120
    20111119 3184         00795008001004 3                   20lb. Exchan   5015                             20111119   20111119
    20111120 3184         00795008001004 3                   20lb. Exchan   5015                             20111120   20111120
    20111119 3185         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111119 3191         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111119 3193         00795008001004 2                   20lb. Exchan   5015                             20111119   20111119
    20111119 3195         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111120 3195         00795008001004 1                   20lb. Exchan   5015                             20111120   20111120

    I need it to look like this * Note stores 3184 and 3195

    Sale Date Store No   UPC                    Quantity Sold   Friendly Desc  Vendor Item Number      MaxRptDate MinRptDate
    20111119 3066         00795008003008 1                   20lb. Tank P   5013                             20111119   20111119
    20111119 3142         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111120 3144         00795008003008 1                   20lb. Tank P   5013                             20111120   20111120
    20111119 3159         00795008001004 1                   20lb.  Exchan   5015                             20111119   20111119
    20111120 3181         00795008001004 1                   20lb  Swap     1197                             20111120   20111120
    20111119 3184         00795008001004 3                   20lb. Exchan   5015                             20111119   20111120  *
    20111120 3184         00795008001004 3                   20lb. Exchan   5015                             20111119   20111120  *
    20111119 3185         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111119 3191         00795008001004 1                   20lb. Exchan   5015                             20111119   20111119
    20111119 3193         00795008001004 2                   20lb. Exchan   5015                             20111119   20111119
    20111119 3195         00795008001004 1                   20lb. Exchan   5015                             20111119   20111120  *
    20111120 3195         00795008001004 1                   20lb. Exchan   5015                             20111119   20111120  *

    I'm thinking the grouping to get the SUM is throwing off the Store grouping.  Any way around this?

    TIA

    Harry

    Tuesday, November 22, 2011 5:00 PM

Answers

  • Sorry, hard to concentrate on ten things at once. Ok, here is correct working code:

    Select 
     R.[Sale Report Date] as [Sale Date],
     R.[Store No],
     R.[UPC],
    SUM(R.[Quantity Sold]) as [Quantity Sold],
      R.[Friendly Desc],
    R.[Vendor Item Number],
    MaxRptDate = convert(varchar(10),M.MaxSaleReportDate, 112),
    MinRptDate = convert(varchar(10),M.MinSaleReportDate, 112)
    from @temp_NewsTranSaleRcpts R 
    INNER JOIN (select max([Sale Report Date]) as MaxSaleReportDate,
    MIN([Sale Report Date]) as MinSaleReportDate, [Store No]
    FROM @temp_NewsTranSaleRcpts R 
    WHERE R.[UPC] IS NOT NULL and R.[Vendor Number] = 943 
    GROUP BY R.[Store No]
    ) M
    ON R.[Store No] = M.[Store No]
     
    WHERE R.[UPC] IS NOT NULL and R.[Vendor Number] = 943  
    GROUP BY 
    R.[Friendly Desc],
    R.[Sale Report Date],
     R.[Store No],
     R.[UPC],
    R.[Vendor Item Number],
    M.MaxSaleReportDate, M.MinSaleReportDate
    ORDER BY R.[Store No]
    
    Don't know how FROM went missing.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Mimosa Arts Tuesday, November 22, 2011 6:24 PM
    Tuesday, November 22, 2011 6:18 PM

All replies

  • Hi, try please :

    Select 
    [Sale Report Date] as [Sale Date],
    [Store No],
    [UPC],
    SUM([Quantity Sold]) as [Quantity Sold],
    [Friendly Desc],
    [Vendor Item Number],
    MaxRptDate = convert(varchar(10), MAX([Sale Report Date]), 112),
    MinRptDate = convert(varchar(10), MIN([Sale Report Date]), 112),
    Case When convert(varchar(10), MAX([Sale Report Date]), 112)<>
    convert(varchar(10), MIN([Sale Report Date]), 112)
    Then '*' Else '' End as note
    from @temp_NewsTranSaleRcpts
    WHERE [UPC] IS NOT NULL and [Vendor Number] = 943 
    GROUP BY 
    [Friendly Desc],
    [Sale Report Date],
    [Store No],
    [UPC],
    [Vendor Item Number]
    ORDER BY [Store No]
    

     

     

    Best regards
    Tuesday, November 22, 2011 5:08 PM
  • Try the below one!

     

    Select	[Sale Report Date] as [Sale Date],
    		[Store No],
    		[UPC],
    		SUM([Quantity Sold]) as [Quantity Sold],
    		[Friendly Desc],
    		[Vendor Item Number],
    		MAX([Sale Report Date]) OVER(PARTITION BY [Store No]) AS MaxRptDate,
    		MIN([Sale Report Date]) OVER(PARTITION BY [Store No]) AS MinRptDate
    FROM	@temp_NewsTranSaleRcpts
    WHERE	[UPC] IS NOT NULL 
    AND		[Vendor Number] = 943  
    GROUP BY	[Friendly Desc], [Sale Report Date], [Store No], [UPC], [Vendor Item Number]
    ORDER BY [Store No]
    


     


    Tuesday, November 22, 2011 5:19 PM
  • Same result
    Tuesday, November 22, 2011 5:37 PM
  • Incorrect syntax near the keyword 'OVER'.
    Tuesday, November 22, 2011 5:38 PM
  •  

    Yes Naomi, shouldve mentioned that before....

    I'm getting a "Incorrect syntax near ',' .

    pointing to the

    @temp_NewsTranSaleRcpts R

    line which of course is not the line but don't see the syntax problem right off....

    Tuesday, November 22, 2011 5:57 PM
  • I saw the typo in my original code (missing closing ) in the MAX function in the derived table. I corrected it in the original reply.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, November 22, 2011 6:13 PM
  • Thanks Naomi

     

    Still getting a

    Line 90: Incorrect syntax near '@temp_NewsTranSaleRcpts'.

    Should there be a FROM in there?

    Tuesday, November 22, 2011 6:16 PM
  • Sorry, hard to concentrate on ten things at once. Ok, here is correct working code:

    Select 
     R.[Sale Report Date] as [Sale Date],
     R.[Store No],
     R.[UPC],
    SUM(R.[Quantity Sold]) as [Quantity Sold],
      R.[Friendly Desc],
    R.[Vendor Item Number],
    MaxRptDate = convert(varchar(10),M.MaxSaleReportDate, 112),
    MinRptDate = convert(varchar(10),M.MinSaleReportDate, 112)
    from @temp_NewsTranSaleRcpts R 
    INNER JOIN (select max([Sale Report Date]) as MaxSaleReportDate,
    MIN([Sale Report Date]) as MinSaleReportDate, [Store No]
    FROM @temp_NewsTranSaleRcpts R 
    WHERE R.[UPC] IS NOT NULL and R.[Vendor Number] = 943 
    GROUP BY R.[Store No]
    ) M
    ON R.[Store No] = M.[Store No]
     
    WHERE R.[UPC] IS NOT NULL and R.[Vendor Number] = 943  
    GROUP BY 
    R.[Friendly Desc],
    R.[Sale Report Date],
     R.[Store No],
     R.[UPC],
    R.[Vendor Item Number],
    M.MaxSaleReportDate, M.MinSaleReportDate
    ORDER BY R.[Store No]
    
    Don't know how FROM went missing.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Mimosa Arts Tuesday, November 22, 2011 6:24 PM
    Tuesday, November 22, 2011 6:18 PM
  • Thanks Naomi!!  This is it!  I really appreciate your help and time!!

     

    Tuesday, November 22, 2011 6:48 PM