asked on

Get the different records within the same table

Hi,

I have a table that contains data from different months. I am trying to retrieve all the records that exist in the current month but not in the previous month. This is my code, but need help.

SELECT *
FROM T1
WHERE  ReportAsOfDate = '9/30/2018' AND
[EntryId] NOT IN
    (SELECT * 
     FROM T1 WHERE ReportAsOfDate = '8/30/2018')

Open in new window

Jim Horn

>I am trying to retrieve all the records that exist in the current month but not in the previous month.
You'll have to give us your table schema and how this is defined, but for starters..

SELECT *
FROM T1
WHERE  ReportAsOfDate >= '20181001' AND ReportAsOfDate < '20181101'

Open in new window

mcrmg

ASKER

Please have a look.

There are two columns. I would like to find out the EntryId that did not exist in 8/30/2018. In other words, I need to find out the new Entryid for 9/30/2018. thank you
test.xlsx

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mcrmg

ASKER

thank you very much

Jim Horn

Thanks.

Slight edit to above code, did not have the table alias a defined

SELECT * FROM T1 WHERE ReportAsOfDate ='20180930'
EXCEPT
SELECT * FROM T1 WHERE ReportAsOfDate ='20180830'

... or ... 

SELECT a.* 
FROM (SELECT * FROM T1 WHERE ReportAsOfDate ='20180930') a
  LEFT JOIN (SELECT * FROM T1 WHERE ReportAsOfDate ='20180830') b ON a.EntryID = b.EntryID
WHERE b.EntryID IS NULL

Open in new window

mcrmg

ASKER

Thanks again for the help.