MSSQL DateDiff Between two dates and then average elapsed time across all rows

I am stuck trying to get an overall average elapsed time between 2 dates for all rows for a certain day.

Heres is my setup:

Table A

ID

CreatedDate

Table B

ID

ID_A

LastUpdate

This is a one (Table A) to many (Table B) realationship

So I need:

1 - To get only the first LastUpdate(Table B) for each row in Table A as well as the CreatedDate from Table A for a given day i.e 2011-08-19.

2 - Then I need to get the elapsed time between CreatedDate(Table A) and LastUpdate(Table B)

3 - Once we have the elapsed time for each row work out the average elasped time for all rows, an overall if you will.

Any help you could provide would be very much apreciated

EDIT

As requested test data:

create table tablea(id int, createddate datetime)
create table tableb(id int, id_a int, lastupdate datetime)

insert into tablea select 1,'2010-12-09 21:37:32.137'
insert into tablea select 2,'2010-12-09 21:38:22.033'
insert into tablea select 3,'2010-12-27 09:48:30.723'
insert into tablea select 15,'2011-03-02 17:38:47.043'
insert into tablea select 16,'2011-03-02 18:02:39.497'
insert into tablea select 18,'2011-03-27 12:23:50.060'
insert into tablea select 19,'2011-03-27 12:50:55.887'
insert into tablea select 20,'2011-03-27 12:57:11.833'
insert into tablea select 21,'2011-03-27 13:05:24.157'
insert into tablea select 22,'2011-03-28 19:57:02.290'
insert into tablea select 24,'2011-04-05 18:17:22.707'
insert into tablea select 25,'2011-04-06 18:41:50.737'
insert into tablea select 26,'2011-04-25 17:52:14.603'
insert into tablea select 28,'2011-04-27 08:51:52.780'
insert into tablea select 29,'2011-04-27 08:52:15.100'
insert into tablea select 30,'2011-04-27 08:55:46.570'
insert into tablea select 31,'2011-04-27 08:56:24.447'
insert into tablea select 32,'2011-04-27 09:33:22.107'
insert into tablea select 33,'2011-04-27 09:34:15.400'
insert into tablea select 34,'2011-04-29 08:30:30.137'
insert into tablea select 35,'2011-04-29 09:58:45.050'
insert into tablea select 36,'2011-08-15 14:07:42.290'
insert into tablea select 37,'2011-08-15 14:07:42.390'
insert into tablea select 38,'2011-08-15 14:27:59.283'
insert into tablea select 39,'2011-08-15 14:27:59.353'
insert into tablea select 40,'2011-08-16 11:51:46.167'
insert into tablea select 41,'2011-08-16 11:51:46.220'
insert into tablea select 62,'2011-08-19 09:21:00.400'
insert into tablea select 63,'2011-08-19 09:21:00.467'
insert into tablea select 64,'2011-08-19 09:21:03.467'
insert into tablea select 65,'2011-08-19 09:39:09.930'
insert into tablea select 66,'2011-08-19 09:39:09.967'
insert into tablea select 67,'2011-08-19 09:44:34.617'
insert into tablea select 68,'2011-08-19 09:44:34.647'
insert into tablea select 71,'2011-08-19 09:49:57.760'
insert into tablea select 72,'2011-08-19 09:49:57.787'
insert into tablea select 73,'2011-08-19 09:51:57.403'
insert into tablea select 74,'2011-08-19 09:51:57.480'
insert into tablea select 75,'2011-08-19 09:52:00.490'
insert into tablea select 89,'2011-08-19 10:57:48.153'
insert into tablea select 90,'2011-08-19 10:57:48.247'
insert into tablea select 17,'2011-03-09 17:07:19.593'
insert into tablea select 23,'2011-03-28 20:51:18.337'
insert into tablea select 27,'2011-04-26 09:33:22.890'
insert into tablea select 42,'2011-08-16 14:03:28.067'
insert into tablea select 43,'2011-08-16 14:03:28.110'
insert into tablea select 44,'2011-08-16 14:03:31.283'
insert into tablea select 45,'2011-08-16 14:05:56.460'
insert into tablea select 46,'2011-08-16 14:05:56.487'
insert into tablea select 47,'2011-08-16 14:05:59.500'
insert into tablea select 48,'2011-08-18 06:06:08.460'
insert into tablea select 49,'2011-08-18 06:06:08.493'
insert into tablea select 50,'2011-08-18 06:06:11.553'
insert into tablea select 51,'2011-08-19 08:53:35.783'
insert into tablea select 52,'2011-08-19 08:53:35.813'
insert into tablea select 53,'2011-08-19 08:53:38.837'
insert into tablea select 54,'2011-08-19 08:58:43.103'
insert into tablea select 55,'2011-08-19 08:58:43.137'
insert into tablea select 56,'2011-08-19 09:07:54.663'
insert into tablea select 57,'2011-08-19 09:07:54.700'
insert into tablea select 58,'2011-08-19 09:07:57.687'
insert into tablea select 59,'2011-08-19 09:14:31.000'
insert into tablea select 60,'2011-08-19 09:14:31.070'
insert into tablea select 61,'2011-08-19 09:15:01.140'
insert into tablea select 69,'2011-08-19 09:47:20.577'
insert into tablea select 70,'2011-08-19 09:47:20.637'
insert into tablea select 76,'2011-08-19 09:58:24.527'
insert into tablea select 77,'2011-08-19 09:58:24.563'
insert into tablea select 78,'2011-08-19 09:58:27.727'
insert into tablea select 79,'2011-08-19 10:24:43.583'
insert into tablea select 80,'2011-08-19 10:24:43.620'
insert into tablea select 81,'2011-08-19 10:39:31.607'
insert into tablea select 82,'2011-08-19 10:39:31.650'
insert into tablea select 83,'2011-08-19 10:48:24.317'
insert into tablea select 84,'2011-08-19 10:48:24.343'
insert into tablea select 85,'2011-08-19 10:48:27.390'
insert into tablea select 86,'2011-08-19 10:53:39.003'
insert into tablea select 87,'2011-08-19 10:53:39.050'
insert into tablea select 88,'2011-08-19 10:53:42.037'
insert into tablea select 91,'2011-08-19 11:07:13.280'
insert into tablea select 92,'2011-08-19 11:07:13.313'
insert into tablea select 93,'2011-08-19 11:07:16.450'
insert into tableb select 1,1,'2010-12-09 21:37:32.223'
insert into tableb select 2,2,'2010-12-09 21:38:22.053'
insert into tableb select 3,1,'2010-12-18 12:05:43.213'
insert into tableb select 4,2,'2010-12-24 11:28:00.857'
insert into tableb select 5,3,'2010-12-27 09:48:30.757'
insert into tableb select 46,2,'2011-02-28 20:54:08.247'
insert into tableb select 47,15,'2011-03-02 17:38:47.153'
insert into tableb select 49,16,'2011-03-02 18:02:39.513'
insert into tableb select 52,17,'2011-03-09 17:24:54.683'
insert into tableb select 13,2,'2011-01-03 12:39:49.850'
insert into tableb select 53,17,'2011-03-10 19:37:31.677'
insert into tableb select 54,17,'2011-03-10 19:40:02.457'
insert into tableb select 55,17,'2011-03-10 19:40:59.930'
insert into tableb select 56,17,'2011-03-10 19:41:54.623'
insert into tableb select 58,17,'2011-03-13 09:19:26.663'
insert into tableb select 64,17,'2011-03-26 10:05:41.793'
insert into tableb select 65,17,'2011-03-26 10:13:06.773'
insert into tableb select 66,18,'2011-03-27 12:23:50.200'
insert into tableb select 67,19,'2011-03-27 12:50:55.933'
insert into tableb select 68,20,'2011-03-27 12:57:11.833'
insert into tableb select 69,21,'2011-03-27 13:05:24.173'
insert into tableb select 70,22,'2011-03-28 19:57:02.570'
insert into tableb select 71,22,'2011-03-28 19:57:26.857'
insert into tableb select 72,22,'2011-03-28 19:57:51.630'
insert into tableb select 73,23,'2011-03-28 20:51:18.383'
insert into tableb select 74,23,'2011-03-28 20:51:37.643'
insert into tableb select 75,24,'2011-04-05 18:17:22.770'
insert into tableb select 76,24,'2011-04-05 19:25:42.460'
insert into tableb select 77,24,'2011-04-05 21:08:40.787'
insert into tableb select 78,24,'2011-04-05 23:06:11.670'
insert into tableb select 79,24,'2011-04-05 23:18:14.483'
insert into tableb select 81,24,'2011-04-05 23:31:50.060'
insert into tableb select 82,24,'2011-04-05 23:39:37.403'
insert into tableb select 83,24,'2011-04-05 23:45:48.253'
insert into tableb select 84,24,'2011-04-06 18:03:29.003'
insert into tableb select 85,24,'2011-04-06 18:09:37.817'
insert into tableb select 86,24,'2011-04-06 18:41:47.423'
insert into tableb select 87,25,'2011-04-06 18:41:50.757'
insert into tableb select 88,24,'2011-04-06 20:23:39.847'
insert into tableb select 89,24,'2011-04-06 20:30:37.447'
insert into tableb select 90,24,'2011-04-06 20:58:40.903'
insert into tableb select 91,24,'2011-04-06 21:08:43.977'
insert into tableb select 92,26,'2011-04-25 17:52:14.667'
insert into tableb select 94,28,'2011-04-27 08:51:52.887'
insert into tableb select 95,29,'2011-04-27 08:52:15.110'
insert into tableb select 96,30,'2011-04-27 08:55:46.580'
insert into tableb select 98,32,'2011-04-27 09:33:22.127'
insert into tableb select 99,33,'2011-04-27 09:34:15.410'
insert into tableb select 102,34,'2011-04-29 08:30:30.173'
insert into tableb select 103,35,'2011-04-29 09:58:45.063'
insert into tableb select 104,28,'2011-05-21 14:59:42.567'
insert into tableb select 105,35,'2011-06-15 19:53:18.547'
insert into tableb select 109,36,'2011-08-15 14:07:42.310'
insert into tableb select 110,37,'2011-08-15 14:07:42.400'
insert into tableb select 111,38,'2011-08-15 14:27:59.333'
insert into tableb select 112,39,'2011-08-15 14:27:59.360'
insert into tableb select 113,40,'2011-08-16 11:51:46.180'
insert into tableb select 135,62,'2011-08-19 09:21:00.450'
insert into tableb select 136,63,'2011-08-19 09:21:00.477'
insert into tableb select 137,64,'2011-08-19 09:21:03.477'
insert into tableb select 138,65,'2011-08-19 09:39:09.950'
insert into tableb select 139,66,'2011-08-19 09:39:09.973'
insert into tableb select 140,67,'2011-08-19 09:44:34.627'
insert into tableb select 141,68,'2011-08-19 09:44:34.650'
insert into tableb select 144,71,'2011-08-19 09:49:57.770'
insert into tableb select 145,72,'2011-08-19 09:49:57.797'
insert into tableb select 146,73,'2011-08-19 09:51:57.447'
insert into tableb select 147,74,'2011-08-19 09:51:57.487'
insert into tableb select 148,75,'2011-08-19 09:52:00.5开发者_开发知识库10'
insert into tableb select 152,78,'2011-08-19 09:59:46.097'
insert into tableb select 153,77,'2011-08-19 09:59:46.170'
insert into tableb select 154,76,'2011-08-19 09:59:46.263'
insert into tableb select 155,75,'2011-08-19 09:59:46.320'
insert into tableb select 156,74,'2011-08-19 09:59:46.347'
insert into tableb select 157,73,'2011-08-19 09:59:46.387'
insert into tableb select 158,72,'2011-08-19 09:59:46.413'
insert into tableb select 159,71,'2011-08-19 09:59:46.437'
insert into tableb select 160,70,'2011-08-19 09:59:46.463'
insert into tableb select 161,69,'2011-08-19 09:59:46.487'
insert into tableb select 162,68,'2011-08-19 09:59:46.513'
insert into tableb select 163,67,'2011-08-19 09:59:46.537'
insert into tableb select 164,66,'2011-08-19 09:59:46.563'
insert into tableb select 165,65,'2011-08-19 09:59:46.597'
insert into tableb select 166,64,'2011-08-19 09:59:46.620'
insert into tableb select 167,63,'2011-08-19 09:59:46.647'
insert into tableb select 168,62,'2011-08-19 09:59:46.670'
insert into tableb select 6,3,'2011-01-01 20:04:37.693'
insert into tableb select 97,31,'2011-04-27 08:56:24.467'
insert into tableb select 106,31,'2011-06-15 19:53:19.027'
insert into tableb select 14,2,'2011-01-03 12:55:42.507'
insert into tableb select 15,2,'2011-01-03 12:56:04.507'
insert into tableb select 107,27,'2011-06-15 19:53:19.427'
insert into tableb select 108,23,'2011-06-15 19:53:19.783'
insert into tableb select 114,41,'2011-08-16 11:51:46.230'
insert into tableb select 169,60,'2011-08-19 09:59:46.697'
insert into tableb select 170,59,'2011-08-19 09:59:46.720'
insert into tableb select 171,58,'2011-08-19 09:59:46.747'
insert into tableb select 172,57,'2011-08-19 09:59:46.770'
insert into tableb select 173,56,'2011-08-19 09:59:46.797'
insert into tableb select 174,55,'2011-08-19 09:59:46.820'
insert into tableb select 175,54,'2011-08-19 09:59:46.847'
insert into tableb select 176,53,'2011-08-19 09:59:46.870'
insert into tableb select 177,52,'2011-08-19 09:59:46.897'
insert into tableb select 178,51,'2011-08-19 09:59:46.923'
insert into tableb select 179,50,'2011-08-19 09:59:46.947'
insert into tableb select 180,49,'2011-08-19 09:59:46.973'
insert into tableb select 181,48,'2011-08-19 09:59:46.997'
insert into tableb select 182,47,'2011-08-19 09:59:47.023'
insert into tableb select 183,46,'2011-08-19 09:59:47.047'
insert into tableb select 184,45,'2011-08-19 09:59:47.073'
insert into tableb select 185,44,'2011-08-19 09:59:47.097'
insert into tableb select 186,43,'2011-08-19 09:59:47.123'
insert into tableb select 187,42,'2011-08-19 09:59:47.147'
insert into tableb select 188,41,'2011-08-19 09:59:47.173'
insert into tableb select 189,40,'2011-08-19 09:59:47.197'
insert into tableb select 190,39,'2011-08-19 09:59:47.223'
insert into tableb select 191,38,'2011-08-19 09:59:47.263'
insert into tableb select 192,37,'2011-08-19 09:59:47.287'
insert into tableb select 193,36,'2011-08-19 09:59:47.313'
insert into tableb select 204,89,'2011-08-19 10:57:48.163'
insert into tableb select 205,90,'2011-08-19 10:57:48.263'
insert into tableb select 206,90,'2011-08-19 11:00:20.850'
insert into tableb select 207,89,'2011-08-19 11:00:20.877'
insert into tableb select 208,88,'2011-08-19 11:00:20.900'
insert into tableb select 209,87,'2011-08-19 11:00:20.927'
insert into tableb select 210,86,'2011-08-19 11:00:20.950'
insert into tableb select 211,85,'2011-08-19 11:00:20.977'
insert into tableb select 212,84,'2011-08-19 11:00:21.000'
insert into tableb select 213,83,'2011-08-19 11:00:21.027'
insert into tableb select 214,82,'2011-08-19 11:00:21.050'
insert into tableb select 215,81,'2011-08-19 11:00:21.077'
insert into tableb select 216,80,'2011-08-19 11:00:21.100'
insert into tableb select 217,79,'2011-08-19 11:00:21.127'
insert into tableb select 57,17,'2011-03-10 19:44:18.340'
insert into tableb select 93,27,'2011-04-26 09:33:22.990'
insert into tableb select 42,1,'2011-02-28 19:59:31.450'
insert into tableb select 43,2,'2011-02-28 20:20:14.560'
insert into tableb select 44,2,'2011-02-28 20:30:17.653'
insert into tableb select 45,2,'2011-02-28 20:53:33.747'
insert into tableb select 48,15,'2011-03-02 17:39:21.980'
insert into tableb select 50,16,'2011-03-02 18:03:40.917'
insert into tableb select 51,17,'2011-03-09 17:07:20.093'
insert into tableb select 63,17,'2011-03-19 09:02:55.330'
insert into tableb select 80,24,'2011-04-05 23:25:20.910'
insert into tableb select 59,17,'2011-03-14 18:28:45.263'
insert into tableb select 60,17,'2011-03-14 18:29:00.523'
insert into tableb select 61,17,'2011-03-14 18:33:34.380'
insert into tableb select 62,17,'2011-03-19 08:57:42.847'
insert into tableb select 115,42,'2011-08-16 14:03:28.077'
insert into tableb select 116,43,'2011-08-16 14:03:28.130'
insert into tableb select 117,44,'2011-08-16 14:03:31.290'
insert into tableb select 118,45,'2011-08-16 14:05:56.470'
insert into tableb select 119,46,'2011-08-16 14:05:56.497'
insert into tableb select 120,47,'2011-08-16 14:05:59.510'
insert into tableb select 121,48,'2011-08-18 06:06:08.477'
insert into tableb select 122,49,'2011-08-18 06:06:08.503'
insert into tableb select 123,50,'2011-08-18 06:06:11.567'
insert into tableb select 124,51,'2011-08-19 08:53:35.797'
insert into tableb select 125,52,'2011-08-19 08:53:35.823'
insert into tableb select 126,53,'2011-08-19 08:53:38.853'
insert into tableb select 127,54,'2011-08-19 08:58:43.120'
insert into tableb select 128,55,'2011-08-19 08:58:43.143'
insert into tableb select 129,56,'2011-08-19 09:07:54.680'
insert into tableb select 130,57,'2011-08-19 09:07:54.707'
insert into tableb select 131,58,'2011-08-19 09:07:57.703'
insert into tableb select 132,59,'2011-08-19 09:14:31.050'
insert into tableb select 133,60,'2011-08-19 09:14:31.077'
insert into tableb select 134,61,'2011-08-19 09:15:01.150'
insert into tableb select 142,69,'2011-08-19 09:47:20.620'
insert into tableb select 143,70,'2011-08-19 09:47:20.647'
insert into tableb select 149,76,'2011-08-19 09:58:24.537'
insert into tableb select 150,77,'2011-08-19 09:58:24.570'
insert into tableb select 151,78,'2011-08-19 09:58:27.783'
insert into tableb select 194,79,'2011-08-19 10:24:43.603'
insert into tableb select 195,80,'2011-08-19 10:24:43.627'
insert into tableb select 196,81,'2011-08-19 10:39:31.627'
insert into tableb select 197,82,'2011-08-19 10:39:31.670'
insert into tableb select 198,83,'2011-08-19 10:48:24.327'
insert into tableb select 199,84,'2011-08-19 10:48:24.350'
insert into tableb select 200,85,'2011-08-19 10:48:27.400'
insert into tableb select 201,86,'2011-08-19 10:53:39.017'
insert into tableb select 202,87,'2011-08-19 10:53:39.060'
insert into tableb select 203,88,'2011-08-19 10:53:42.050'
insert into tableb select 218,91,'2011-08-19 11:07:13.297'
insert into tableb select 219,92,'2011-08-19 11:07:13.320'
insert into tableb select 220,93,'2011-08-19 11:07:16.460'
insert into tableb select 100,32,'2011-04-27 09:36:53.943'
insert into tableb select 101,32,'2011-04-27 09:37:44.207'

