Hi again,

This version uses a Holiday table, which does not need to include weekends. The presence of a row with a particular date in this table indicates a national holiday...

That would explain it. My Calendar table contains sequential dates and row with dayType = 'W' indicates a working day. Sorry if I didn't make this clear. There are many...

You need some form of Tally table (or Numbers table). These can be permanent tables or can be generated on the fly. The Tally table I'm using below has the...

More information please!

Do you want to select all the records in some table or view where the value of a datetime column is between two dates, or do you want...

Only thing I don't have working now is the createdate can be created on the weekend or after 5:30 PM. If this happens the duetime starts at 8:30 am...

Why are you using the vchClientSubSessionID column in the CONVERT expression when you used ClientID in the CASE WHEN boolean expressions?

Also ISNUMERIC isn't the best way to validate an integer...

I think this fulfills OP's amended requirements:

DECLARE @InputDate datetime

DECLARE @StartDate datetime

SELECT @InputDate = DATEADD(day, DATEDIFF(day, '17530101', '20090316 13:15'), '17530101')

SELECT @StartDate = DATEADD(DAY, DATEDIFF(day, '17530101', @InputDate) / 7 * 7, '17530101')

SELECT...

Using the flavour of Calendar table defined in the previous post by Bob Hovious, the following code will return the due date/time given the create date/time and the due time...

Are your business hours the same whatever the day?

i.e. is there any such thing as a half-day holiday?

Given that you need to take account of weekends and holidays, you...

Assuming the data types are like my @testData table below:

DECLARE @testData TABLE (

ID int,

Startday int,

Starttime varchar(5),

Endday int,

Endtime varchar(5)

)

/* Test data */

INSERT @testData (ID, Startday, Starttime, Endday,...

are you going to store time alone or data and time?

if you are going to store date and time use 'DATETIME' data type.

if you are going to store hh:mm only...

If you had used a temporary mapping table then it would be easy to find the extensions that don't exist in the Person table.

So based on temporary table #ExtCityMap in...

Why not shred the text nodes into separate rows instead?

DECLARE @xml xml

SELECT @xml = '<Results>

<Group><XmlNode>A1<x>extra</x></XmlNode><XmlNode>B1</XmlNode></Group>

<Group><XmlNode>A2</XmlNode><XmlNode>B2</XmlNode></Group>

</Results>'

SELECT X.N.value('.', 'varchar(max)')

FROM @xml.nodes('//XmlNode//text()') AS X(N)

If you do need to concatenate the shredded rows back into...

Yes, the text datatype can be converted to the xml data type in SQL Server 2005.

You could potentially use either the pre-2005 OPENXML method (together with sp_xml_preparedocument), or use XQuery...

You haven't exactly made your requirements clear, but what I think you are trying to do is to allocate rows in your STrack table to a data bin ("bucket") based...