Joining row from one table with the sum value from another table

Question

I have the table IPAddressHistory that contains three columns IPAddressID, DateFrom and DateTo, and another table IPAddressTimeValue with detailed values by particular time. So, I need select SUM values from IPAddressTimeValue related by IPAddressID between DateFrom and DateTo from IPAddressHistory. You can see what I want from sqlfiddle, there I used, for example, the 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:

Result output:

Answer 1

The query you need is this one:

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];

See it working here WITH you own select unions: http://sqlfiddle.com/#!6/abfe6/5

To format the dates (datefrom and dateto) as you want, just use some date format function. Don't forget to use it on the select field also on the group by.

Answer 2

I don't know if I understood correctly but you need something like this? Join tables via IPAddressID and use DateFrom, DateTo from History table in WHERE clause?

UPDATED

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

You can test It at: SQL FIDDLE