compare data and filter unique

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

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.

Thank you.
 
Thank you.

I get a "syntax" error in the code. Here is what I have in:

SELECT DISTINCTROW DataTbl.[Index], DataTbl.[Market Date], DataTbl.[Prod
Code], DataTbl.[Pkg Code], DataTbl.[Exp Date]
FROM DataTbl WHERE dataTbl.[Market Date] BETWEEN
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date(),1)) AND
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()+1,0)) AND [Prod Code]
NOT IN (SELECT dataTbl.[Prod Code] FROM DataTbl WHERE [Market Date] BETWEEN
DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1)) AND
DateSerial(Year(Date()),Month(Date()),1)-1)


Thank you.
 
I got it, Thank you very much. Very helpful
--
AJ


AJ said:
Thank you.

I get a "syntax" error in the code. Here is what I have in:

SELECT DISTINCTROW DataTbl.[Index], DataTbl.[Market Date], DataTbl.[Prod
Code], DataTbl.[Pkg Code], DataTbl.[Exp Date]
FROM DataTbl WHERE dataTbl.[Market Date] BETWEEN
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date(),1)) AND
DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()+1,0)) AND [Prod Code]
NOT IN (SELECT dataTbl.[Prod Code] FROM DataTbl WHERE [Market Date] BETWEEN
DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1)) AND
DateSerial(Year(Date()),Month(Date()),1)-1)


Thank you.
--
AJ


MGFoster said:
-----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-----
Click to expand...
Click to expand...
 
Back
Top