Help with SP syntax

I have the stored proc. below and I'm passing two
parameters. What I'm trying to do is if either one of
the parameters is equal to "All", then change the value
of the paramter to an empty string or set another
variable to an empty string. SQL doesn't like the code I
have below. Please help.
CREATE PROCEDURE GetUSFSUsers
(
@.Role nvarchar(100),
@.Unit nvarchar(20)
)
AS
Declare @.Role2 nvarchar(100)
Declare @.Unit2 nvarchar(20)
If @.Role = 'All'
@.Role2 = ''
Else
@.Role2 = @.Role
If @.Unit = 'All'
@.Unit2 = ''
Else
@.Unit2 = @.UnitYou can change it to
if @.role = 'all'
set @.role2 = ''
else
set @.role2 = @.role
if @.unit = 'all'
set @.unit2 = ''
else
set @.unit2 = @.unit
HTH
Ray Higdon MCSE, MCDBA, CCNA
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help with ServerAgent Job syntax

Hi,

Hopefully someone can help me. I'm having difficulty with the syntax to delete a record from 4 joined tables when creating a job.

I have an 'Applicants' table linked to four other tables 'Courses', 'EmploymentHistory', 'Qualifications', and 'References' using the field 'ApplicantID'.

I want to create a job to delete all the records where the Finalised field = '0' and the record was created more than 3 days ago.

The syntax I have been using on just one of the joined tables to start with doesn't delete from the joined table:

USE OnlineApplications
DELETE Applicants
FROM Applicants
INNER JOIN Courses
ON Applicants.ApplicantID = Courses.ApplicantID
WHERE Finalised = 0 AND Created < DATEADD(d, 3, Created)

How can I delete the records from the other four tables?

ThanksHave you defined foreign key relationships?|||Darnit Poots, you beat me to it again!
I was going to ask for the joins between the tables :'(|||joins <> relationships ;)|||*shifty look*
I knew that ;)|||Have you defined foreign key relationships?

Nope, makes sense to do that I suppose. :o

For the projects I do, I merely create the tables then use the tables to store the data, haven't needed to create relationships in the past, bad development I know.

But I haven't really looked into the features of SQL Server yet.

I take it the syntax should work then if I create relationships?
Can I do this by creating a diagram?
Then I take it I need to set the joins to cascade to delete from other tables?|||Can I do this by creating a diagram?Yeah - but script it out.
Basic sample code. Check BoL for more options.

IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N't_name') BEGIN
DROP TABLE t_name
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'other_t_name') BEGIN
DROP TABLE other_t_name
END
CREATE TABLE dbo.other_t_name
(
c_name INT NOT NULL CONSTRAINT df_other_t_name_c_name DEFAULT 0
, CONSTRAINT pk_other_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_other_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_other_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
)
GO
CREATE TABLE dbo.t_name
(
c_name INT NOT NULL CONSTRAINT df_t_name_c_name DEFAULT 0
, CONSTRAINT pk_t_name PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ix_t_name_c_name_u_nc UNIQUE NONCLUSTERED (c_name) WITH (FILLFACTOR = 80)
, CONSTRAINT ck_t_name_c_name CHECK (c_name BETWEEN 1 AND 10)
, CONSTRAINT fk_t_name_other_t_name FOREIGN KEY (c_name) REFERENCES other_t_name (c_name) ON DELETE CASCADE
)
GO

HTH

Help with script and variables

Hi If I try to run the code below I get the following errors

Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'view'.
Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near '@.month1'.

I am not sure why it does not like the keyword view ? also I am trying to use the variables in the column name of the create table but again it does not like this.

-- declare all variables!

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money,
@.month2 money,
@.month3 money
)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

For starters, the CREATE VIEW statement has to be the first statement in a batch so it can't be used in this way.

You could dynamically create your CREATE VIEW statement then use sp_executesql to execute the command.

Chris

|||

Hi thanks for the reply

I am quite new to this what do you mean by dynamically create the CREATE VIEW statement ?

|||

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate -- you cant have variabl inside view
and date_and_time <= @.enddate1 -- you cant have variabl inside view

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money, -- Remove @.
@.month2 money, -- Remove @.
@.month3 money -- Remove @.

)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

