CODEWITHSUNDEEP
sqlsql-server
Rahmatizadeh
Rahmatizadeh

Reputation: 125

Summing up columns from two different tables

I have two different tables FirewallLog and ProxyLog. There is no relation between these two tables. They have four common fields :

LogTime     ClientIP     BytesSent     BytesRec

I need to Calculate the total usage of a particular ClientIP for each day over a period of time (like last month) and display it like below:

Date        TotalUsage
2/12         125
2/13         145
2/14         0
.               .
.               .
3/11         150
3/12         125

TotalUsage is SUM(FirewallLog.BytesSent + FirewallLog.BytesRec) + SUM(ProxyLog.BytesSent + ProxyLog.BytesRec) for that IP. I have to show Zero if there is no usage (no record) for that day. I need to find the fastest solution to this problem. Any Ideas?

Upvotes: 1

Views: 341

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you don't have a calendar table, you can create one using a recursive CTE:

declare @startdate date = '2013-02-01';
declare @enddate date = '2013-03-01';
with dates as (
      select @startdate as thedate
      union all
      select dateadd(day, 1, thedate)
      from dates
      where thedate < @enddate
     )
select driver.thedate, driver.ClientIP,
       coalesce(fwl.FWBytes, 0) + coalesce(pl.PLBytes, 0) as TotalBytes
from (select d.thedate, fwl.ClientIP
      from dates d cross join
           (select distinct ClientIP from FirewallLog) fwl
     ) driver left outer join
     (select cast(fwl.logtime as date) as thedate,
             SUM(fwl.BytesSent + fwl.BytesRec) as FWBytes
      from FirewallLog fwl
      group by cast(fwl.logtime as date)
     ) fwl
     on driver.thedate = fwl.thedate and driver.clientIP = fwl.ClientIP left outer join
     (select cast(pl.logtime as date) as thedate,
             SUM(pl.BytesSent + pl.BytesRec) as PLBytes
      from ProxyLog pl
      group by cast(pl.logtime as date)
     ) pl
     on driver.thedate = pl.thedate and driver.ClientIP = pl.ClientIP

This uses a driver table that generates all the combinations of IP and date, which it then uses for joining to the summarized table. This formulation assumes that the "FirewallLog" contains all the "ClientIp"s of interest.

This also breaks out the two values, in case you also want to include them (to see which is contributing more bytes to the total, for instance).

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

I would recommend creating a Dates Lookup table if that is an option. Create the table once and then you can use it as often as needed. If not, you'll need to look into creating a Recursive CTE to act as the Dates table (easy enough -- look on stackoverflow for examples). 

Select d.date, 
    results.ClientIp
    Sum(results.bytes) 
From YourDateLookupTable d
    Left Join (
        Select ClientIp, logtime, BytesSent + BytesRec bytes From FirewallLog
        Union All
        Select ClientIp, logtime, BytesSent + BytesRec bytes From ProxyLog
    ) results On d.date = results.logtime
Group By d.date, 
    results.ClientIp

This assumes the logtime and date data types are the same. If logtime is a date time, you'll need to convert it to a date.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

First, create a Calendar table. One that has, at the very least, an id column and a calendar_date column, and fill it with dates covering every day of every year you can ever be interested in . (You'll find that you'll add flags for weekends, bankholidays and all sorts of other useful meta-data about dates.)

Then you can LEFT JOIN on to that table, after combining your two tables with a UNION.

SELECT
  CALENDAR.calendar_date,
  JOINT_LOG.ClientIP,
  ISNULL(SUM(JOINT_LOG.BytesSent + JOINT_LOG.BytesRec), 0)  AS TotalBytes
FROM
  CALENDAR
LEFT JOIN
(
  SELECT LogTime, ClientIP, BytesSent, BytesRec FROM FirewallLog
  UNION ALL
  SELECT LogTime, ClientIP, BytesSent, BytesRec FROM ProxyLog
)
  AS JOINT_LOG
    ON  JOINT_LOG.LogTime >= CALENDAR.calendar_date
    AND JOINT_LOG.LogTime <  CALENDAR.calendar_date+1
WHERE
      CALENDAR.calendar_date >= @start_date
  AND CALENDAR.calendar_date <  @cease_date
GROUP BY
  CALENDAR.calendar_date,
  JOINT_LOG.ClientIP

SQL Server is very good at optimising this type of UNION ALL query. Assuming that you have appropriate indexes.

Upvotes: 3

Related Questions