Join a row from one table with a total value from another table

Question

I have an IPAddressHistory table that contains three IPAddressID , DateFrom, and DateTo columns , and another IPAddressTimeValue table with detailed values ​​for a specific time. So, I need to select the SUM values ​​from IPAddressTimeValue associated with the IPAddressID between DateFrom and DateTo from IPAddressHistory . You can see what I want from sqlfiddle , there I used, for example, simple static dates from IPAddressHistory and UNION ALL. Thanks.

Initial Tables:

CREATE TABLE IPAddressHistory(
  [IPAddressHistoryID] int IDENTITY(1,1) NOT NULL,
  [IPAddressID] int NOT NULL,
  [DateFrom] datetime,
  [DateTo] datetime,
CONSTRAINT [PK_IPAddressHistory] PRIMARY KEY CLUSTERED 
(
    [IPAddressHistoryID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

CREATE TABLE IPAddressTimeValue(
  [IPAddressTimeValueID] int IDENTITY(1,1) NOT NULL,
  [IPAddressID] int NOT NULL,
  [Time] datetime,
  [CCNI] int,
  [TRNI] int,
CONSTRAINT [PK_IPAddressTimeValue] PRIMARY KEY CLUSTERED 
(
    [IPAddressTimeValueID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

Initial data:

Output result:

Answer 1

:

select a.[IPAddressID], 
       a.[DateFrom], 
       a.[DateTo], 
       SUM([CCNI]) [CCNI], 
       SUM([TRNI]) [TRNI]
  from IPAddressHistory a
         INNER JOIN IPAddressTimeValue b 
                ON (a.[IPAddressID] = b.[IPAddressID])
 where b.[Time] > a.[DateFrom]
   and b.[Time] <= a.[DateTo]
 group by a.[IPAddressID], a.[DateFrom], a.[DateTo];

, . : http://sqlfiddle.com/#!6/abfe6/5

(datefrom dateto), , . .

Answer 2

, , - ? IPAddressID DateFrom, DateTo History WHERE?

select h.IPAddressID, sum(CCNI) as CCNI, sum(TRNI) as TRNI, DateFrom, DateTo 
from IPAddressHistory h
left join IPAddressTimeValue v on h.IPAddressID = v.IPAddressID 
where v.[time] > h.DateFrom and v.[time] <= h.DateTo
group by h.IPAddressID, DateFrom, DateTo

: SQL FIDDLE