and also tell us what u are intended to do... there are couple of wrong sysntax in the script... you can not use variable inside a view definition...

Madhu

|||

Hi

First of I am trying to create a view of data between a specific date range. This is the last three months. So if it was to run today then the view would contain data from 1st nov 2006 to 28th feb 2007.

When it runs on the 1st Apr the view would contain data from 1st Dec to 31st march and so on. This is why I am trying to drop the view first then create it. I am then running a query on the view and inserting the results of this into the table i create in the script.

Secondly as I am working with a rolling 3 months of data I need to drop the table I insert the data into an create it again with the correct column headings ie NOV,DEC,JAN. This is why I have the variables in the create view and create table statements.

If this is not possible is their an other way of doing this ?

Hope that makes sense

|||

I wouldn't bother creating a View for temporary purposes, you can use the SELECT statement as is to directly insert data into tables.

Try the code below to drop and create your table - I see no need for a cursor in the scenario you have presented.

Chris

DECLARE @.startdate DATETIME

DECLARE @.enddate DATETIME

DECLARE @.enddate1 DATETIME

DECLARE @.month1 NVARCHAR(10)

DECLARE @.month2 NVARCHAR(10)

DECLARE @.month3 NVARCHAR(10)

DECLARE @.TableName NVARCHAR(100)

DECLARE @.SQLString NVARCHAR(4000)

SELECT @.startdate = dbo.removetime(DATEADD(month, -3, getdate())),

@.enddate = dbo.removetime(DATEADD(month, -2, getdate())-1),

@.enddate1 = dbo.removetime(DATEADD(month, 0, getdate())-1),

@.month1 = left(dbo.removetime(DATEADD(month, -3, getdate())),5),

@.month2 = left( dbo.removetime(DATEADD(month, -2, getdate())-1),4),

@.month3 = left(dbo.removetime(DATEADD(month, 0, getdate())-1),4)

--The name of the new table

SELECT @.TableName = N'Temp_Table'

--Build the string to drop the table

SET @.SQLString =

