sir,i have
employee table which have columns
Empid Empname Empaddress Salary
1 XYZ pppp 10000
2 ABC aaa 5000
3 GHI ooo 7000
4 PQR iii 8000
OT table contains
OTID Empid MonthStartDate MonthEndDate TotalHrs
1 1 2016-03-01 2016-03-31 5
2 2 2016-03-01 2016-03-31 7
3 3 2016-04-01 2016-04-30 15
4 1 2016-04-01 2016-04-30 3
i need single query that returns employee empid ,empname ,TotalHrs from [employee] And [OT] table.
(employee hows TotalHrs in [OT] table for MonthStartDate= '2016-04-01' and MonthEndDate='2016-04-30' )
Also Records
employee hows TotalHrs in [OT] table for MonthStartDate= '2016-03-01' and MonthEndDate='2016-03-31' but how not exists for MonthStartDate= '2016-04-01' and MonthEndDate='2016-04-30' in upper result
i write query
SELECT E.EmpID,E.EmpName,ET.TotalHrs
FROM empinfo AS E
CROSS APPLY
(
SELECT E.EmpID,T.TotalHrs
FROM OT AS T
WHERE T.EmplID =E.EmpID AND
MonthStartDate= '2016-04-01' and MonthEndDate='2016-04-30'
) AS ET
please help
