CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Sanprof
Sanprof

Reputation: 409

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

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:

enter image description here enter image description here

Result output: enter image description here

Upvotes: 5

Views: 3091

Answers (2)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

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

Upvotes: 1

Jorge Campos
Jorge Campos

Reputation: 23361

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.

Upvotes: 2

Related Questions