when creating an index you can filter the index key to read only N number of characters in field e.g abcdefg and not abcdeftgghhjjjjjjjjjjjjjjjjjjjjjjjjjj. I understand that when the...

Thank you very very much John.

Andy

Thank you, I have this working

It just sounds like it should be much easier to do than it is.

your first suggestion seems much simpler. trying that now...

thanks for you time

Thanks for your help.

The initial proble, was where the two dates in the date diff span two months, the ouput is only considering datediff within the same month.

using your code:

CREATE...

--Where a date diff is calculated within the same month i.e. where the planned.period

--dates used in the date diff are both in June 20-07-2010 (EventType=5) and 10-07-2010 (EventType=11),

--the...

thanks for being patient with me Chris. I'll get you some better raw material post haste...

Sorry about that. How's this?

INSERT INTO [PELVIS].[dbo].[Event]

([DataSource_ID]

,[EventType_ID]

...

Hello there. Thanks for your help.

The Create Table:

CREATE TABLE [dbo].[Event](

[ID] [int] IDENTITY(1,1) NOT NULL,

[DataSource_ID] [int] NOT NULL,

[EventType_ID] [int] NOT NULL,

[User_ID] [int] NOT NULL,

[Period] [datetime] NULL,

[Notes] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Success]...

Its been a while since i posted the question above but I've recently noticed a problem with the code. Utilising the ABS() suggestion above, I came up with the...

ahh yes, you're right. very much appreciated.

thanks

Thanks for link. What skt5000 was fine. I popualted the calendar with his logic up until 01-01-2020, which is fine for my purposes.

Cheers Jim. That's done the trick.

Andy

Is this okay

event table------------

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Event](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Source_ID] [int] NOT NULL,

[EventType_ID] [int] NOT NULL,

[User_ID] [int] NOT NULL,

[Period] [datetime] NULL,

CONSTRAINT [PK_Event] PRIMARY...

thanks for your help guys.

I'm not sure how to re-write the sub query, what functions do I use to return the negative value?