June 14, 2010 at 3:48 pm
Hi everyone.
I've got a question (no surprise there).
I have a query that I would like to search by the StaffID, I would also like to separate out those hits that do not have a staffID attached to it. Can someone help me with this?
The dataset query looks like this:
SELECT Customer.Name, Customer.Phone, Customer.City, SUBSTRING(Customer.Phone, 1, 3)
AS AreaCode, Customer.Zip, Customer.Number,
Staff.Username, Staff.StaffID
FROM InvoicedFeeView INNER JOIN
Customer ON InvoicedFeeView.CustomerID = Customer.CustomerID INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID
WHERE (@Staff = -1 OR ISNULL(Staff.StaffID, '0') = @Staff)
the PARAMETER query looks like this (for now):
SELECT 'openAccounts' AS Username, - 1 AS StaffID
UNION
SELECT Username, StaffID
FROM Staff
WHERE (Department = 'Sales') AND (StaffID NOT IN ('135', '233', '244', '246', '234','229'))
This report returns results, but not the desired results when I choose '-1' as the staffID (OpenAccounts). What should I change to filter out all accounts that have a 'staffid' attached to it when I select '-1' as the parameters? I'm stuck 🙁
June 15, 2010 at 12:39 am
iklektic (6/14/2010)
Hi everyone.I've got a question (no surprise there).
I have a query that I would like to search by the StaffID, I would also like to separate out those hits that do not have a staffID attached to it. Can someone help me with this?
The dataset query looks like this:
SELECT Customer.Name, Customer.Phone, Customer.City, SUBSTRING(Customer.Phone, 1, 3)AS AreaCode, Customer.Zip, Customer.Number,
Staff.Username, Staff.StaffID
FROM InvoicedFeeView INNER JOIN
Customer ON InvoicedFeeView.CustomerID = Customer.CustomerID INNER JOIN
Staff ON Customer.SalesRepStaffID = Staff.StaffID
WHERE (@Staff = -1 OR ISNULL(Staff.StaffID, '0') = @Staff)
the PARAMETER query looks like this (for now):
SELECT 'openAccounts' AS Username, - 1 AS StaffIDUNION
SELECT Username, StaffID
FROM Staff
WHERE (Department = 'Sales') AND (StaffID NOT IN ('135', '233', '244', '246', '234','229'))
This report returns results, but not the desired results when I choose '-1' as the staffID (OpenAccounts). What should I change to filter out all accounts that have a 'staffid' attached to it when I select '-1' as the parameters? I'm stuck 🙁
Below code is working for me.Post table defintion along with sample data.
declare @id int
set @id = 15
select * from sysobjects where (@id = 1 or id = @id)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 15, 2010 at 7:29 am
the inner join to the Staff table is what is limiting the data coming back. But it's hard to test without sample data and table layouts. But try this
SELECT Customer.Name, Customer.Phone, Customer.City,
SUBSTRING(Customer.Phone, 1, 3) AS AreaCode, Customer.Zip,
Customer.Number, Staff.Username, Staff.StaffID
FROM InvoicedFeeView
INNER JOIN Customer
ON InvoicedFeeView.CustomerID = Customer.CustomerID
INNER JOIN Staff
ON (Customer.SalesRepStaffID = Staff.StaffID or @Staff = -1)
WHERE ISNULL(Staff.StaffID, '0') = @Staff
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply