How to check dates condition from one table to another in SQL

Question

Which way we can use to check and compare the dates from one table to another.

Table : inc

+--------+---------+-----------+-----------+-------------+
| inc_id | cust_id |  item_id  | serv_time |  inc_date   |
+--------+---------+-----------+-----------+-------------+
|      1 | john    | HP        |        40 | 17-Apr-2015 |
|      2 | John    | HP        |        60 | 10-Jan-2016 |
|      3 | Nick    | Cisco     |       120 | 11-Jan-2016 |
|      4 | samanta | EMC       |       180 | 12-Jan-2016 |
|      5 | Kerlee  | Oracle    |        40 | 13-Jan-2016 |
|      6 | Amir    | Microsoft |       300 | 14-Jan-2016 |
|      7 | John    | HP        |       120 | 15-Jan-2016 |
|      8 | samanta | EMC       |        20 | 16-Jan-2016 |
|      9 | Kerlee  | Oracle    |        10 | 2-Feb-2017  |
+--------+---------+-----------+-----------+-------------+

Table: Contract:

+-----------+---------+----------+------------+
|  item_id  | con_id  |  Start   |    End     |
+-----------+---------+----------+------------+
| Dell      | DE2015  | 1/1/2015 | 12/31/2015 |
| HP        | HP2015  | 1/1/2015 | 12/31/2015 |
| Cisco     | CIS2016 | 1/1/2016 | 12/31/2016 |
| EMC       | EMC2016 | 1/1/2016 | 12/31/2016 |
| HP        | HP2016  | 1/1/2016 | 12/31/2016 |
| Oracle    | OR2016  | 1/1/2016 | 12/31/2016 |
| Microsoft | MS2016  | 1/1/2016 | 12/31/2016 |
| Microsoft | MS2017  | 1/1/2017 | 12/31/2017 |
+-----------+---------+----------+------------+

Result:

+-------+---------+---------+--------------+
| Calls | Cust_id | Con_id  | Tot_Ser_Time |
+-------+---------+---------+--------------+
|     2 | John    | HP2016  |          180 |
|     2 | samanta | EMC2016 |          200 |
|     1 | Nick    | CIS2016 |          120 |
|     1 | Amir    | MS2016  |          300 |
|     1 | Oracle  | OR2016  |           40 |
+-------+---------+---------+--------------+

MY Query:

select count(inc_id) as Calls,  inc.cust_id,  contract.con_id,
  sum(inc.serv_time) as tot_serv_time
from  inc inner join contract   on inc.item_id = contract.item_id  
where  inc.inc_date between '2016-01-01' and '2016-12-31'
group by inc.cust_id,  contract.con_id

The result from inc table with filter between 1-jan-2016 to 31-Dec-2016 with count of inc_id based on the items and its contract start and end dates .

Answer 1

select
    Calls = count(*)
  , Cust = i.Cust_id
  , Contract = c.con_id
  , Serv_Time = sum(Serv_Time)
from inc as i
  inner join contract as c
    on i.item_id = c.item_id
    and i.inc_date >= c.[start]
    and i.inc_date <= c.[end]
where c.[start]>='20160101'
group by i.Cust_id, c.con_id 
order by i.Cust_Id, c.con_id

returns:

+-------+---------+----------+-----------+
| Calls |  Cust   | Contract | Serv_Time |
+-------+---------+----------+-----------+
|     1 | Amir    | MS2016   |       300 |
|     2 | John    | HP2016   |       180 |
|     1 | Kerlee  | OR2016   |        40 |
|     1 | Nick    | CIS2016  |       120 |
|     2 | samanta | EMC2016  |       200 |
+-------+---------+----------+-----------+

test setup: http://rextester.com/WSYDL43321

create table inc(
    inc_id int
  , cust_id varchar(16)
  , item_id varchar(16)
  , serv_time int
  , inc_date  date
 );
insert into inc values
 (1,'john','HP', 40 ,'17-Apr-2015')
,(2,'John','HP', 60 ,'10-Jan-2016')
,(3,'Nick','Cisco', 120 ,'11-Jan-2016')
,(4,'samanta','EMC', 180 ,'12-Jan-2016')
,(5,'Kerlee','Oracle', 40 ,'13-Jan-2016')
,(6,'Amir','Microsoft', 300 ,'14-Jan-2016')
,(7,'John','HP', 120 ,'15-Jan-2016')
,(8,'samanta','EMC', 20 ,'16-Jan-2016')
,(9,'Kerlee','Oracle', 10 ,'02-Feb-2017');

create table contract (
    item_id varchar(16) 
  , con_id varchar(16)
  , [Start] date
  , [End] date
);
insert into contract values 
 ('Dell','DE2015','20150101','20151231')
,('HP','HP2015','20150101','20151231')
,('Cisco','CIS2016','20160101','20161231')
,('EMC','EMC2016','20160101','20161231')
,('HP','HP2016','20160101','20161231')
,('Oracle','OR2016','20160101','20161231')
,('Microsoft','MS2016','20160101','20161231')
,('Microsoft','MS2017','20170101','20171231');

Answer 2

If I understand correctly your problem, this query will return the desidered result:

select
  count(*) as Calls,
  inc.cust_id,
  contract.con_id,
  sum(inc.serv_time) as tot_serv_time
from
  inc inner join contract
  on inc.item_id = contract.item_id
     and inc.inc_date between contract.start and contract.end
where
  inc.inc_date between '2016-01-01' and '2016-12-31'
group by
  inc.cust_id,
  contract.con_id

the question is a little vague so you might need some adjustments to this query.