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?
 
awking00Commented:
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
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
awking00Commented:
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
 
awking00Commented:
You're welcome. Glad to have done so.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.