Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Select everything NOT sold in last 3 years

I have to do a select staement to show all partnumbers not sold in the last 3 years. I can easliy grab all part numbers that did sell in the last 3 years:

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers INNER JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
WHERE     (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))

Now how do I get the opposite of that?
0
kevlause
Asked:
kevlause
  • 2
1 Solution
 
MikeTooleCommented:
Make it a left join to select all Partnumbers, put the date criteria in the Join and look for nulls in OrderDetails

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers Left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
                  AND (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
Where  dbo.OrderDetails.PartNumberID
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers
WHERE NOT EXISTS
(SELECT 1
FROM   dbo.OrderDetails
WHERE dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
AND(dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102)))
0
 
MikeTooleCommented:
Oops, missed the last bit:

Make it a left join to select all Partnumbers, put the date criteria in the Join and look for nulls in OrderDetails

SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers Left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
                  AND (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
Where  dbo.OrderDetails.PartNumberID IS Null
0
 
NickUpsonCommented:
SELECT     dbo.PartNumbers.ManufacturerPartNumber
FROM         dbo.PartNumbers left JOIN
                      dbo.OrderDetails ON dbo.PartNumbers.PartNumberID = dbo.OrderDetails.PartNumberID
WHERE    dbo.OrderDetails.<some other field from this table> is NULL and
 (dbo.OrderDetails.ShipDate >= CONVERT(DATETIME, '2004-10-12 00:00:00', 102))
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now