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
Can you run this 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
)
Select *
From   PopDetails
Where  RowId = 1

Does it product the correct data for each STOCK_CODE?

-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 hanging on with this one :)
Yes, that seems to return what we need - what is the best way to implement this into the script?
 
Upvote 0 Downvote
There's another aggregate you are using....

SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL,

Does this value depend on the same stock code as the previous query. I mean... the PopDetails query is returning POP_DETAILS.POD_STOCK_CODE which is linked back to the query.

Can you write the simplest query you can that returns this SUM and the stock code that matches the value from dbo.STK_STOCK?

My thoughts are this... if we can write separate queries for the aggregates, then the entire query becomes simpler.

-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 really am not that hot on SQL, and getting a bit out of my depth here.
The aggregate of SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL, depends on the same stock code, yes.
not quite sure what i need to to do here, sorry.


 
Upvote 0 Downvote
Hi
I have pasted your script above on top of my original script, see below:

; 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
)
Select *
From PopDetails
Where RowId = 1

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(dbo.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 (dbo.POP_DETAIL.POD_QTYRESERVED <>0)
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


This all looks ok but is open in two windows (Yours at the top and the original query at the bottom)
All i need to do now is get the results from the top query in the POP_DETAIL.POD_QTYRESERVED and the POP_DETAIL.POD_REQDATE fields into the report on the bottom which relate to the corresponding stock code.
But i have no idea of the syntax to do so - Help! :)
 
Upvote 0 Downvote
Can you try running this query exactly as shown? Please run this directly in SQL Server Management Studio. Click "New Query", make sure the database is set correctly, paste the query, and then run it. I *think* it will produce the correct results, but cannot be sure. If it does produce the correct results, I will explain it to your satisfaction.

If you get an error message, please post it. It's entirely possible that I may have made a minor syntax error.

I honestly believe that a person should completely understand any code they put in to production.

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, 
        StockLocation.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 (
          Select  dbo.STK_LOCATION.LOC_STOCK_CODE,
                  SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
          From    dbo.STK_LOCATION
          Where	  dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT') 
          ) As StockLocation
          On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE
        INNER JOIN (
          Select  POD_STOCK_CODE,
                  POD_QTYRESERVED,
                  POD_REQDATE
          From    (
                  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   dbo.POP_DETAIL.POD_QTYRESERVED <> 0
                  ) As ALL_POP_DETAILS
          Where   RowId = 1
          ) As PopDetails
          On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
WHERE   dbo.STK_STOCK3.STK_USRFLAG1 <> 1

-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

Ok, tried the script and i got a message relating to aggregates so presumed this was because my grouping was not included. i Included the grouping at the bottom of the script and now it seems it needs the "dbo.STK_LOCATION.LOC_STOCK_CODE" in the grouping as it gives the error message below:

Msg 8120, Level 16, State 1, Line 1
Column 'dbo.STK_LOCATION.LOC_STOCK_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Your script with my grouping added 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,
StockLocation.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 (
Select dbo.STK_LOCATION.LOC_STOCK_CODE,
SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
From dbo.STK_LOCATION
Where dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')
) As StockLocation
On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
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 dbo.POP_DETAIL.POD_QTYRESERVED <> 0
) As ALL_POP_DETAILS
Where RowId = 1
) As PopDetails
On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
WHERE dbo.STK_STOCK3.STK_USRFLAG1 <> 1
GROUP BY dbo.STK_STOCK.STKCODE,
dbo.STK_STOCK.STKNAME,
dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1,
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
 
Upvote 0 Downvote
Like I said, it's hard to write queries when you can't test them. Sorry about that. I did miss a group by, but not where you expected.

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, 
        StockLocation.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 (
          Select  dbo.STK_LOCATION.LOC_STOCK_CODE,
                  SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
          From    dbo.STK_LOCATION
          Where	  dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT') 
          [!]Group By dbo.STK_LOCATION.LOC_STOCK_CODE[/!]
          ) As StockLocation
          On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE
        INNER JOIN (
          Select  POD_STOCK_CODE,
                  POD_QTYRESERVED,
                  POD_REQDATE
          From    (
                  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   dbo.POP_DETAIL.POD_QTYRESERVED <> 0
                  ) As ALL_POP_DETAILS
          Where   RowId = 1
          ) As PopDetails
          On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
WHERE   dbo.STK_STOCK3.STK_USRFLAG1 <> 1

Let me explain what I'm doing here so that it makes sense. I apologize if I explain things you already know.

When you use an aggregate, like SUM, all of the columns in the SELECT clause must be an aggregate OR part of the GROUP BY clause. (I suspect you know this already).

The way I created the query shown above, you do not need to have a monster group by on the outer query because all of the aggregates are contained within derived tables. To help explain, let's take just a small part of the query.

Code: 
SELECT  dbo.STK_STOCK.STKCODE, 
        dbo.STK_STOCK.STKNAME, 
        dbo.STK_STOCK.STK_BASEPRICE, 
        [green]StockLocation[/green].STK_PHYSICAL
FROM    dbo.STK_STOCK 
        INNER JOIN [blue]([/blue]
          [red]Select  dbo.STK_LOCATION.LOC_STOCK_CODE,
                  SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
          From    dbo.STK_LOCATION
          Where	  dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT') 
          Group By dbo.STK_LOCATION.LOC_STOCK_CODE[/red]
          [blue])[/blue] [green]As StockLocation[/green]
          On dbo.STK_STOCK.STKCODE = [green]StockLocation[/green].LOC_STOCK_CODE

The part in red is clearly just a query. Nothing special about it. When you run this query, you will get the stock code and the SUM column (STK_PHYSICAL). Since we are only returning 2 columns and 1 of them is an aggregate, we only need to group by 1 column.

Imagine this... If you had a separate table in your database that stored stock code and STK_PHYSICAL, and there was only 1 row per stock code. This query would be really simple to write. Since you don't have such a table, we can create a derived table instead. To use a derived table, you need to enclose the smaller query in parenthesis (shown in blue above).

When you use a derived table, you must give it an alias. That's the [green] As StockLocation[/green] part immediately following the closing parenthesis. At this point, the derived table will appear as an actual table to the outer query. As such, you need to refer to it consistently throughout the outer query. Notice that the ON clause refers to the table alias, and so does the column in the SELECT clause.

Of course, your actual query is more complex than this example. Your query required multiple derived tables, but the concept still holds. It's really just more of the same.

Does this make sense?

-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 explaining - this makes sence.
I have rerun the query but still getting an error:
Msg 8120, Level 16, State 1, Line 1
Column 'StockLocation.STK_PHYSICAL' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
 
Upvote 0 Downvote
I'm at a loss. I don't see why you would be getting that error unless there is more to the query that you are not showing. The outer query doesn't have any aggregates so there's no need for a group by on the outer query. I'm confused.

-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
I know, doesnt make sence, is it referring to th ehighlighted below where its looking for that in a group where the rest are in the group at the bottom of the query?




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,
[highlight #FCE94F]StockLocation.STK_PHYSICAL[/highlight],
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 (
Select dbo.STK_LOCATION.LOC_STOCK_CODE,
SUM(dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL
From dbo.STK_LOCATION
Where dbo.STK_LOCATION.LOC_CODE NOT IN ('CREDIT','RETURN','REJECT')
Group By dbo.STK_LOCATION.LOC_STOCK_CODE
) As StockLocation
On dbo.STK_STOCK.STKCODE = StockLocation.LOC_STOCK_CODE
INNER JOIN (
Select POD_STOCK_CODE,
POD_QTYRESERVED,
POD_REQDATE
From (
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 dbo.POP_DETAIL.POD_QTYRESERVED <> 0
) As ALL_POP_DETAILS
Where RowId = 1
) As PopDetails
On dbo.STK_STOCK.STKCODE = PopDetails.POD_STOCK_CODE
WHERE dbo.STK_STOCK3.STK_USRFLAG1 <> 1
GROUP BY dbo.STK_STOCK.STKCODE,
dbo.STK_STOCK.STKNAME,
dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1,
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
 
Upvote 0 Downvote
Try removing the entire group by at the bottom and then run the query.

-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
By Jove i think thats it! Will do some testing but it looks ok iniially.
Like you said earlier i suppose, the way its structured the outer query doesnt need a group now.
Really appreciate your help with this George - will give you a yell when i've finished testing. You have a great weekend!
 
Upvote 0 Downvote
I'm glad it's finally working (or at least appears to be). I hope you have a good weekend too.

-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
Finally got a proper look at this query.
Seems fine but one problem - only seems to be returning correct data in the "QuantityDue" and "NextDelivery" fields if the STK_PHYSICAL field is empty.
If there is a value in the STK_PHYSICAL field then the "QuantityDue" and "NextDelivery" values are 0.

I've had a look at the query but cant see where this could be limiting?
 
Upvote 0 Downvote
I'm not sure....

Try changing the last 2 joins to Left joins and re-run.

-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
Any chance you could take another look please? i have tried all sorts and no joy.
Thanks in advance
 
Upvote 0 Downvote
Tony,

I am really sorry, but I see nothing wrong with the query. I see no reason why the returned data is what you want. Unfortunately, I think this is a situation where I would really need to see the data to figure out the problem.

Since there are so many tables involved, scripting some sample data may be a bit tedious, and not necessarily helpful. If you could, though, that would be great.


-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
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
643
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