# How To Calculate Average Date Difference With A Mix of Dates and NULL Values [Answered]RSS

## 3 replies

Last post Dec 01, 2014 12:52 PM by PatriceSc

• Rivelyn

Member

209 Points

815 Posts

### How To Calculate Average Date Difference With A Mix of Dates and NULL Values

Not really sure how to title this question, hope it was close.

I've tried some If Else searches with no results, I am using t-sql for this, here is my select statement

`SELECT AVG(DATEDIFF(day,Inventory.DateEntered,Inventory.DateSold)) AS AvgDaysInInv`

That gives me the average days in inventory for sold items. What I am trying to figure out is, if an item has a NULL DateSold value because it has not sold. I need to take that time  in inventory into consideration.

Example.

Item1 was in inventory for 30 days then sold.

Item2 was in inventory for 20 days then sold.

Item3 has been in inventory for 45 days and has not sold.

Average days in inventory 32.

So how would I use the if else function if DateSold is NULL and I need to use getDate()?

Thanks!

• abhijithmani...

Member

370 Points

305 Posts

### Re: How To Calculate Average Date Difference With A Mix of Dates and NULL Values

Hi,

If you want to check the Sold Date for NULL values you can do as below statement -

`SELECT AVG(DATEDIFF(day,Inventory.DateEntered,ISNULL(Inventory.DateSold,GETDATE()))) AS AvgDaysInInv`

Not tested though :-)

My Blog - http://solvemytechissue.blogspot.in/
• limno

All-Star

122292 Points

9718 Posts

Moderator

### Re: How To Calculate Average Date Difference With A Mix of Dates and NULL Values

`select AVG(DATEDIFF(day,Inventory.DateEntered,ISNULL(Inventory.DateSold, getdate()))) AS AvgDaysInInv from Inventory`

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• PatriceSc

All-Star

40840 Points

13432 Posts