Dear Teami'm in need of your help guys.please help me out.1.tblEmployeeEmpid EmpFirstName1 John2 David3 Peter
2. tblEmployeeProjectAllocationDetailsPrjCode PrjType EmpId FromDate ToDate RFE Bench 1 2014-04-21 00:00:00 2014-04-28 00:00:00 LFG Leave 1 2014-02-21 00:00:00 2014-03-05 00:00:00 ABC Production 1 2014-01-01 00:00:00 2014-02-20 00:00:00 XYZ Support 2 2014-04-01 00:00:00 2014-05-31 00:00:00 FGH Production 2 2014-03-01 00:00:00 2014-03-31 00:00:00 TYH Leave 2 2014-02-28 00:00:00 2014-02-28 00:00:00 JYR Leave 2 2014-02-27 00:00:00 2014-02-27 00:00:00 BHR Bench 2 2014-02-21 00:00:00 2014-02-26 00:00:00 HYZ Support 2 2014-01-18 00:00:00 2014-02-20 00:00:00 RIO Bench 2 2013-12-19 00:00:00 2013-02-19 00:00:00 ERO Production 2 2013-02-01 00:00:00 2013-02-18 00:00:00 EIU Leave 3 2014-03-01 00:00:00 2014-04-22 00:00:00 GRE Leave 3 2014-01-06 00:00:00 2014-02-28 00:00:00 WTG bench 3 2013-12-21 00:00:00 2014-01-05 00:00:00 RQO bench 3 2013-12-14 00:00:00 2013-12-20 00:00:00 ERL Leave 3 2013-01-15 00:00:00 2013-12-13 00:00:00 HOO bench 3 2013-01-01 00:00:00 2013-01-14 00:00:00
OutputEmpid PrjCode PrjType FromDate ToDate1 ABC Production 2014-01-01 00:00:00 2014-02-20 00:00:002 HYZ Support 2014-01-18 00:00:00 2014-02-20 00:00:003 GRE Leave 2014-01-06 00:00:00 2014-02-28 00:00:00
Kindly note the following1. have to look for 2014-02-28 00:00:00 (Feb 28 ,2014 ) between fromdate and Todate in allocation table. - we may get more then one record but have to consider the lastest one2. Basially need to find the production projcode for an employee before he falls into bench or leave before 28th feb.*Production Prjcode means prjcode not in Bench or LeaveConditions:3. (if the PrjType is Leave and DateDifference ( day, fromdate,todate) < 180) or (If the PrjType is Bench and DateDifference ( day,fromdate,todate) < 10) ,we need to go through his histoty to find out the production code before he falls into bench.( Please refer ex 1 and 2)4. incase, if there are no prod code found, return them with the first project code itself. ( Refer sample ex ; 3)Thanks in Advance
Query Help please
Posted - 2014-03-31 : 04:33:38
