How to average a date column?

Question

How to average a date column?

dcase 42479

SSC-Addicted

Points: 484
More actions
November 2, 2010 at 11:37 am

#231191

Ok so I currently have a report that is grouped by Job#. I basically want to add a field and have it locate the earliest "Sch Start" date in this group for each Machine. I thought about doing an average but can't since this isn't a numerical field. Any ideas how this could be accoplished? I can do this in CR but I'm fairly new to SSRS.
Job # XXXXX (Group)
Part#01 Machine#03 Sch Start 11/25/10
Part#02 Machine#02 Sch Start 11/20/10
Part#03 Machine#04 Sch Start 11/28/10
Part#04 Machine#01 Sch Start 11/25/10
Part#05 Machine#01 Sch Start 11/21/10
Part#06 Machine#04 Sch Start 11/27/10
Part#07 Machine#02 Sch Start 11/27/10
Part#08 Machine#02 Sch Start 11/27/10
Part#09 Machine#04 Sch Start 11/25/10
Part#10 Machine#05 Sch Start 11/25/10
Part#11 Machine#04 Sch Start 11/22/10
Part#12 Machine#03 Sch Start 11/25/10
Part#13 Machine#05 Sch Start 11/23/10
Part#14 Machine#02 Sch Start 11/25/10
Part#15 Machine#01 Sch Start 11/26/10

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

rather than the AVG, i think you want the MIN([Sch Start]) for the earliest, or the MAX(([Sch Start]) for the latest date.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Hm, the only solutions I'd have for you with this puzzle would be in T-SQL code, not at the reporting layer.

If you really wanted a date average, you could take datediffs from 1/1/1900 in days (or hours, minutes, whatever you were looking for) and then dateadd the avg(datediff()) to 1/1/1900 to find the average.

For what you're looking for though, I agree with Lowell above, a SELECT MIN(), MAX() from tbl group by MachineName would be the best bet here.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

Averaging a date column is a fairly unusual thing to want to do, but if you really want to do this, it is achievable by first converting the datetime to a float, taking the average, then converting back to a datetime, e.g.

SELECT CONVERT(datetime, AVG(CONVERT(float, MyDateColumn)))

FROM MyTable

GROUP BY ...

dcase 42479 SSC-Addicted Points: 484 More actions · Answer 4

Thanks guys, the MIN function is working great! Now I'm having a little trouble if I only want it to show the MIN date for a specific machine. This is probably really simple but as I said, new to SSRS but learning quick!

dcase 42479 SSC-Addicted Points: 484 More actions · Answer 5

I got it to work out. Just had my IIF expression formatted wrong. Thanks again all!

komal145 SSCrazy Eights Points: 9918 More actions · Answer 6

komal145

SSCrazy Eights

Points: 9918

September 29, 2016 at 3:27 pm

#1904056

can you reply with your iff condition?

Thom A SSC Guru Points: 99133 More actions · Answer 7

komal145 (9/29/2016)
can you reply with your iff condition?

This is a 6 year old topic you've just "necro'd", it's unlikely that they're going to still have it now.

You'd be better off creating your own topic, with your question specifying for your own needs, and someone will be able to help 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

MMartin1 One Orange Chip Points: 27799 More actions · Answer 8

Hmm, I would rather do this in the SQL code ...

I scrapped up the following >>

with cte as

(

selectcast('20140701' as date) as cteDate

Union all

selectdateadd(d, 1,c.cteDate)

fromcte as c

wherec.cteDate <'20140731'

)

,nextCte as

(

select top 1

cteDate,

min(cteDate) Over (Partition by NULL) as minDate,

max(cteDate) Over (Partition by NULL) as maxDate

from cte

)

Select

minDate,

maxDate,

datediff(d, minDate, maxDate) as numDaysBetween,

dateadd(d, datediff(d, minDate, maxDate)/2,minDate) as averageDate

from nextCte

If gives me the average day of 7/16 for the month of July. You may need to do some tweaking for when the number of days between divided by two is not a whole number (like employ datetime instead of date). See if this gets you going.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Evil Kraig F (11/2/2010)
Hm, the only solutions I'd have for you with this puzzle would be in T-SQL code, not at the reporting layer.
If you really wanted a date average, you could take datediffs from 1/1/1900 in days (or hours, minutes, whatever you were looking for) and then dateadd the avg(datediff()) to 1/1/1900 to find the average.
For what you're looking for though, I agree with Lowell above, a SELECT MIN(), MAX() from tbl group by MachineName would be the best bet here.

DATETIME hasn't been crippled like the new DATE and DATETIME(2) datatypes. If you wanted an "average date", the easiest way would be to convert it to a FLOAT, take the average of the float, and then convert that answer back to a datetime.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 10

Sorry... I wasn't paying attention. This is a 6 year old thread and andrewd.smith did previously post code for the "Average Date" problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)