Unsure how to define this query

Question

Post reply

Unsure how to define this query

saintor1

Mr or Mrs. 500

Points: 562

September 10, 2019 at 12:27 pm

#3678717

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.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18407 More actions · Answer 1

I'm not sure from your description exactly what you are trying to do but a NOT EXISTS clause should do the job for you.

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 fd.DateLastSale > '2016-09-01')

Thom A SSC Guru Points: 99143 More actions · Answer 2

FYI: Duplicate post of https://www.sqlservercentral.com/forums/topic/unsure-how-to-define-this-query

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 3

Oh!... I apologize for the duplicate, it didn't pass through the first time (gateway error). :o( Please delete if possible.

This reply was modified 5 years, 10 months ago by saintor1.

Unsure how to define this query

Cookies on SQLServerCentral