Re: compare data and filter unique

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

AJ wrote:
I have a table where we load products made each month. Each month we need to look at the products ran that month and determine if there are any new ones that were not run the previous 12 months.

Then we have to look at the month a year ago and determine if any of those have not been run in the last year and what they are if any.

I can not seem to think how to accomplish this. Any help is greatly appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

New products not in previous 12 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateSerial(Year(Date()),
Month(Date()),1) And DateSerial(Year(Date()), Month(Date())+1, 0)
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1)) And
DateSerial(Year(Date()), Month(Date()),1)-1
)

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date()),1)) returns the beginning of the month, 12 months ago.

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month.

The function DateSerial(Year(Date()), Month(Date()),1) returns the first
day of the current month.

The function DateSerial(Year(Date()), Month(Date())+1, 0) returns the
last day of the current month.



New products 12 months ago, not "run" in previous 11 months:

SELECT DISTINCT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -12,
DateSerial(Year(Date()), Month(Date()),1))
And DateAdd("m", -12, DateSerial(Year(Date()), Month(Date())+1,0))
AND product_name NOT IN
(SELECT product_name
FROM Products
WHERE introduction_date BETWEEN DateAdd("m", -11,
DateSerial(Year(Date()), Month(Date()),1))
And DateSerial(Year(Date()), Month(Date()),1)-1
)


The function DateAdd("m", -12,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 12 months ago.

The function DateAdd("m", -12, DateSerial(Year(Date()),
Month(Date())+1,0)) returns the last day of the month, 12 months ago.

The function DateAdd("m", -11,DateSerial(Year(Date()), Month(Date()),1))
returns the first day of the month, 11 months ago (the beginning of the
previous year).

The function DateSerial(Year(Date()), Month(Date()),1)-1 returns the
last day of the previous month (the end of the previous year).

Change the table and column names to suit your set up.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSaxrbIechKqOuFEgEQKO+wCgql7s0mMqtJ3ZEOO+jVIL2/eTOiwAoMpS
AKDtBYSuW2ZiI8vjcb2pLhKR
=MEbX
-----END PGP SIGNATURE-----
.

Relevant Pages