Query the data between two tables

Question

Need help for query the data between two tables

Table 1: Time sheet

P.ID      P.Name EmpID HoursSpend DateTime

c12234  Test      25        4                06/12/2013

c12234  Test      25        7                06/13/2013

c12234  Test      25        8                06/15/2013

c12234  Test      5          3                06/21/2013

c12234  Test      2          5                07/15/2013

c12234  Test      25        4                07/21/2013

Table 2: cost table

EmpID  FromDate       ToDate         Rate

25         05/01/2013    06/30/2013    250

2         04/01/2013    05/31/2013      150

25         07/01/2013     09/30/2013    300 

Output

P.ID      P.Name EmpID HoursSpend DateTime       Rate   Total (HoursSond x Rate)

c12234  Test      25        4                06/12/2013    250     1000 (4*250)

c12234  Test      25        7                06/13/2013    250      1750

c12234  Test      25        8                06/15/2013    250       2000

c12234  Test      25        4              07/21/2013     300        1200

c12234  Test      2          5              07/15/2013    150           750

===========================================     

Total                           28                                                  6700

============================================

Here EmpID =2 don't have rate in the cost table on july month should be pick from last entry from cost table.

Answer 1

Hello Gopal,

Try this query:

SELECT *
FROM time
     INNER JOIN
     cost
         ON time.EmpID = cost.EmpID
            AND time.DateTime BETWEEN cost.FromDate AND cost.ToDate

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hi Gopal,

According to your description, it seems that the output needn’t include the row when EmpID=2. Because the DateTime for it in Table1 doesn’t included between FromDate column and ToDate column. After testing the issue in my environment, we can refer to the query like below to achieve your requirement:

SELECT time.*,cost.EmpID,cost.Rate,(time.HoursSpend * cost.Rate)as [Total (HoursSond x Rate)]
FROM [Time sheet] as time
     INNER JOIN
     [cost table]as cost
         ON time.EmpID = cost.EmpID
            AND time.DateTime BETWEEN cost.FromDate AND cost.ToDate

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

---

Katherine Xiong
TechNet Community Support