I need to make a query that will select all items only with ALL their warehouses datelastsale earlier than 2016-09-01.
If Wh1, Wh2 are before 2016-09-01 or blank, but Wh3 is 2017-09-01 I don't want this item to show.
I first thought of using this but I just can't put my finger on making int work the way I want...
SELECT fd.Warehouse, I.StockCode, I.Description, fd.QtyOnHand, fd.QtyOnOrder, fd.DateLastSale
FROM InvMaster AS I INNER JOIN
(SELECT StockCode, Warehouse, QtyOnHand, QtyOnOrder, DateLastSale FROM InvWarehouse WHERE (DateLastSale<'2016-09-01' OR DateLastSale IS NULL)) AS fd ON I.StockCode = fd.StockCode
In reference of the example I provided, this will list the item with Wh1 and Wh2, but there can be a Wh3 not showing, so I wouldn't want this item at all.
I tried
SELECT I.StockCode, I.Description
FROM InvMaster AS I INNER JOIN
(SELECT StockCode, MAX(DateLastSale) FROM InvWarehouse WHERE (DateLastSale<'2016-09-01' OR DateLastSale IS NULL) GROUP BY StockCode) AS fd ON I.StockCode = fd.StockCode
But this doesn't work at all and I would lose my warehouse info such as QtyOnHand, QtyOnOrder.
Any help would be appreciated.
To really help we need CREATE AND INSERT statements for the tables and what is the expected output for the data supplied.
However this might get you going
SELECTStockCode
FROM(SELECTw.StockCode,w.Warehouse,MAX(w.DateLastSale)
FROM#InvWarehouse w
GROUPBY w.StockCode,w.Warehouse
) a (StockCode,Warehouse,DateLastSale)
GROUPBY StockCode
HAVINGCOUNT(Warehouse) =
SUM(CASE WHEN DateLastSale IS NULL OR DateLastSale < '20160901' THEN 1 ELSE 0 END)
Far away is close at hand in the images of elsewhere.
Anon.
September 10, 2019 at 2:00 pm
SELECT fd.Warehouse,
I.StockCode,
I.Description,
fd.QtyOnHand,
fd.QtyOnOrder,
fd.DateLastSale
FROM InvMaster AS I
INNER JOIN InvWarehouse fd
ON I.StockCode = fd.StockCode
AND (fd.DateLastSale < '2016-09-01'
OR fd.DateLastSale IS NULL)
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.DateLastSale > '2016-09-01')
September 10, 2019 at 2:20 pm
Hello Jonathan. I tried it, unfortunately, for part no XXX708, it selects 3 warehouses with no activity but there are 2 more with recent sales.
I would want XXX708 to not show as it has recent activity in at least one of the 5 warehouses.
David, we are on something. I ran the query and it worked. So far it seems to be a multi-step process.
September 10, 2019 at 2:56 pm
Now my best shot based on David's reply. It works via a temporary table, but it is a bit complex. If there is a way to simplify, I would love to hear.
--- STEP 1
DELETE FROM [db1].dbo.tblTestAlpha
--- STEP 2
INSERT INTO [db1].dbo.tblTestAlpha
SELECTStockCode, '1' as Expr1, '2' as Expr2 -- because of mandatory 3 columns
FROM(SELECTw.StockCode,w.Warehouse, MAX(w.DateLastSale)
FROMInvWarehouse w
GROUPBY w.StockCode,w.Warehouse, QtyOnHand
) a (StockCode,Warehouse,DateLastSale)
GROUPBY StockCode
HAVINGCOUNT(Warehouse) =
SUM(CASE WHEN DateLastSale IS NULL OR DateLastSale < '20160901' THEN 1 ELSE 0 END)
---STEP 3
SELECT T.StockCode, W.Warehouse, W.QtyOnHand, W.QtyOnOrder FROM [db1].[dbo].[tblTestAlpha] T INNER JOIN [db2].dbo.InvWarehouse W ON T.StockCode=W.StockCode WHERE W.QtyOnHand>0 OR W.QtyOnOrder>0
September 10, 2019 at 4:29 pm
Hello Jonathan. I tried it, unfortunately, for part no XXX708, it selects 3 warehouses with no activity but there are 2 more with recent sales.
You just need to add
AND x.Warehouse = fd.Warehouse
to the NOT EXISTS:
SELECT fd.Warehouse,
I.StockCode,
I.Description,
fd.QtyOnHand,
fd.QtyOnOrder,
fd.DateLastSale
FROM InvMaster AS I
INNER JOIN InvWarehouse fd
ON I.StockCode = fd.StockCode
AND (fd.DateLastSale < '2016-09-01'
OR fd.DateLastSale IS NULL)
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.Warehouse = fd.Warehouse
AND x.DateLastSale > '2016-09-01')
September 10, 2019 at 6:04 pm
Hello Jonathan, I still get the same results but I adapted your method to another table and so far, so good.
SELECT W.Warehouse, I.StockCode, I.Description, W.QtyOnHand, W.QtyOnOrder, W.DateLastSale, W.DateLastPurchase, W.UnitCost
FROM InvMaster AS I INNER JOIN
InvWarehouse AS W ON W.StockCode = I.StockCode WHERE NOT EXISTS
(SELECT * FROM dbo.InvMovements M WHERE
(MovementType='S' AND EntryDate>'2016-09-01' AND W.StockCode=M.StockCode)
OR
(TrnType='I' AND EntryDate>'2016-09-01' AND W.StockCode=M.StockCode)
)
ORDER BY I.StockCode
For the record, 'I' are transactions done through jobs and 'S' Sales.
Thanks
September 10, 2019 at 6:23 pm
re "for part no XXX708, it selects 3 warehouses with no activity but there are 2 more with recent sales"
I think the initial query I gave you should work:
SELECT fd.Warehouse,
I.StockCode,
I.Description,
fd.QtyOnHand,
fd.QtyOnOrder,
fd.DateLastSale
FROM InvMaster AS I
INNER JOIN InvWarehouse fd
ON I.StockCode = fd.StockCode
AND (fd.DateLastSale < '2016-09-01'
OR fd.DateLastSale IS NULL)
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.DateLastSale > '2016-09-01')
There is no way that query can return any items that exist on any warehouse inventory with a DateLastSale greater than '2016-09-01'
September 10, 2019 at 7:43 pm
re "for part no XXX708, it selects 3 warehouses with no activity but there are 2 more with recent sales"
I think the initial query I gave you should work:
SELECT fd.Warehouse,
I.StockCode,
I.Description,
fd.QtyOnHand,
fd.QtyOnOrder,
fd.DateLastSale
FROM InvMaster AS I
INNER JOIN InvWarehouse fd
ON I.StockCode = fd.StockCode
AND (fd.DateLastSale < '2016-09-01'
OR fd.DateLastSale IS NULL)
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.DateLastSale > '2016-09-01')There is no way that query can return any items that exist on any warehouse inventory with a DateLastSale greater than '2016-09-01'
Unless the DateLastSale is 2016-09-01 00:00:00.000 and it is defined as a datetime or datetime2 datatype.
September 10, 2019 at 8:07 pm
They are datetime type. I guess that removing the apostrophes around the dates was required - it seems to work now without them
September 10, 2019 at 8:22 pm
Jonathan AC Roberts wrote:re "for part no XXX708, it selects 3 warehouses with no activity but there are 2 more with recent sales"
I think the initial query I gave you should work:
SELECT fd.Warehouse,
I.StockCode,
I.Description,
fd.QtyOnHand,
fd.QtyOnOrder,
fd.DateLastSale
FROM InvMaster AS I
INNER JOIN InvWarehouse fd
ON I.StockCode = fd.StockCode
AND (fd.DateLastSale < '2016-09-01'
OR fd.DateLastSale IS NULL)
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.DateLastSale > '2016-09-01')There is no way that query can return any items that exist on any warehouse inventory with a DateLastSale greater than '2016-09-01'
Unless the DateLastSale is 2016-09-01 00:00:00.000 and it is defined as a datetime or datetime2 datatype.
Yes, would have to change the not exists to >=
WHERE NOT EXISTS(SELECT *
FROM InvWarehouse x
WHERE x.StockCode = fd.StockCode
AND x.DateLastSale >= '20160901')
Also would be better to remove the hyphens from the date if the column is defined as type datetime on the database; as if the language is set to british it would mean a different date to the one the OP wants, i.e. 9th January 2016.
September 11, 2019 at 11:33 am
Try this:
SELECT fd.Warehouse, I.StockCode, I.Description, fd.QtyOnHand, fd.QtyOnOrder, fd.DateLastSale
FROM InvMaster AS I
JOIN InvWarehouse fd
ON fd.StockCode = I.StockCode
WHERE '2016-09-01' > ALL (select isnull(DateLastSale, '2016-01-01') from InvWarehouse where StockCode = I.StockCode)
September 11, 2019 at 12:16 pm
This way is quite new to me and it works (same numbers of records).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply