Need Query

Venkat Govind

i am struck with below problem please help me to resolve the problem.

Step -1: the Employee1 is created(01-01-2015 ) under one of the contractor "A" the employee worked under contractor "A" between (01-01-2015 to 31-01-2015, the employee details is stored in (table 1), here stored name , created date ,contractor ID )

Step -2 :after one month the particular employee1 going to work for another contractor "B" now the employee working under contractor "B " between (02-02-2015 to 28-02-2015) to until now ,the employee contractor change details stored in (table 3) here stored current contractor name, Fromdate(01-01-2015) , Todate(31-01-2015),changing Contractor Name(Contractor B) ,fromdate(01-02-2015) and simultaneously table1 created date is update(02-02-2015) for employee1 ) ,

Note :each contractor have unique contractor ID , the employee attendance stored attendtl (table3)

now i am search the attendance where between 01-01-2015 to 28-02-2015

i need the result employee1(01-01-2015 to 31-01-2015) attendance under contractor A

and employee1(01-02-2015 to 28-02-2015) attendance under contractor B

How can Achieve this result ?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Venkat Govind11y ago
HI Supreeth J,

thanks for your query ,Here with attached my table details , Normally the Employee register under one of the contractor here i captured the created if any employees not changed any other contractor its not Problem ,below query used for that ,the query result also attached with please find

select v.VendorName,wf.FirstName,tc.Tradecat,wa.WorkforceID,wf.BarcodeId,CONVERT(VARCHAR(20),wa.AttendanceDate,105) as AttendanceDate,wa.InTime,wa.OutTime,CAST( DATEDIFF(Minute,InTime,OutTime)/60 AS VARCHAR(5)) + ':'+ RIGHT('0' + CAST( DATEDIFF(Minute,InTime,OutTime)%60 AS VARCHAR(2)), 2) AS 'WorkingTime' from workforce_attendance as wa inner join mstr_workforce as wf on wf.WorkforceID=wa.WorkforceId inner join mstr_vendor as v on v.VendorCode=wf.VendorCode inner join mstr_tradecategory as tc on tc.TrcatCode=wf.Category Where 1=1 AND InTime != '00:00:00'AND wa.AttendanceDate BETWEEN '09/21/2015' AND '09/21/2015' order by wa.AttendanceDate

i want check from date and todate is there WfComChngDtl

When the Employee was changed Contractor , i saved the person contractor changed details in WfComChngDtl WorkforceID(Like Employee ID),CurrVendorCode(Currently Working Contractor),CurrVendorName(Currently Working Contractor) ,ChngVendorCode(this employee going to work from currentVendor),CrntFromDate(when is created by under one Vendor Date ,First joining Date ),CrntTodate (Working Upto Current Vendor Date),FromDate (Employee Workfrom After Changed other contractor First join Date)

Thanking You
Venkat
Supreeth J11y ago
Venkat Govind11y ago
Hi Supreeth J,

thanks for answer i need one clarification some of employees only to change the Contractor other employees working same contractor until they are relieving

thanks
Venkat
Supreeth J11y ago
table1 ---> EmployeeName, CreatedDate, ContractorID
table3 ---> ContractorName, FromDate, ToDate, ContractorID

SELECT EmployeeName+' ( '+ CAST(C.FromDate AS VARCHAR) + ' TO' + CAST(C.ToDate AS VARCHAR) + ' ) attendance under contractor '+C.ContractorName
FROM table1 a
INNER JOIN table3 C
ON a.ContractorID=C.ContractorID
WHERE C.FromDate >= '2015-01-01' AND C.ToDate <= '2015-02-28'
Shashank Patil11y ago
table1 ---> EmployeeName, CreatedDate, ContractorID
table3 ---> ContractorName, FromDate, ToDate
table2 --> ContractorID, ContractorName --- mapping for table1 and table3

Query:

SELECT C.ContractorName, C.FromDate, C.ToDate
FROM table1 as A INNER JOIN table2 as B ON A.ContractorID == B.ContractorID
INNER JOIN table3 as C ON B.ContractorName == C.ContractorName
WHERE A.EmployeeName="Name to check for attendence" AND C.FromDate >= '01-01-2015' AND C.ToDate <= '28-02-2015'

---------------------------------------------------------
If it helped you, please accept my answer