how to get a average of days with two different tables

There are two tables 1)HR_OrderRequest (column to be considered is HRdate) other columns are HRUID,UID 2)HR_Supplydetails(colmn to be considered is HRUID) other columns are createddttm,UID by considering the date fro开发者_StackOverflow社区m HR_Supplydetails we should find the average days taken for that particular UID time taken to release of HRdate .I have a problem getting a average of days

Please do the need .

---

You can do a datediff:

SELECT AVG(DATEDIFF(day, createddttm,HRdate))

And then do your join on the two tables.

---

so the steps would be first getting the number of days for a order so you have like these tables:

order (id, date) # this date is the date the order was placed
supply (id, date) # this date is the date the order was filled

these might not be right but i think they are close

select avg(days)
from (select (supply.date-order.date) as days
     from order join supply on order.id=supply.id)

but the sub query isn't needed as it can be written as:

select avg(supply.date-order.date)
from order join supply on order.id=supply.id;

now to map this to your tables:

HR_OrderRequest (HRUID, UID, HRdate)
HR_Supplydetails (HRUID, UID, createddttm)

select avg(HR_OrderRequest.HRdate-HR_Supplydetails.createddttm)
from HR_OrderRequest join HR_Supplydetails on HR_OrderRequest.HRUID=HR_Supplydetails.HRUID
where UID=?

clearly if you have date functions use them