Max Date Problem

Question

Post reply

Max Date Problem

swiedner

Mr or Mrs. 500

Points: 534
More actions
October 1, 2003 at 9:56 am

#159928

I have no idea how to get this result, I need HELP!!
Here are the field names:
evtid serverid jobname procdate
Here are all the records.
3 10 model Full Backup 2003-09-30 12:37:58.760
4 10 msdb Full Backup 2003-09-30 12:37:58.860
5 14 Distribution clean up: distribution 2003-09-30 15:36:23.563
6 14 Distribution clean up: distribution 2003-09-30 15:37:16.810
7 14 Distribution clean up: distribution 2003-10-01 09:57:02.717
I am trying to get the max date for EACH job name for THAT particular day, so , for example, I want to see two records for the Distribution clean up: distribution, one for the max entry on 9-30 and the other one for the 10-1 item
Here is what I tried:
select serverid, jobname, max(procdate)
from disjobevt
group by serverid, jobname
and the results:
14 Distribution clean up: distribution 2003-10-01 09:57:02.717
10 model Full Backup 2003-09-30 12:37:58.760
10 msdb Full Backup 2003-09-30 12:37:58.860
It is just pulling the max date period, instead of the max date for 9-30, and the max date for 10-01
HELP!!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

hi,

this might give you a nudge in the right direction :-

select d1.serverid, d1.jobname,convert(char(11),d1.procdate) 'date' , max(procdate)'max_procdate'

from disjobevt d1

where convert(char(11),d1.procdate) in (select convert(char(11),d2.procdate) from disjobevt d2

where d1.serverid=d2.serverid

and d1.jobname=d2.jobname

)

group by d1.serverid, d1.jobname , convert(char(11),d1.procdate)

this converts the datetime to just a date, and then uses that to get a list of days that a job was run. and then the max function works correctly.

HTH

Paul

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

SELECT ServerId, JobName, MAX(ProcDate)

FROM DisjObEvt

GROUP BY ServerId, JobName, CAST(ProcDate AS int)

--Jonathan

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 3

something like:

select A.serverid, A.jobname, max(A.procdate)

froma_tst A

join(

--get distinct job/processing days

select distinct jobname, convert(char(10), procdate, 112) procdate2

from a_tst

) B

onB.jobname = A.jobname

andB.procdate2 = convert(char(10), A.procdate, 112)

group by

A.serverid,

A.jobname,

convert(char(10), A.procdate, 112)

Jon

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 4

Jonathan Tomkins

Ten Centuries

Points: 1268

October 1, 2003 at 11:01 am

#476599

I like that one Jonathan

way better than mine

swiedner Mr or Mrs. 500 Points: 534 More actions · Answer 5

This was perfect guys, not sure I understand why it works. The cast appears to be the key, but it works like a charm. THANKS!

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 6

Jonathan,

I've found an interresting side effect with this cast function.

If you cast a datetime to an int it round up to the nearest integer value and it does not just cut the decimal part of the field.

When I've tried out your example I could not understand why I only have a single row for 'Distribution clean up: distribution'

try this out:

create table #disjobevt

(

evtid int,

serverid int,

jobname varchar(250),

procdate datetime

)

insert into #disjobevt

select 3, 10, 'model Full Backup', '2003-09-30 12:37:58.760'

union all

select 4, 10, 'msdb Full Backup', '2003-09-30 12:37:58.860'

union all

select 5, 14, 'Distribution clean up: distribution', '2003-09-30 15:36:23.563'

union all

select 6, 14, 'Distribution clean up: distribution', '2003-09-30 15:37:16.810'

union all

select 7, 14, 'Distribution clean up: distribution', '2003-10-01 09:57:02.717'

SELECTServerId,

JobName,

CAST(ProcDate AS int) as ProcDateInt,

CAST(ProcDate AS decimal(20,0)) as ProcDateDecimal,

FLOOR(CAST(ProcDate AS float)) as ProcDateFloor,

ProcDate

FROM#DisjObEvt

Here is the result:

ServerIdJobNameProcDateIntProcDateDecimalProcDateFloorProcDate

10model Full Backup378933789337892.02003-09-30 12:37:58.760

10msdb Full Backup378933789337892.02003-09-30 12:37:58.860

14Distribution clean up: distribution378933789337892.02003-09-30 15:36:23.563

14Distribution clean up: distribution378933789337892.02003-09-30 15:37:16.810

14Distribution clean up: distribution378933789337893.02003-10-01 09:57:02.717

(5 row(s) affected)

Notice that the cast to int and decimal makes a round but not the float.

So here is the correct answer to the solution:

SELECT ServerId, JobName, MAX(ProcDate)

FROM DisjObEvt

GROUP BY ServerId, JobName, floor(CAST(ProcDate AS float))

Never the less your solution is really very elegant. I like it

Bye

Gabor

Bye
Gabor

Jonathan SSC-Insane Points: 20427 More actions · Answer 7

quote:
Jonathan,
I've found an interresting side effect with this cast function.
If you cast a datetime to an int it round up to the nearest integer value and it does not just cut the decimal part of the field.
When I've tried out your example I could not understand why I only have a single row for 'Distribution clean up: distribution'
try this out:
create table #disjobevt
(
evtid int,
serverid int,
jobname varchar(250),
procdate datetime
)
insert into #disjobevt
select 3, 10, 'model Full Backup', '2003-09-30 12:37:58.760'
union all
select 4, 10, 'msdb Full Backup', '2003-09-30 12:37:58.860'
union all
select 5, 14, 'Distribution clean up: distribution', '2003-09-30 15:36:23.563'
union all
select 6, 14, 'Distribution clean up: distribution', '2003-09-30 15:37:16.810'
union all
select 7, 14, 'Distribution clean up: distribution', '2003-10-01 09:57:02.717'
SELECTServerId,
JobName,
CAST(ProcDate AS int) as ProcDateInt,
CAST(ProcDate AS decimal(20,0)) as ProcDateDecimal,
FLOOR(CAST(ProcDate AS float)) as ProcDateFloor,
ProcDate
FROM#DisjObEvt
Here is the result:
ServerIdJobNameProcDateIntProcDateDecimalProcDateFloorProcDate
10model Full Backup378933789337892.02003-09-30 12:37:58.760
10msdb Full Backup378933789337892.02003-09-30 12:37:58.860
14Distribution clean up: distribution378933789337892.02003-09-30 15:36:23.563
14Distribution clean up: distribution378933789337892.02003-09-30 15:37:16.810
14Distribution clean up: distribution378933789337893.02003-10-01 09:57:02.717
(5 row(s) affected)
Notice that the cast to int and decimal makes a round but not the float.
So here is the correct answer to the solution:
SELECT ServerId, JobName, MAX(ProcDate)
FROM DisjObEvt
GROUP BY ServerId, JobName, floor(CAST(ProcDate AS float))
Never the less your solution is really very elegant. I like it
Bye
Gabor

Thanks, Gabor. Knowing how these values are stored internally, I was a bit surprised myself when I found that casting to int "worked."

That being said, this is closer to the bone (and I've actually used this method before):

SELECT ServerId, JobName, MAX(ProcDate)

FROM DisjObEvt

GROUP BY ServerId, JobName, SUBSTRING(CAST(ProcDate AS BINARY(8)),1,4)

--Jonathan

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 8

Just to add my twopenny worth:-

SELECT ServerId, JobName, MAX(ProcDate)

FROM DisjObEvt

GROUP BY ServerId, JobName, convert(varchar(255),procdate,106)

Vladan SSC-Insane Points: 22072 More actions · Answer 9

Since we have many datetime fields in our database, and quite often only the date part is needed, we have a UDF that "strips off" the time from a datetime value. Then such query could be

SELECT ServerId, JobName, MAX(ProcDate)

FROM DisjObEvt

GROUP BY ServerId, JobName, nf_Dpart_date(procdate)

Of course the UDF is very simple, but anyway it makes larger queries a lot more understandable than if you'd write the entire conversion again and again.

Jonathan SSC-Insane Points: 20427 More actions · Answer 10

Okay, let's beat this poor horse some more by pitching pennies at it...

quote:
Just to add my twopenny worth:-
SELECT ServerId, JobName, MAX(ProcDate)
FROM DisjObEvt
GROUP BY ServerId, JobName, convert(varchar(255),procdate,106)

You'll get fewer surprises if you standardize on CONVERT(char(8),ProcDate,112). Try using your method in the ORDER BY clause...

quote:
Since we have many datetime fields in our database, and quite often only the date part is needed, we have a UDF that "strips off" the time from a datetime value. Then such query could be
SELECT ServerId, JobName, MAX(ProcDate)
FROM DisjObEvt
GROUP BY ServerId, JobName, nf_Dpart_date(procdate)
Of course the UDF is very simple, but anyway it makes larger queries a lot more understandable than if you'd write the entire conversion again and again.

But that's much a much less efficient way to save a few characters in your code. I don't consider this "entire conversion":

CONVERT(char(8),ProcDate,112)

To be less undestandable or less onerous than:

dbo.nf_Dpart_date(ProcDate)

And it will certainly process faster.

With Yukon, we'll be able to just cast the datetime to a date. That will be concise, fast, and understandable.

--Jonathan

rstone SSCertifiable Points: 6209 More actions · Answer 11

Would not casting to an int will result in grouping by the 24 hour period from noon to noon, not midnight to midnight?

I suppose you could pick the start time of the 24 hour period by subtracting or adding a value before the floor. For example,

FLOOR(CAST(ProcDate as float) - .041666666667) -- subtract 1 hr = 1/24

would group values from 1 AM to 1 AM. The "day" part for the period is that for the start of the period when subtracting.

To reproduce the cast to int results using this method, add .5 (i.e., 12 hrs) before the floor to simulate the rounding up.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