Return total count with max date rows

The following rows...

Auto ID    Doc_Type      Desc        LastUpdateDate

2                VN              School      9/1/2013
3                 VN             School       9/1/2013
4                 VN             School       9/1/2013
5                 VN             School        8/1/2013
6                 VN              School        8/1/2013
7                  VN            School         8/1/2013
8                  vN             School         8/1/2013


I'd like to return the maxdate total of 3 because there are 3 rows with 9/1/2013
so it should show count(auto_id)... returning 3
zachvaldezAsked:
Who is Participating?
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.

CharlesSenior Software EngineerCommented:
SELECT   COUNT (AUTO_ID) AS OCCURANCE, LAST_UPDATE_DATE
    FROM TABLE_NAME
   WHERE TRUNC (LAST_UPDATE_DATE) IN (SELECT MAX (TRUNC (LAST_UPDATE_DATE))
                                        FROM TABLE_NAME)
GROUP BY LAST_UPDATE_DATE
0
PortletPaulfreelancerCommented:
nb:
TRUNC() is Oracle syntax [The TRUNC (date) function returns date with the time portion of the day truncated]
If you want only the count returned than this should do that:

SELECT
  count(*)
FROM TABLE_NAME
WHERE LastUpdateDate = (SELECT max(LastUpdateDate)
                        FROM TABLE_NAME
                       )
;

NOTE: We only see dates in the sample above. IF the LastUpdateDate contains times as well as dates then the solution would need refinement.

    /* IF you just want a count at max(LastUpdateDate) */
    
    SELECT
      count(*)
    FROM TABLE_NAME
    WHERE LastUpdateDate = (SELECT max(LastUpdateDate)
                            FROM TABLE_NAME
                           )

    | COLUMN_0 |
    |----------|
    |        3 |

**Query 2**:

    /* IF you wanted the full records at max(LastUpdateDate) */
    SELECT
            [Auto ID]
          , [Doc_Type]
          , [Desc]
          , CONVERT(varchar(10), [LastUpdateDate], 121) AS LastUpdateDate
    FROM TABLE_NAME
    WHERE LastUpdateDate = (SELECT max(LastUpdateDate)
                            FROM TABLE_NAME
                           )
    
    | AUTO ID | DOC_TYPE |   DESC | LASTUPDATEDATE |
    |---------|----------|--------|----------------|
    |       2 |       VN | School |     2013-09-01 |
    |       3 |       VN | School |     2013-09-01 |
    |       4 |       VN | School |     2013-09-01 |


  [1]: http://sqlfiddle.com/#!3/ed724/8

Open in new window

    CREATE TABLE Table_Name
    	([Auto ID] int, [Doc_Type] varchar(2), [Desc] varchar(6), [LastUpdateDate] datetime)
    ;
    	
    INSERT INTO Table_Name
    	([Auto ID], [Doc_Type], [Desc], [LastUpdateDate])
    VALUES
    	(2, 'VN', 'School', '2013-09-01 00:00:00'),
    	(3, 'VN', 'School', '2013-09-01 00:00:00'),
    	(4, 'VN', 'School', '2013-09-01 00:00:00'),
    	(5, 'VN', 'School', '2013-08-01 00:00:00'),
    	(6, 'VN', 'School', '2013-08-01 00:00:00'),
    	(7, 'VN', 'School', '2013-08-01 00:00:00'),
    	(8, 'vN', 'School', '2013-08-01 00:00:00')
    ;

Open in new window

{+ edit - sorry}
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
David ToddSenior DBACommented:
Hi

The generic solution I use is something like this:

select
	st.key
	, st.SomeDateTime
	, st.SomeOtherValue
from dbo.SomeTable st
inner join (
	select
		ti.key
		, max( ti.SomeDateTime ) as MaxSomeDateTime
	from dbo.SomeTable ti
	group by
		ti.key
	) tt
	on tt.key = st.key
	and tt.MaxSomeDateTime = st.SomeDateTime
;

Open in new window


It returns the SomeOtherValue at the latest value of SomeDateTime. For instance, in monitoring disk usage, I often want the latest value, where the datetime might differ across several machines, so the above pattern will return the latest value etc.

HTH
  David
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

zachvaldezAuthor Commented:
Yeah,I missed to include the time
It may be possible that there are entries with same dates
but different time
Is that also covered in the suggested solution?
0
David ToddSenior DBACommented:
Hi

My solution will handle dates with time

Regards
  David
0
PortletPaulfreelancerCommented:
well all "will handle" time - but the results might not be what you are expecting

Auto ID    Doc_Type      Desc        LastUpdateDate
2                 VN              School         9/1/2013 13:14:15 -- this is MAX(LastUpdateDate)
3                 VN              School         9/1/2013 12:13:14
4                 VN              School         9/1/2013 11:12:13

OR: is '9/1/2013 00:00:00' = "the latest date" you want to use?

note the first entry assumes the need to get '9/1/2013 00:00:00' but uses Oracle syntax
0
PortletPaulfreelancerCommented:
for datetime, and assuming you want '9/1/2013 00:00:00' = "the latest date" then it's a matter of "truncating" the max(LastUpdateDate) then seeking data that is >= that value
    
    CREATE TABLE Table_Name
    	([Auto ID] int, [Doc_Type] varchar(2), [Desc] varchar(6), [LastUpdateDate] datetime)
    ;
    	
    INSERT INTO Table_Name
    	([Auto ID], [Doc_Type], [Desc], [LastUpdateDate])
    VALUES
    	(2, 'VN', 'School', '2013-09-01 13:14:15'),
    	(3, 'VN', 'School', '2013-09-01 12:13:14'),
    	(4, 'VN', 'School', '2013-09-01 11:12:13'),
    	(5, 'VN', 'School', '2013-08-01 00:00:00'),
    	(6, 'VN', 'School', '2013-08-01 00:00:00'),
    	(7, 'VN', 'School', '2013-08-01 00:00:00'),
    	(8, 'vN', 'School', '2013-08-01 00:00:00')
    ;

Open in new window

**Query 1**:

    /* IF you just want a count at max(LastUpdateDate) */
    
    SELECT
      count(*)
    FROM TABLE_NAME
    WHERE LastUpdateDate >= (SELECT dateadd(day, datediff(day,0, max(LastUpdateDate) ), 0)
                            FROM TABLE_NAME
                           )
    

**[Results][2]**:
    
    | COLUMN_0 |
    |----------|
    |        3 |


**Query 2**:

    /* IF you wanted the full records at max(LastUpdateDate) */
    SELECT
            [Auto ID]
          , [Doc_Type]
          , [Desc]
          , CONVERT(varchar(10), [LastUpdateDate], 121) AS LastUpdateDate
    FROM TABLE_NAME
    WHERE LastUpdateDate >= (SELECT dateadd(day, datediff(day,0, max(LastUpdateDate) ), 0)
                            FROM TABLE_NAME
                           )
    

**[Results][3]**:
    
    | AUTO ID | DOC_TYPE |   DESC | LASTUPDATEDATE |
    |---------|----------|--------|----------------|
    |       2 |       VN | School |     2013-09-01 |
    |       3 |       VN | School |     2013-09-01 |
    |       4 |       VN | School |     2013-09-01 |



  [1]: http://sqlfiddle.com/#!3/0bb34/3

Open in new window

0
zachvaldezAuthor Commented:
Oh ah, I forgot to add to filter the records to Doc_Type. IN this case it is 'VN'. Of course there are types.
Because of no filters, Im getting all the records...
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.