Here is some sample data for what I am trying to do.

--Create temp table for trees

create table #trees (ID int Identity(1,1), Parentid int NULL, TreeName varchar(20) NOT NULL,RootID int)

--Create tempo...

Thanks for all the input. I think I figured it out. If you have better suggestions, I'm open but I believe it is working.

declare @dt smalldatetime,@day...

mistake in the example. 09/07/2010 should be 10/07/2010

and 11/07/2010 should be 01/07/2011

Thanks again for your help with this. There's one piece I left out

there's actually 2 input dates.

Declare @StaticDate datetime, @Today datetime

Set @StaticDate = '04/07/2010'

Set @today = getdate()

So the 3...

Thanks, that is the basis of what I'm trying to do.

Expanding on your example, if today is 08/05/2011 I would expect a targetdate =09/07/2011

on every 3rd month, the date should...

Great discussion here.

I'm having a similar dilemma. We are building a social component to our website and the requirements are that customers and or external application would be able...

My appologies...

Here's the table structure and current indexes.

CREATE TABLE [dbo].[BookAccess](

[BookAccessID] [uniqueidentifier] NOT NULL,

[SessionID] [uniqueidentifier] NOT NULL,

[UserID] [uniqueidentifier] NOT NULL,

[BookID] [int] NOT NULL,

[ChunkID] [int] NOT NULL,

[Time] [datetime] NOT NULL,

[Type] [int] NOT...

Here's the basis of the SP. Partioned on YearMonth (YYYYMM). Clustered index on (YearMonth,Time)

create stored proc test @startdate datetime, @enddate datetime

as

declare @start varchar(20), @end varchar(20)

set @start = ...

I jumped the gun a bit.

The dynamic sql works fine when I'm only accessing 1 partition.

when expand the date range to include multiple partitions, it does a full scan again.

partioned...

Thanks for all your help.

Using Dynamic SQL works fine but a pain to recode.

Yes, it is partitioned on yearmonth YYYYMM

Time is datetime datatype

If it's parameter sniffing, then how come if I change the condition in the query to this...

"where yearmonth = @fnstart

and time between @start and @end"

this works fine

or

"where yearmonth between @fnstart...

Here's the execution plan when I add (maxdop 1)

insert into #download select userid, bookid, count(*), type from bookaccess b with(nolock) where ...

Here's the execution plan when I run the SP as suggested with assigning local variables for the between.

declare @fnstart int, @fnend int;

set @fnstart = dbo.fn_yearmonth(@start);

set @fnstart = dbo.fn_yearmonth(@end);

select *

from table2

where...

When this runs in management studio as a query, it runs fine. As soon as I create it as a stored proc, is when it hangs.

The plan for the...