select avg(datediff(s, tablea.createddate, b.lastupdate)) as Avg_Elapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a
where tablea.createddate >= '2011-08-19'

drop table tablea
drop table tableb

The above yeilds 0 regardless if where clause is included or not.

---

Create a subquery that gets a list of IDs and their minimum last updates. Then, use AVG and DATEDIFF functions to get the desired results

select avg(datediff(d, tablea.createddate, b.lastupdate)) as AvgDaysElapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a

Edit: The following test data/query works fine for me, so you'll need to post more info as to why your query isn't working. I suspect it has to do with your filter...

create table tablea(id int, createddate datetime)
create table tableb(id int, id_a int, lastupdate datetime)
insert into tablea select 1,'1/1/2000'
insert into tableb select 1,1,'1/1/2000 12:49'
insert into tableb select 2,1,'1/1/2000 12:50'
insert into tablea select 2,'1/1/2000 8:54'
insert into tableb select 3,2,'1/1/2000 8:55'
insert into tableb select 4,2,'1/1/2000 8:56'

select avg(datediff(s, tablea.createddate, b.lastupdate)) as AvgDaysElapsed
from tablea 
inner join (select id_a, min(lastupdate) as lastupdate from tableb group by id_a) b
  on tablea.id = b.id_a

drop table tablea
drop table tableb

---

Using the names in your test setup:

DECLARE @date datetime;
SET @date = '20110819';

WITH rankedUpdates AS (
  SELECT
    id,
    id_a,
    lastupdate,
    rn = ROW_NUMBER() OVER (PARTITION BY id_a ORDER BY lastupdate)
  FROM tableb
),
diffs AS (
  SELECT
    a.id,
    diff = DATEDIFF(MILLISECOND, a.createddate, b.lastupdate)
  FROM tablea a
    INNER JOIN rankedUpdates b ON a.id = b.id_a
  WHERE a.createddate >= @date
    AND a.createddate < DATEADD(DAY, 1, @date)
    AND b.rn = 1
)
SELECT AVG(diff) FROM diffs

This query uses ranking to get the minimal LastUpdate values.