N'IF OBJECT_ID(' + QUOTENAME(@.TableName, '''') + ') IS NOT NULL DROP TABLE [' + @.TableName + '];'

--Build the string to create the table

SET @.SQLString = @.SQLString +

N'CREATE TABLE [' + @.TableName + ']

(

[account_no] int,

[account_holder_surname] varchar(80),

[account_holder_forename] varchar(80),

[' + @.month1 + '] money,

[' + @.month2 + '] money,

[' + @.month3 + '] money

)'

--Execute the statements

EXEC(@.SQLString)

--Prove that the new table exists

--EXEC sp_help 'Temp_Table'

/*

--Don't create a 'temporary' view - just use the query directly like this...

INSERT INTO.... / UPDATE etc...

select column1,column2

from some_table

where date_and_time >= @.startdate

and date_and_time <= @.enddate1

*/

|||

Hi Chris

Thanks so much. It was the syntax around the create table I could not get my head around !

Thanks

Help with reading datetime with DATEPART

I was hoping someone could help me with the sql syntax in trying to return the date from a datetime value. I'm trying to get the month and day and year from a datetime value in the database but I keep getting a token error. This is the code I'm using to try to read the date, from everything I've read for sql, it should work but it doesn't.

Dim sql As String = "SELECT * FROM People WHERE DATEPART(month, dtime) = '" & _
DateTime.Month & "' & DATEPART(year, dtime) = '" & DateTime.Year & '"

Dim Sqlreader As SqlCeDataReader = cmd.ExecuteReader

The error I get is:
There was an error parsing the query. [ Token line number = 1,Token line offset = 82,Token in error = = ]

It doesn't seem to recognize the second DATEPART search and the = sign is a syntax error.

What am I doing wrong here?

in the "& DATEPART(year, dtime)" part, replace "&" with "and"

regards

|||Hmmm, I tried the '&' symbol and also tried 'AND' but not a lowercase 'and'.
Thanks.

crt

help with query syntax

Hi all, I have been fighting with this query and would like some advice. Please consider the following tables;

prod_table
widget_number
shift
date
production_time (in minutes)
down_table
rec_id (ident key)
down_shift
down_date
down_minutes

Prod_table (data)

widget_number shift date production_time
0001 1 08/02/06 5.00
0002 1 08/02/06 10.00
0003 1 08/02/06 7.00
0004 2 08/02/06 5.00
0005 2 07/31/06 3.00
Down_table (data)
rec_id down_shift down_date down_minutes
1 1 08/02/06 3.00
2 1 08/02/06 20.00
3 2 07/31/06 10.00
I would like to combine the production times and down times into one summary where the down time is in the same date and shift as the production time.
As you can see in my results below, I can group them correctly, but the down totals obviously repeat for each match. Is there any way of getting to the "Desired results"?
My Results
widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 23.00
0003 1 08/02/06 7.00 23.00
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

Desired results
Widget_number shift date production_time down_time
0001 1 08/02/06 5.00 23.00
0002 1 08/02/06 10.00 NULL
0003 1 08/02/06 7.00 NULL
0004 2 08/02/06 5.00 0.00
0005 2 07/31/06 3.00 10.00

thank you in advance.

Try this:

UPDATE <your result table> SET down_time = null
FROM (SELECT min(widget_number) as widget_number, shift, date, down_time
FROM <your result table>
group by shift, date, down_time) a left join <your result table> b
on a.widget_number = b.widget_number
WHERE b.widget_number is null

|||I dont have an actual result table. This is a SQL query returning the result set to a report.|||What version of SQL Server are you using? The result is slightly easier to achieve in SQL Server 2005. But this is really a reporting / formatting application so you may be better off doing it in the client side (avoiding repeating groups for instance).|||I am currently using SQL 2000. I will be moving to 2005 soon, but I need this before then. I am using reporting services for SQL , so I really dont see how I can do it client side. Thats where I run into my issue.|||If you have access to the report, then change the datasource of the report, add the update statement in. Otherwise, nothing you can do.|||Why do you need to update anything? There is no table that contains the end result. This is just a read-only operation.|||I don't know about the capabilities of Reporting Services. I have used Crystal Reports in the past and there is an option to suppress repeating values in a column. And that will achieve what you want. So you might want to post this question in the Reporting Services forum since the experts for that product do not participate in this forum.|||Thanks for the response. I just wanted to see if there was a way to do it via T-SQL. I did not think so, but it was worth a shot. Thanks for your time.|||

Seems like you gave up too soon. The Update method will work if you put your query in a stored procedure and use a table variable.

Also, it seems like if you created a Derived table that joined the Downtime with the Min Widget for the shift you could then use a CASE to supress the downtime on the additional rows.

help with my UPDATE query

I'm receiving this error: Incorrect syntax near the keyword 'SET'

CODE:

Code Snippet

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
UPDATE dbo.pnpcart_Customer_Details
SET dbo.pnpcart_Customer_Details.Prefix = @.Prefix,
SET dbo.pnpcart_Customer_Details.FirstName = @.FirstName,
SET dbo.pnpcart_Customer_Details.MiddleName = @.MiddleName,
SET dbo.pnpcart_Customer_Details.LastName = @.LastName,
SET dbo.pnpcart_Customer_Details.Address = @.Address,
SET dbo.pnpcart_Customer_Details.City = @.City,
SET dbo.pnpcart_Customer_Details.State = @.State,
SET dbo.pnpcart_Customer_Details.Zip = @.Zip,
SET dbo.pnpcart_Customer_Details.HomePhone = @.HomePhone,
SET dbo.pnpcart_Customer_Details.CellPhone = @.CellPhone,
SET dbo.pnpcart_Customer_Details.Email = @.Email
END

Thanks for the help in advanced!

-Thanks,
Rich

Use the following query...(are you forget your where clause, the current query will update all the records in the table)

UPDATE dbo.pnpcart_Customer_Details
SET Prefix = @.Prefix,
FirstName = @.FirstName,
MiddleName = @.MiddleName,
LastName = @.LastName,
Address = @.Address,
City = @.City,
State = @.State,
Zip = @.Zip,
HomePhone = @.HomePhone,
CellPhone = @.CellPhone,
Email = @.Email

Where (logical expression)

|||GREAT! That works perfectly for now. I'm going to have to revamp my code to work with a JOIN along with asp.net grid control.

Thanks,
Rich

help with LIKE syntax...

HI All,

I am writing some code where i want to get a list of all the company names that start with say, M...

here is what i have:

Dim M_company, companyID_M
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )

but this is only returning the first company in the list that starts with M. Can anybody help with getting it to return more than one value?

thanks,
Leissa1. this is a 'classic' ASP question. this is an ASP.NET forum.
2. that code is doing exactly what I'd expect it to
3. you've neglected to loop through the returned recordset. are you new at this by any chance?|||hmm...yes i am... how'd you guess? :)

so i need to loop something to make it read all the records? any help on how i can do this?|||couple of things :

1. take a decent beginner's tutorial. try www.asp101.com, www.aspin.com, www.4guysfromrolla.com - it'll fill you in on more than a simple forum post.
2. use a classic ASP forum - the ASP.NET snobs here will either ignore your question or tell you you're behind the times (they're wrong, but hey). try www.aspmessageboard.com - you'll usually find me there answering questions.

it's like this

While not recordset.EOF
 ' do stuff here
recordset.moveNext()
Wend

|||Thanks for your help in pointing me in the right direction... :)

i am using the while not statement like this:

Dim M_company, companyID_M, allRecords
' create a recordset
Set allRecords = Server.CreateObject("ADODB.Recordset")
While NOT allRecords.EOF
Set M_company = adoCon.Execute("SELECT company FROM tbl_exib WHERE company LIKE 'm%'")
companyID_M = M_company( 0 )
allRecords.MoveNext
WEnd

however i get this error:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/spiritxmas/displayExhibList_M.asp, line 17

Any quick fix? or should i just start over with the tutorials...

thanks,

Leissa

help with joining on an index

I'm trying to speed up a query by joining on a known index. However, I've
forgotten the syntax.
Could someone please help me?
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
Thanks a million,
PatrickYou cannot join to an index. You can use an index hint, but in my
experience they are generally not very effective.
JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
Can you share more information about your situation and problem?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
> I'm trying to speed up a query by joining on a known index. However, I've
> forgotten the syntax.
> Could someone please help me?
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) <-- here!!
> Thanks a million,
> Patrick
>|||Hi Adam,
Thanks for responding.
I'm just getting the sum of sales per doctor for either a particular
department or all departments.
When going to product level from an order, many record scans take place; so
i'm trying to speed things up. I have an index called Cases_Doctor that I
want to explicitly use in hopes of making things go quicker. I don't expect
anyone to do my job, but here is my query:
SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate =@.PriorEndingDate
,
C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID,
Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
@.EndingDate THEN CP.Quantity ELSE 0.00 END),
PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
@.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00 END),
PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN @.PriorBeginningDate
AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
FROM dim_Doctors D WITH(NOLOCK)
JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
COALESCE(CP.Prod_Fac, '1'))
JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
COALESCE(P.ProductTypeID, '1'))
WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
COALESCE(C.OriginFacility, '1'))
AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
COALESCE(D.SalesPersonID, '1'))
AND C.Type IN(0,1)
GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
D.WorkPhone, D.SalesPersonID
If anything jumps out as a definite no-no. Please let me know.
Thanks again for your response,
Patrick
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
> You cannot join to an index. You can use an index hint, but in my
> experience they are generally not very effective.
> JOIN Cases C (NOLOCK INDEX=Doctor) ON D.DoctorID = C.DoctorID
> Can you share more information about your situation and problem?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "news.microsoft.com" <Patrick@.hotmail.com> wrote in message
> news:%232lzQsF8FHA.2600@.tk2msftngp13.phx.gbl...
>|||Hey Adam,
You're exactly right (I'm starting to learn thats more than likely the
case :))
When specifying an index the query took over 2 minutes.. without it took 55
secs.
A total of 6 mons worth of sales for about 16 thousand customers will take
some time,
but I'm still hoping to get it quicker.
Patrick
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||SQL Server will tend to not use a non-clustered index if the lookup into the
cluster is going to be more expensive than simply doing a table scan (which
is probably what's happening in your case.) It looks like you may be able
to create a covering non-clustered index for that query.
Please read the following article, and post back here if you have any
questions:
http://www.sql-server-performance.c...ing_indexes.asp
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"news.microsoft.com" <Patrick@.hotmail.com> wrote in message
news:u7OHBAG8FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi Adam,
> Thanks for responding.
> I'm just getting the sum of sales per doctor for either a particular
> department or all departments.
> When going to product level from an order, many record scans take place;
> so i'm trying to speed things up. I have an index called Cases_Doctor that
> I want to explicitly use in hopes of making things go quicker. I don't
> expect anyone to do my job, but here is my query:
> SELECT BeginningDate = @.BeginningDate, EndingDate = @.EndingDate,
> PriorBeginningDate = @.PriorBeginningDate, PriorEndingDate
> =@.PriorEndingDate ,
> C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID,
> Dollars = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.TotalCharge ELSE 0.00 END),
> Units = SUM(CASE WHEN C.DateInvoiced BETWEEN @.BeginningDate AND
> @.EndingDate THEN CP.Quantity ELSE 0.00 END),
> PriorDollars = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.TotalCharge ELSE 0.00
> END),
> PriorUnits = SUM(CASE WHEN C.DateInvoiced BETWEEN
> @.PriorBeginningDate AND @.PriorEndingDate THEN CP.Quantity ELSE 0.00 END)
> FROM dim_Doctors D WITH(NOLOCK)
> JOIN Cases C WITH(NOLOCK) ON D.DoctorID = INDEX(Cases_Doctor) AND
> C.DateInvoiced BETWEEN @.PriorBeginningDate AND @.EndingDate
> JOIN CaseProducts CP WITH(NOLOCK) ON CP.CaseID = C.CaseID AND
> COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
> COALESCE(CP.Prod_Fac, '1'))
> JOIN Products P WITH(NOLOCK) ON P.ProductID = CP.ProductID AND
> COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
> COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
> COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
> COALESCE(D.SalesPersonID, '1'))
> AND C.Type IN(0,1)
> GROUP BY C.OriginFacility, CP.Prod_Fac, C.DoctorID, D.DoctorDisplayName,
> D.WorkPhone, D.SalesPersonID
> If anything jumps out as a definite no-no. Please let me know.
> Thanks again for your response,
> Patrick
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23fkSS4F8FHA.604@.TK2MSFTNGP10.phx.gbl...
>|||On Wed, 23 Nov 2005 13:59:20 -0500, news.microsoft.com wrote:
(snip)
>If anything jumps out as a definite no-no. Please let me know.
Hi Patrick,
These four comparisons are almost guaranteed performance-killers:

>COALESCE(CP.Prod_Fac, '1') = COALESCE(@.ProductionFacility,
>COALESCE(CP.Prod_Fac, '1'))
(...)
>COALESCE(P.ProductTypeID, '1') = COALESCE(@.ProductTypeID,
>COALESCE(P.ProductTypeID, '1'))
> WHERE COALESCE(C.OriginFacility, '1') = COALESCE(@.OriginFacility,
>COALESCE(C.OriginFacility, '1'))
> AND COALESCE(D.SalesPersonID, '1') = COALESCE(@.SalesPersonID,
>COALESCE(D.SalesPersonID, '1'))
These can only be satisfied by table scan or index scan (maybe a partial
index scan if some of the other filters allow it).
If you want to take maximum advantage of an index on, for instance,
P.ProductTypeID, then make sure that you write the filter as:
P.ProductTypeID = some interesting expression
This allows SQL Server to calculate the expression first, then use the
index to find the matching row(s) directly, without the need to scan
over lots of other rows and determine if they match as well.
I know too little about your tables and your data to be able to tell you
how "some interesting expression" would have to look.
If you need more assistance, then check www.aspfaq.com/5006 first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)