SQL script help MAX Date

I'm needing only one line per item_id which would be the one with the greatest date.  The problem is there could be more then one entry per month so even if I select Max on the date it pulls all for that month.  Below is my script.

SELECT DISTINCT key2_value AS location, item_id, old_value AS old_inv_min, new_value AS new_inv_min, MAX(date_created) AS Expr1
FROM     p21_view_audit_trail_inv_mast_1314
WHERE  (column_changed = 'inv_min') AND (key2_cd = 'location_id')
GROUP BY key2_value, item_id, old_value, new_value
HAVING (MAX(date_created) BETWEEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, 
                  GETDATE()), 0))))

Open in new window


This is what I get from the script above.  I only want to see the one from 2018-01-12.  
  1. location      item_id      old_inv_min      new_inv_min      date_created
  2. 10000      Apples      0.000000000      2.000000000      2018-01-10 10:17:29.777
  3. 10000      Apples      2.000000000      40.000000000      2018-01-12 10:18:09.653
LVL 1
abarefootAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
How about this:
Select whatfieldsyouwant FROM p21_view_audit_trail_inv_mast_1314 
WHERE date_created = (SELECT MAX(date_created) 
FROM p21_view_audit_trail_inv_mast_1314 
WHERE  (column_changed = 'inv_min') AND (key2_cd = 'location_id')
GROUP BY key2_value, item_id HAVING (MAX(date_created) BETWEEN 
DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND 
DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))))
) 

Open in new window


The idea is to first only select the max date without looking at old_value and new_value and then pick whatever you want from the record with exactly that max date found.

Bye, Olaf.
0
awking00Information Technology SpecialistCommented:
I'm not sure what your having statement is measuring. If I can assume it's intending to find records within the past 3 months, then you could use something like the following:

SELECT location, item_id, old_inv_min, new_inv_min, date_created from
(SELECT key2_value AS location, item_id, old_value AS old_inv_min, new_value AS new_inv_min, date_created,
 row_number() over (partition by key2_value, item_id, datepart(mm,date_created) order by date_created desc) rn
 FROM     p21_view_audit_trail_inv_mast_1314
 WHERE  (column_changed = 'inv_min') AND (key2_cd = 'location_id'
 AND date_created > datediff(mm, -3, getdate())
)
WHERE rn = 1;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abarefootAuthor Commented:
awking00,

I'm getting an error near your last Where statement.
Incorrect syntax near the keyword 'WHERE'.

SELECT location, item_id, old_inv_min, new_inv_min, date_created from
(
SELECT key2_value AS location, item_id, old_value AS old_inv_min, new_value AS new_inv_min, date_created,
 row_number() over (partition by key2_value, item_id, datepart(mm,date_created) order by date_created desc) rn
 FROM     p21_view_audit_trail_inv_mast_1314
 WHERE  (column_changed = 'inv_min') AND (key2_cd = 'location_id')
 AND (date_created > datediff(mm, -1, getdate()))
 )
 WHERE rn = 1

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

abarefootAuthor Commented:
Figured it out.  I needed to call the SubQuery something.
SELECT location, item_id, old_inv_min, new_inv_min, date_created from
(
SELECT key2_value AS location, item_id, old_value AS old_inv_min, new_value AS new_inv_min, date_created,
 row_number() over (partition by key2_value, item_id, datepart(mm,date_created) order by date_created desc) rownum
 FROM     p21_view_audit_trail_inv_mast_1314
 WHERE  (column_changed = 'inv_min') AND (key2_cd = 'location_id')
 AND (date_created BETWEEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, 
                  GETDATE()), 0))))) sub
				  where sub.rownum = 1

Open in new window

0
abarefootAuthor Commented:
awking00,

I think row_number is going to work.
0
awking00Information Technology SpecialistCommented:
Sorry about that. I keep forgetting that sql server requires an alias for its subqueries (I'm more used to Oracle which doesn't)
0
abarefootAuthor Commented:
Thanks for all your help!
0
awking00Information Technology SpecialistCommented:
You're welcome. Glad to have done so.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.