September 5, 2015 at 5:19 am
Hi,
I am trying to create a manifest for one of our clients and want to show the total qty for a product.
Below is my query, the quantity is listed below as TL.Quantity but I think I need to use a SUM function to show the total by product?
Please help!
Thanks
Select
TH.TransactionDate,
TH.Reference,
TransactionAccountNumber.AccountNumber as [Client Ref],
Product.Code,
Product.Description,
TL.Quantity as Qty,
TL.LineLevel,
DENSE_RANK() over(order by TL.TransactionPackageLineID ) AS BoxNumber,
tL.TransactionPackageLineId,
TH.TotalNumberOfPackages,
tpl.WeightOverride,
PackageTotals.TotalActualWeight As ActualWeight,
Barcode.Code as Barcode,
JLCode.Code as JLCode,
ard1.Answer as ShipRef,
Slot.Answer As Slot,
Slottime.Answer as [Slot Time],
Slotref.Answer as [Slot Reference],
SlotVen.Answer as [Slot Vendor],
SlotDept.Answer as [Slot Department]
from TransactionLine as TL
inner join product on TL.productid = product.id
left join ProductAlias as Barcode on Barcode.ProductId = Product.Id AND Barcode.CategoryIdForProductAliasType =452
left join ProductAlias as JLCOde on JLCOde.ProductId = Product.Id AND JLCode.CategoryIdForProductAliasType = 12615
left join TransactionHeader as TH on TH.Id = TL.TransactionHeaderId
left join TransactionAccountNumber on TransactionAccountNumber.TransactionHeaderId = th.Id
left JOIN TransactionPackageLine AS tpl ON TL.TransactionPackageLineId = tpl.Id
left join RelatedDataTransactionHeaderResponse as ard1 on th.Id = ard1.TransactionHeaderId and ard1.RelatedDataElementId = 235
left join RelatedDataTransactionHeaderResponse as Slot on Slot.TransactionHeaderId = th.Id and slot.RelatedDataElementId = 440
left join RelatedDataTransactionHeaderResponse as Slottime on Slottime.TransactionHeaderId = th.Id and slottime.RelatedDataElementId = 439
left join RelatedDataTransactionHeaderResponse as Slotref on Slotref.TransactionHeaderId = th.Id and slotref.RelatedDataElementId = 442
left join RelatedDataTransactionHeaderResponse as SlotVen on SlotVen.TransactionHeaderId = th.Id and slotven.RelatedDataElementId = 443
left join RelatedDataTransactionHeaderResponse as SlotDept on SlotDept.TransactionHeaderId = th.Id and SlotDept.RelatedDataElementId = 444
Left Outer Join (
Select
TransactionHeader.Id as TransactionHeaderId
,Sum(Cast(Coalesce(TransactionPackageLine.WeightOverride,PackageContents.OriginalWeight,0) as Decimal (18,3))) as TotalActualWeight
from TransactionHeader
Inner Join TransactionPackageLine on TransactionPackageLine.TransactionHeaderId = transactionheader.Id
Left Outer Join (Select TransactionLine.TransactionPackageLineId
,Sum(TransactionLine.Weight) As OriginalWeight
from TransactionLine
Group By TransactionLine.TransactionPackageLineId)
As PackageContents on PackageContents.TransactionPackageLineId = TransactionPackageLine.Id
Group By TransactionHeader.Id
)
AS PackageTotals on PackageTotals.TransactionHeaderId = TH.Id
where tl.TransactionHeaderId = 2258752 and PRODUCT.BillOfMaterialType = 0
September 5, 2015 at 5:54 am
Quick thought, yes you will have to sum up the quantity, probably similar to the PackageTotals.TotalActualWeight
😎
Although one could try to decypher the query it is unlikely that answers derived from that are going to be accurate as there are no information on the schema or the data in your post.
September 5, 2015 at 7:22 am
Based on the query alone, I don't see where to put either a SUM or a COUNT (and it could be either depending on your structure). How is an item identified, or, where within what you're showing is the count of items in a transaction? If the items are individually listed, then probably a COUNT is the way to go. If there is already a count of items within a transaction, then SUM would be your answer.
But where exactly within that hot mess it goes? Not a clue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply