sql - Summing up columns from two different tables -

i have 2 different tables firewalllog , proxylog. there no relation between these 2 tables. have 4 common fields :

logtime     clientip     bytessent     bytesrec

i need calculate total usage of particular clientip each day on period of time (like last month) , display below:

date        totalusage
2/12         125
2/13         145
2/14         0
.               .
.               .
3/11         150
3/12         125

totalusage sum(firewalllog.bytessent + firewalllog.bytesrec) + sum(proxylog.bytessent + proxylog.bytesrec) ip. have show 0 if there no usage (no record) day. need find fastest solution problem. ideas?

first, create calendar table. 1 has, @ least, id column , calendar_date column, , fill dates covering every day of every year can ever interested in . (you'll find you'll add flags weekends, bankholidays , sorts of other useful meta-data dates.)

then can left join on table, after combining 2 tables union.

select   calendar.calendar_date,   joint_log.clientip,   isnull(sum(joint_log.bytessent + joint_log.bytesrec), 0)  totalbytes   calendar left join (   select logtime, clientip, bytessent, bytesrec firewalllog   union   select logtime, clientip, bytessent, bytesrec proxylog )   joint_log     on  joint_log.logtime >= calendar.calendar_date     , joint_log.logtime <  calendar.calendar_date+1       calendar.calendar_date >= @start_date   , calendar.calendar_date <  @cease_date group   calendar.calendar_date,   joint_log.clientip 

sql server @ optimising type of union query. assuming have appropriate indexes.