Two table join on datetime with unlike intervals - please advise

[dca]

I have two tables with information that I need to join together. One table has records for each server on a 15 minute interval containing system utilization metrics. I then have another table that posts records only when a system configuration change occurs. So records exist containing information on the number of CPUs and mega-bytes of memory but only at the dates when the system changed. I would like to join these two tables and fill in the missing date records in the configuration table with the information from the last-closest configuration record date to the system utilization record's date.


Globl
------------------
systemname <------------|
datetime <-----------|-----|
gbl_cpu_total_util | |
gbl_mem_util | |
| |
Configuration | | I need to somehow link
------------------ | | to the last-closest
systemname <-------| | date in the
datetime <------------------| configuration table.
CPUs
PhysicalMemory


I tried using select distinct but it appears to be too costly after 15 minutes I'm still waiting for the results. Also, I dont believe this will give me what I want as the first result will be selected which will be the performance metrics paired with the oldest configuration record, not the most recent relative to the performance metric datetime.

use reporter ;
select distinct a.systemname, a.datetime, a.gbl_cpu_total_util, b.gbl_num_cpu
from globl a inner join configuration b on
a.systemname = b.systemname and
b.datetime <= a.datetime

I really need to filter this where b.datetime = max(b.datetime) within the b.datetime <= a.datetime subsets.

 

[alfredp77]

Try this:

create table #report(
systemname
sdatetime
gbl_cpu_total_util
gbl_num_cpu
)

insert #report
select b.systemname, a.datetime, max(b.datetime), null,null
from globl a inner join configuration b on
a.systemname = b.systemname and
b.datetime <= a.datetime
group by b.systemname, a.datetime

update #report
set gbl_cpu_total_util = a.gbl_cpu_total_util,
gbl_num_cpu = a.gbl_num_cpu
from #report r join globl a
on r.systemname=a.systemname and
r.datetime=a.datetime

 

[dca]

Thanks, I'll try that out.