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

Add Column Values To the Bottom Of the Row By Condition

Hi
I am getting some results from stored procedure.
Name Value1 Value2 Value3 Total
North 1 2 3 6
North 4 5 6 15
South 5 6 7 18
Footer (North) 5 7 9 21
Footer(South) 5 6 7 18
I displaying footer for each group and i want to display Total value of
the column for that respective group Name.Is therea way to add all
column value depending up on the condition.
For North add only 2 rows.
Can any one help on this issue.
Thankstry sum in the group
=Sum(field!abc.value, "group1") you can have scope in the sum.
Amarnath
"vamsi" wrote:
> Hi
> I am getting some results from stored procedure.
> Name Value1 Value2 Value3 Total
> North 1 2 3 6
> North 4 5 6 15
> South 5 6 7 18
> Footer (North) 5 7 9 21
> Footer(South) 5 6 7 18
>
> I displaying footer for each group and i want to display Total value of
> the column for that respective group Name.Is therea way to add all
> column value depending up on the condition.
> For North add only 2 rows.
> Can any one help on this issue.
> Thanks
>

Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny