Need Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Need Help

Author

Topic

dmcdonnell
Starting Member

2 Posts

Posted - 2013-10-21 : 15:05:11

I Just posted a small question in this forum, Though the answers were helpful, I am still lost at how to proceed, So I decided I would elaborate on the entirety of what I am trying to achieve.

I have 2 tables, Parts & WOParts. Parts keeps a comprehensive list of all parts in all warehouses, WOParts are all parts that have been on work orders. I am trying to make a query that a report can use, which will tell how many parts have been used in a selected time frame, and also identify parts that are not on WO's in that time frame. Because the data was bad to begin with, you will notice some things in the code I have so far that are unconventional. I am untrained in T-SQL so any improvements on my current code are also welcome.

Crucial or Useful Columns in each Table

Parts: p
Part Number -[PartNo] (PK, Not Null)
Warehouse - [Warehouse] (PK, Not Null)
Master SKU - [MasterPartNo]
Description - [Description]
Cust. Part Number - [PartNoAlias]
Bin Location - [Bin]
Part Cost - [Cost]
Part Price - [List]
Stock Qty. - [OnHand]
Alt. Number - [PartNoOriginal]
Alt. Number - [OldNumber]

WOParts: w
Order Number - [WONo] (PK, Not Null)
Order Date - [EntryDate] (PK, Not Null)
Cust. ID - [BillTo]
Part Number - [PartNo]
Cust. Part Number - [PartNoAlias]
Amount Sold - [Qty]

The code below returns to me all parts in the warehouse and then dates in which they were ordered since the date i specified:

SELECT p.Warehouse, p.PartNo, p.PartNoAlias, p.PartNoOriginal, p.OldNUmber, p.description, p.Bin, p.onhand, p.Cost, p.List, w.EntryDate, w.Qty
FROM (SELECT *
FROM woparts
WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w
right join
(SELECT *
FROM parts
WHERE Left(Warehouse,1)='t' and OnHand > 0) p
ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or w.PartNoAlias=p.PartNoAlias) and p.Warehouse= left (w.BillTo,5)

with what i have returned, If there were multiple orders on a certain part in a warehouse, i will show up multiple times on this list. What I need help with is to modify this to show, only 1 row of each part number per warehouse (similar to the Parts PK). and show the number of orders placed for that part ( a unique count of {P.PartNo, P.Warehouse}), a sum of Qty sold in that date range ( Sum of {w.Qty} per distinct {p.PartNo,P.Warehouse}), and the last entry sale date of the part ( max{EntryDate} per distinct {P.PartNumber,P.Warehouse}).

It sounds like a big task, but for someone who knows this T-SQL well, it shouldn't be that difficult. I just hope I did well explaining, Please ask any questions if you are confused about what I am looking for, I have been trying to figure this out for a while and am not knowledgeable enough about code.

Thank you very much to whoever helps me!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 15:21:14

sounds like this to me


SELECT p.Warehouse, p.PartNo, COUNT(*) AS OrderCount
SUM(w.Qty) AS QtySold,
MAX(EntryDate) AS LastSaleDate
FROM (SELECT * 
FROM woparts
WHERE Left(billto,1)='t' and EntryDate > '2012-10-16' and WONo<910000000) w
right join 
(SELECT *
FROM parts
WHERE Left(Warehouse,1)='t' and OnHand > 0) p 
ON (p.partno=w.partno or w.PartNo = p.PartNoOriginal or w.partno = p.PartNoAlias or w.PartNo = p.OldNUmber or w.PartNoAlias=p.PartNoAlias) and p.Warehouse= left (w.BillTo,5)
GROUP BY p.Warehouse, p.PartNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources