Query Help

[dwg23]

Hello,
I am writing a Query that I almost have complete but am having a problem with.
When I run the below query everything runs fine and I get the results that I am looking for.
but when I add ,
Sum(tblarInvoiceLine.Quantity) As 'Total Quan Used'
to the query I get duplicate lines rather than a total.

I am out of ideas on how to do this.

Thanks,
dwg23

Select Distinct
Max(tblwhPickTicket.DatePrinted) As 'Last Date',
tblimItemLocBin.Item,
tblimItemLocBin.Location,
tblimItemLocBin.Bin,
tblimItemLoc.ForecastDemandQty As 'FMD',
tblimItemLoc.Class As 'Item Class',
tblimItemLoc.QuantityOnHand As 'On Hand',
Count(tblarInvoice.InvoiceNumber) As 'Total Lines'

From
tblarInvoiceLine Inner Join
tblimItemLocBin On tblarInvoiceLine.Item = tblimItemLocBin.Item Inner Join
tblimItemLoc On tblimItemLocBin.Item = tblimItemLoc.Item And
tblimItemLocBin.Location = tblimItemLoc.Location Inner Join
tblarInvoice On tblarInvoice.InvoiceNumber = tblarInvoiceLine.InvoiceNumber
Inner Join
tblwhPickTicket On tblwhPickTicket.SONumber = tblarInvoice.SONumber Inner Join
tblimItemAttribute On tblimItemLocBin.Item = tblimItemAttribute.Item
Where
tblimItemLocBin.Location = 'sea' And
tblimItemAttribute.Attribute = 'SSP' And
tblimItemLocBin.Bin <> 'stock'
Group By
tblimItemLocBin.Item, tblimItemLocBin.Location, tblimItemLocBin.Bin,
tblimItemLoc.ForecastDemandQty, tblimItemLoc.Class,
tblimItemLoc.QuantityOnHand, tblarInvoice.InvoiceNumber,
tblarInvoiceLine.Quantity

 

[dwg23]

Wanted to add that instead of getting a total of the lines I am getting duplicate lines like below.

Last Date Item Location Bin FMD Item Class On Hand Total Lines Total Quan Used
11/17/2009 9:49 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 2 2
4/20/2010 9:57 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 1 2
6/24/2009 9:51 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 1 10

I am trying to get a result set that shows the most recent date, a single Item number, Location, bin, FMD, Item Class, On Hand, Total Lines and Total Quan Used.

So the result set should look like this.

Last Date Item Location Bin FMD Item Class On Hand Total Lines Total Quan Used
04/20/2010 027-4F4F-BT SEA D04-E-08 0 CItem 3 4 14

 

[SQLBill]

None of those rows are duplicates - DISTINCT won't eliminate them since they are all different. You might need a subquery that groups on these (Item Location Bin FMD Item Class) and finds the max date for those. Then uses that to pull the information you really want.

I would suggest you provide us some sample data, along with what you want the results to be and we should be able to help you better.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[dwg23]

SQLBill,
I guess I don't understand what you mean by sample data, In my second post is that not what you are asking for?
Or do you need something else?

I really appreciate you looking at this.

Thanks,
DWG23

 

[jbenson001]

He means provide the data before your query, the raw data
and then
provide the data in the way you expect it to look after your query

 

[dwg23]

Last Date Item Location Bin FMD Class On Hand Total Lines Total Quan Used
11/17/2009 9:49 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 2 2
4/20/2010 9:57 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 1 2
6/24/2009 9:51 AM 027-4F4F-BT SEA D04-E-08 0 CITEM 1 1 10

I am trying to get a result set that shows the most recent date, a single Item number, Location, bin, FMD, Item Class, On Hand, Total Lines and Total Quan Used.

So the result set should look like this.

Last Date Item Location Bin FMD Class On Hand Total Lines Total Quan Used
04/20/2010 027-4F4F-BT SEA D04-E-08 0 CItem 3 4 14

Thanks,
dwg23

 

[TamarGranor]

SELECT LastDate, Item, Location, Bin, FMD, Class, OnHand, TotalLines, TotalQuanUsed 
  FROM YourTable 
  WHERE LastDate = (SELECT MAX(LastDate) FROM YourTable YT2)

This assumes that all dates are unique.

Tamar

 

[dwg23]

I would like to thank everyone for their help.
With the hints provided by you all I finally figured it out. Below is my final solution.

Thanks again!

Select
il.Item,
il.ForecastDemandQty As FMD,
il.Location,
ilb.Bin,
ilb.QtyOnHand,
Sum(ail.Quantity) As 'Total QTY Used',
Count(ai.InvoiceNumber) As 'Total Lines',
il.Class,
(Select
Max(tblwhPickTicketLine.DateMod)
From
tblwhPickTicketLine Inner Join
tblimItemLoc On tblwhPickTicketLine.Item = tblimItemLoc.Item
Where
ilb.Item In (tblimItemLoc.Item)) As 'Last Date Sold',
il.InvManageGroup
From
tblimItemLoc As il Inner Join
tblimItemLocBin As ilb On il.Item = ilb.Item And il.Location = ilb.Location
Inner Join
tblarInvoiceLine As ail On il.Item = ail.Item Inner Join
tblarInvoice As ai On ail.InvoiceNumber = ai.InvoiceNumber Inner Join
tblimItemAttribute As ia On il.Item = ia.Item Inner Join
tblwhPickTicketLine On tblwhPickTicketLine.SOLineNumber = ail.SOLineNumber And
il.Item = tblwhPickTicketLine.Item
Where
il.Location = 'SEA' And
ia.Attribute = 'SSP' And
ilb.Bin <> 'STOCK'
Group By
il.Item, il.ForecastDemandQty, il.Location, ilb.Bin, ilb.QtyOnHand, il.Class,
ilb.Item, il.InvManageGroup