Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Min of field with groups 4

Status
Not open for further replies.
tonyvee1973

tonyvee1973

IS-IT--Management
Oct 22, 2009
156
GB
Hi Guys
Having a headache with this one and really could do with some guidance.
I have the script below:

SELECT dbo.STK_STOCK.STKCODE, '' AS Spare, dbo.STK_STOCK.STKNAME, '' AS UnitOfMeasure, '' AS UnitOfSales, dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP, '' AS PriceCode,
SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL,
dbo.POP_DETAIL.POD_QTYRESERVED AS QuantityDue, MIN(POP_DETAIL.POD_REQDATE) AS NextDelivery, '' AS ColourCode, '' AS ProductGroup,
(CASE dbo.STK_STOCK.STK_DO_NOT_USE when 1 then 'Y' ELSE 'N' END) as Discontinued,
(CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode, '' AS MinimumMultiples, '' AS GenericCode5,
'' AS GenericCode6, dbo.STK_STOCK.STK_SORT_KEY, dbo.STK_STOCK.STK_SORT_KEY1, '' AS FreeType3, '' AS FreeType4, '' AS FreeType5, '' AS FreeType6, '' AS FreeType7,
'' AS FreeType8, '' AS BreakCode, ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE, '' AS MinimumPrice,
'' AS RangeCode, '' AS MinimumPromotional, '' AS TaxCode, '' AS MinimumOrderQuantity, '' AS FutureStock
FROM dbo.STK_STOCK INNER JOIN
dbo.STK_STOCK_2 ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2 INNER JOIN
dbo.STK_STOCK3 ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3 INNER JOIN
dbo.STK_LOCATION ON STK_STOCK3.STKCODE3 = dbo.STK_LOCATION.LOC_STOCK_CODE INNER JOIN
dbo.POP_DETAIL ON STK_STOCK3.STKCODE3 = dbo.POP_DETAIL.POD_STOCK_CODE
WHERE (dbo.STK_STOCK3.STK_USRFLAG1 <> 1) AND (dbo.STK_LOCATION.LOC_CODE) NOT IN ('CREDIT','RETURN','REJECT') and (POP_DETAIL.POD_QTYRESERVED <>0) and (dbo.STK_STOCK.STKCODE = '11111')
GROUP BY dbo.STK_STOCK.STKCODE, dbo.STK_STOCK.STKNAME, dbo.STK_STOCK.STK_BASEPRICE, dbo.STK_STOCK3.STK_USRNUM1,dbo.POP_DETAIL.POD_QTYRESERVED, POP_DETAIL.POD_REQDATE, dbo.STK_STOCK.STK_DO_NOT_USE,
dbo.STK_STOCK3.STK_USRFLAG1,dbo.STK_STOCK.STK_BARCODE, dbo.STK_STOCK.STK_SORT_KEY, dbo.STK_STOCK.STK_SORT_KEY1, dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1




Originally, the script returned two lines for the stk code 11111 showing two different dates for the "POP_DETAIL.POD_REQDATE" field - i only wanted the line with the lowest date so added the MIN (Above in bold within the script)
I think that i have gone wrong somewhere as it seems to ignore the MIN and still returns two lines - I have tried several ways but its driving me nuts!
Any help really would be appreciated

Thanks as always
 
Sort by date Sort by votes
Hi, Yes there are two lines as there are two different dates in the POP_DETAIL.POD_REQDATE field as stated.
Trouble is that i only want to return th eline with the earliest date in
 
Upvote 0 Downvote
Just returned the error below, seems like it needs to be in the grouping

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.POP_DETAIL.POD_REQDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 
Upvote 0 Downvote
Try this:
Code: 
SELECT 
    dbo.STK_STOCK.STKCODE, 
    '' AS Spare, 
    dbo.STK_STOCK.STKNAME, 
    '' AS UnitOfMeasure, 
    '' AS UnitOfSales, 
    dbo.STK_STOCK.STK_BASEPRICE, 
    dbo.STK_STOCK3.STK_USRNUM1 AS RRP, 
    '' AS PriceCode, 
    SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL,
    dbo.POP_DETAIL.POD_QTYRESERVED AS QuantityDue, 
    MIN([red]dbo.[/red]POP_DETAIL.POD_REQDATE) AS NextDelivery, 
    '' AS ColourCode, 
    '' AS ProductGroup,
    (CASE dbo.STK_STOCK.STK_DO_NOT_USE when 1 then 'Y' ELSE 'N' END) as Discontinued,
    (CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead, 
    dbo.STK_STOCK.STK_BARCODE AS BarCode, 
    '' AS MinimumMultiples, 
    '' AS GenericCode5, 
    '' AS GenericCode6, 
    dbo.STK_STOCK.STK_SORT_KEY, 
    dbo.STK_STOCK.STK_SORT_KEY1, 
    '' AS FreeType3, 
    '' AS FreeType4, 
    '' AS FreeType5, 
    '' AS FreeType6, 
    '' AS FreeType7, 
    '' AS FreeType8, 
    '' AS BreakCode, 
    ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE, 
    '' AS MinimumPrice, 
    '' AS RangeCode, 
    '' AS MinimumPromotional, 
    '' AS TaxCode, 
    '' AS MinimumOrderQuantity, 
    '' AS FutureStock 
FROM dbo.STK_STOCK 
INNER JOIN dbo.STK_STOCK_2 ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2 
INNER JOIN dbo.STK_STOCK3 ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3 
INNER JOIN dbo.STK_LOCATION ON STK_STOCK3.STKCODE3 = dbo.STK_LOCATION.LOC_STOCK_CODE 
INNER JOIN dbo.POP_DETAIL ON STK_STOCK3.STKCODE3 = dbo.POP_DETAIL.POD_STOCK_CODE 
WHERE (dbo.STK_STOCK3.STK_USRFLAG1 <> 1) 
    AND (dbo.STK_LOCATION.LOC_CODE) NOT IN ('CREDIT','RETURN','REJECT') 
    and ([red]dbo.[/red]POP_DETAIL.POD_QTYRESERVED <>0) 
    and (dbo.STK_STOCK.STKCODE = '11111')
GROUP BY 
    dbo.STK_STOCK.STKCODE, 
    dbo.STK_STOCK.STKNAME, 
    dbo.STK_STOCK.STK_BASEPRICE, 
    dbo.STK_STOCK3.STK_USRNUM1,
    dbo.POP_DETAIL.POD_QTYRESERVED, 
    dbo.STK_STOCK.STK_DO_NOT_USE, 
    dbo.STK_STOCK3.STK_USRFLAG1,
    dbo.STK_STOCK.STK_BARCODE, 
    dbo.STK_STOCK.STK_SORT_KEY, 
    dbo.STK_STOCK.STK_SORT_KEY1, 
    [red]([/red]dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1[red])[/red]

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
Hi dj
seems to work a treat! Many thanks buddy.
Was it just simply as i missed some "dbo.'s" ? i can see anything else different from my original script (Except being set out much neater? :)
 
Upvote 0 Downvote
Just taken the line of script out below:
and (dbo.STK_STOCK.STKCODE = '11111')
so i could see all records and eventhough stock record 11111 is only returning one line now the rest still show 2 - AArrrrgghh! :)
 
Upvote 0 Downvote
Can you check something for me?

You say 2 rows... I assume you mean 2 rows for each STKCode. Can you check the data? I suspect that the POD_QTYRESERVED is different for each row. If I am correct, can you tell me which one you want to use, or perhaps you want the sum of POD_QTYRESERVED???

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hiya, yes indeed - 2 rows for each stock code, some have 3 or 4.
I just want the line with the MIN date in on the POP_DETAIL.POD_REQDATE field as above but it seems to be returning all lines with values in for each stock code
 
Upvote 0 Downvote
You are still grouping by , POP_DETAIL.POD_REQDATE so this is over riding your MIN()

Chnage group by to GROUP BY dbo.STK_STOCK.STKCODE, dbo.STK_STOCK.STKNAME, dbo.STK_STOCK.STK_BASEPRICE, dbo.STK_STOCK3.STK_USRNUM1,dbo.POP_DETAIL.POD_QTYRESERVED, dbo.STK_STOCK.STK_DO_NOT_USE,
dbo.STK_STOCK3.STK_USRFLAG1,dbo.STK_STOCK.STK_BARCODE, dbo.STK_STOCK.STK_SORT_KEY, dbo.STK_STOCK.STK_SORT_KEY1, dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1

Ian
 
Upvote 0 Downvote
hi, returned results below for one of the codes.
Its returning all results where i just need it to return the one line with the closest date (2013-01-17) from the results.
Appreciate the help


STKCODE STKNAME COST RRP STK_PHYSICAL QTY DUE NEXT DELIVERY DISCONTIN DEAD
12345 WINIFRED 2.25 4.99 6713 2976 2013-01-17 00:00:00.000 N N
12345 WINIFRED 2.25 4.99 6713 4800 2013-05-31 00:00:00.000 N N
12345 WINIFRED 2.25 4.99 6713 5400 2013-02-05 00:00:00.000 N N
12345 WINIFRED 2.25 4.99 6713 10800 2013-02-28 00:00:00.000 N N
 
Upvote 0 Downvote
You are getting multiple rows in your output because you are grouping on POD_QTYRESERVED, which has different values in the POP_DETAIL table. If you have SQL2005 or newer, try this...

Code: 
; With PopDetails As
(
  Select  POP_DETAIL.POD_STOCK_CODE,
          POP_DETAIL.POD_QTYRESERVED,
          POP_DETAIL.POD_REQDATE,
          Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE) As RowId
  From    POP_DETAIL
  Where   POP_DETAIL.POD_QTYRESERVED <> 0
)
SELECT	dbo.STK_STOCK.STKCODE, 
        '' AS Spare, 
        dbo.STK_STOCK.STKNAME, 
        '' AS UnitOfMeasure, 
        '' AS UnitOfSales, 
        dbo.STK_STOCK.STK_BASEPRICE, 
        dbo.STK_STOCK3.STK_USRNUM1 AS RRP, 
        '' AS PriceCode, 
        SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL,
        PopDetails.POD_QTYRESERVED AS QuantityDue, 
        PopDetails.POD_REQDATE AS NextDelivery, 
        '' AS ColourCode, 
        '' AS ProductGroup,
        (CASE dbo.STK_STOCK.STK_DO_NOT_USE when 1 then 'Y' ELSE 'N' END) as Discontinued,
        (CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead, 
        dbo.STK_STOCK.STK_BARCODE AS BarCode, 
        '' AS MinimumMultiples, 
        '' AS GenericCode5, 
        '' AS GenericCode6, 
        dbo.STK_STOCK.STK_SORT_KEY, 
        dbo.STK_STOCK.STK_SORT_KEY1, 
        '' AS FreeType3, 
        '' AS FreeType4, 
        '' AS FreeType5, 
        '' AS FreeType6, 
        '' AS FreeType7, 
        '' AS FreeType8, 
        '' AS BreakCode, 
        ROUND(dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1, 2) AS STK_COSTPRICE, 
        '' AS MinimumPrice, 
        '' AS RangeCode, 
        '' AS MinimumPromotional, 
        '' AS TaxCode, 
        '' AS MinimumOrderQuantity, 
        '' AS FutureStock 
FROM    dbo.STK_STOCK 
        INNER JOIN dbo.STK_STOCK_2 
          ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2 
        INNER JOIN dbo.STK_STOCK3 
          ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3 
        INNER JOIN dbo.STK_LOCATION 
          ON STK_STOCK3.STKCODE3 = dbo.STK_LOCATION.LOC_STOCK_CODE 
        INNER JOIN dbo.PopDetails
          ON STK_STOCK3.STKCODE3 = PopDetails.POD_STOCK_CODE
          And PopDetails.RowId = 1
WHERE	(dbo.STK_STOCK3.STK_USRFLAG1 <> 1) 
        AND (dbo.STK_LOCATION.LOC_CODE) NOT IN ('CREDIT','RETURN','REJECT') 
GROUP BY dbo.STK_STOCK.STKCODE, 
         dbo.STK_STOCK.STKNAME, 
         dbo.STK_STOCK.STK_BASEPRICE, 
         dbo.STK_STOCK3.STK_USRNUM1,
         PopDetails.POD_QTYRESERVED, 
         PopDetails.POD_REQDATE,
         dbo.STK_STOCK.STK_DO_NOT_USE, 
         dbo.STK_STOCK3.STK_USRFLAG1,
         dbo.STK_STOCK.STK_BARCODE, 
         dbo.STK_STOCK.STK_SORT_KEY, 
         dbo.STK_STOCK.STK_SORT_KEY1, 
         dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hi george, thanks for the script adjustment but it doesnt seem to work, i get the error below:

Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'POP_DETAIL' does not contain a top-level UNION ALL operator.

I had a look and you referenced "popdetails" where the field is "POP_DETAIL" so i changed them as thought this may be the issue but the error remained.
ANy ideas on what the error is referring to?
 
Upvote 0 Downvote
The common table expression (CTE) is the top part of the query.

Code: 
; With [!]PopDetails[/!] As
(
  Select  POP_DETAIL.POD_STOCK_CODE,
          POP_DETAIL.POD_QTYRESERVED,
          POP_DETAIL.POD_REQDATE,
          Row_Number() Over (Partition By POD_STOCK_CODE Order By POD_REQDATE) As RowId
  From    POP_DETAIL
  Where   POP_DETAIL.POD_QTYRESERVED <> 0
)

Notice the PopDetails at the top. This is how the lower query references the CTE. There's nothing recursive about this query, so the error message doesn't make sense unless there are other parts of this query that you're not showing.

If you run the query that is contained within the parenthesis, you should see that a row is returned for every row in the table where qty reserved <> 0. More importantly, you'll see a RowId column that is essentially a counter. The counter resets every time stock code changes, and is ordered by Req Date. So... wherever you see a 1 for RowId, that is the row you want to use because it has the oldest reqdate. The value of Wty Reserved that you see for RowId = 1 is also the value that will be used by the lower query.

Please understand that I am NOT saying the query is flawless. I tried to make it work, but it's possible that I made a mistake somewhere. It's incredibly difficult to write a query when you can't test it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hi george
I cant understand why its not working, the whole script is detailed above - nothing left out.
Will try to have another look this end - thanks for the assistance
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
236
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
642
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
398
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
174
grnzbra
grnzbra
Cpreston
  • Locked
  • Question
Seconds to minutes 2
Replies
5
Views
479
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top