Query doubt.....(Urgent need plz hlp me),

Member 3879881

Hi i am week in query, plz solve my probs..., the following queries,that first query and second query both are working..., Now my question is, What are the fields, i am getting in the two queries those want to show in single query..., Means,in first query ur giving date in that it want to take the year only and check with tblmonthhourdetails.sdate and same time the tblmonthhourdetails empid want to equal with the employeemaster.empid ------------------------------------------------------------------ SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblTimeCardmain.carddate, dbo.tblTimeCardmain.reportingtime, dbo.tblTimeCardmain.leavingtime, dbo.tblSalaryDetails.Whrs, dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid FROM dbo.tblEmployeeMaster INNER JOIN dbo.tblTimeCardmain ON dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid INNER JOIN dbo.tblSalaryDetails ON dbo.tblEmployeeMaster.empId = dbo.tblSalaryDetails.EmpID WHERE dbo.tblTimeCardmain.carddate = '5/2/2007' AND dbo.tblTimeCardmain.app1 = 1 AND dbo.tblTimeCardmain.projectid = 100 ----------------------------------------------------------------------- SELECT SUM(MedicalLeave) AS Ml, SUM(AnnualLeave) AS AL, EmpId FROM dbo.tblMonthHourDetails WHERE (DATEPART(YYYY, SDate) = 2007) GROUP BY EmpId, SDate ----------------------------------------------------------------------- Plz hlp me..., -- modified at 1:05 Thursday 14th June, 2007

Magi

andyharman

Hi Magi Try this:

SELECT EM.empId, EM.empName, TC.carddate, TC.reportingtime,
  TC.leavingtime, SD.Whrs, TC.shift, TC.projectid,
  (SELECT SUM(MedicalLeave) FROM dbo.tblMonthHourDetails MHD
    WHERE MHD.EmpId = EM.empId AND YEAR(MHD.SDate) = 2007) AS ML,
  (SELECT SUM(AnnualLeave) FROM dbo.tblMonthHourDetails MHD
    WHERE MHD.EmpId = EM.empId AND YEAR(MHD.SDate) = 2007) AS AL
FROM dbo.tblEmployeeMaster EM
INNER JOIN dbo.tblTimeCardmain TC
ON EM.empId = TC.empid
INNER JOIN dbo.tblSalaryDetails SD
ON EM.empId = SD.EmpID 
WHERE TC.carddate = '5/2/2007' 
AND TC.app1 = 1
AND TC.projectid = 100

I have used table aliases (EM, TC, etc) to make your query more readable. Correlated sub-queries are used to get the medical and annual leave. Regards Andy

Andy M

Magh_M wrote:

Query doubt.

You have a question, not a doubt.

Member 3879881

Thanks lot..., Its working cool, Keep in touch, Take care,

Magi