add in query

hi

i have existing query ..now i want to add one more join in this with some condition in where clause.

my need is if emloyee's not present for last 4 week .so his working hour is zero..so i want to list those employeee who not worked for last 4 weeks.

see the bold one.

SELECT
HR.ClientID,
CD.Email,
CD.Surname,
CD.FirstNames,
CD.HomeTel,
CD.WorkTel,
CD.MobileTel,
CD.PreferredName,
HR.EmployeeNumber,
HR.EmploymentType,
HR.Status,
HR.Jobtitle,
HR.LocationID,
HR.DivisionID,
HR.IsPayrollEmployee,
HR.StartDate,

CASE
WHEN PIP.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsPrevented',
HR.PayrollCompanyID,
HR.ProbationEndDate,
IsNull(HR.ProbationStatus, 0) AS 'ProbationStatus',
CD.UserTypeID,
CLT.Location,
CLT.Division,
IsNull((SELECT COUNT(*)
FROM t_TerminationDetails AS TD INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_01 ON (PD_01.Frequency = 'M' AND TD.TerminationDate >= PD_01.StartDate AND PD_01.PayPeriod = 1)
INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_02 ON (PD_02.Frequency = 'M' AND TD.TerminationDate <= PD_02.StartDate AND PD_02.PayPeriod = 12)
WHERE TD.ClientID = HR.ClientID), 0) AS 'HasBeenTerminated',
IsNull(TD.Reemploy, 0) AS 'Reemploy',

NOP.NumberOfPayruns,
CASE
WHEN CAR.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsDriver',
HR.HasBeenRehired,
CD.DateCreated,
ISNULL(BD.AccountNumber, 0) AS 'AccountNumber',

(SELECT TOP 1 GD.CompanyID FROM t_BenefitCompanyEmployee BCE INNER JOIN t_GroupDetails GD ON BCE.CompanyID = GD.CompanyID WHERE (BCE.ClientID = CD.ClientID)) as 'PAYECompany',
JT.Name As 'JobTitleName',
JT.IsContractor As 'IsContractor',
CASE WHEN (IsNull(BD.PaymentMethod,0) = 1 AND Len(IsNull(BD.SortCode,0)) = 6) OR IsNull(BD.PaymentMethod,0) <> 1 THEN
1
ELSE
0
END AS 'ValidSortCode'

FROM
@.Client_Table AS CLT INNER JOIN (t_HRDetails AS HR INNER JOIN t_ClientDetails AS CD ON (HR.ClientID = CD.ClientID)) ON (CLT.ClientID = HR.ClientID)
LEFT JOIN #EmployeesByCompany AS EBC ON (CLT.ClientID = EBC.ClientID)
LEFT JOIN t_PayrollIsPrevented AS PIP ON (CLT.ClientID = PIP.ClientID)

LEFT JOIN #NumberOFPayruns_Table AS NOP ON (CLT.ClientID = NOP.ClientID)
LEFT JOIN t_CAR_Driver AS CAR ON (HR.ClientID = CAR.ClientID)
LEFT JOIN (SELECT ClientID, AccountNumber, SortCode, PaymentMethod FROM t_BankDetails WHERE Status = 1 AND AccountNumber <> '') AS BD ON BD.ClientID = HR.ClientID
LEFT JOIN t_JobTitle JT ON HR.Jobtitle = JT.JobTitleID
LEFT JOIN t_TerminationDetails TD ON HR.ClientID = TD.CLientID
Left join t_shiftmembers2 SM on HR.ClientID = SM.CLientID
WHERE

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames

i tried ..but not sure this is correct way.

Change your query as follow as ,

Left join t_shiftmembers2 SM on

HR.ClientID = SM.CLientID

AND (@.NOTWORKED = 0

OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())

AND sum(SM.totalacthrs)= 0

)

)

WHERE

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )

AND

(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')

AND

(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )

AND

(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )

AND

(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )

AND

(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )

AND

(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )

ORDER BY

CD.Surname, CD.FirstNames

|||

First, I see in your query "... IsNull((SELECT COUNT(*) FROM t_TerminationDetails AS TD INNER JOIN ..." but any time select count(*) from any_table return at least 0 if the table has no rows, and you user isnull function ...

Beeing as so complicate selct i think you break it using views to simplify things.

I see @.Client_Table and i understand it is a temporary table variable but in

" (@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames"

@.NOTWORKED and etc are parameters of a stored procedure, isn't it ?

|||

main thanx for reply..

by seeing your query ..i can add all my condition in left join itself..no need to add in where clause...my sum(actualhours) will not give any error.

|||

The point is when you use the outer join, if there is a filter (where) condition then move them from the where clause to corresponding on clause.

|||

mani i run your query i m getting followin g error

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

|||

Ohh yes…How I missed that.. may be the following query might work..

Code Snippet

Left join (Select ClientID,isnull(sum(SM.totalacthrs),0) totalhrs from t_shiftmembers2

Where SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())

Group By ClientID) as SM on

HR.ClientID = SM.CLientID

Where

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )

AND

(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')

AND

(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )

AND

(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )

AND

(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )

AND

(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )

AND

(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )

AND

(@.NOTWORKED = 0 OR (totalhrs = 0))

|||

thanx for reply

mani can u tell me dateadd(week,-4,getdate())

-4 week means 28 days

or some thing else?

|||in this query i think u r not checking this condition sum(totalacthrs) = 0|||

sorry my mistake i didn't see your whole code..

i want to convert getdate like this 2007-09-26 00:00:00:00

mean i want min ,sec all in zero