There are many ways to set up a permanent Tally or Numbers table.

Here's one way that inserts all the integers from 0 to 9999.

If you wish to use a permanent...

To ensure that everyone can understand your requirements properly, can you also show your expected results when run against the test data you have supplied.

Also, how do you want to...

I'm sitting here trying to get my head around this, and I don't understand what the benefit of having a nonclustered PK would be. Is it only to enforce the...

Greg,

I set up some test data with 1000000 rows - 1000 rows for each of 1000 distinct values of Company column and investigated the effect of the following 3 indexing...

Greg,

No offense taken. I've also had a few performance issues with correlated subqueries in the past. I was intimating this by this statement in a previous post:

If you have a...

Unfortunately, the 'YYYY-MM-DD' format is not interpreted 100% consistently either though the ISO8601 datetime format 'YYYY-MM-DDTHH:MM:SS' is. For example, you can set...

Todd Young (4/16/2009)

---

Andrew -

I couldn't get the last sample to work but the one you last

posted works great. I'll pop that into my sproc today.

I tossed a...

tony (4/15/2009)

---

Many thanks for that.

Silly me for not realising that I had to use American Date Format when all the entries are in a UK format.....

Adding to that, would it...

Wow, a WHILE loop just to convert a decimal fraction to a string representation of hours and minutes.

I have 2 comments:

1) Formatting should be done on the front end rather...

kokkula (4/16/2009)

---

Hello,

Try this

CREATE TABLE #TestData (

Id int IDENTITY(1, 1) PRIMARY KEY,

StartDate datetime NOT NULL,

EndDate datetime NOT NULL,

StartTime datetime NOT NULL,

EndTime datetime NOT NULL

)

INSERT INTO #TestData (StartDate, EndDate, StartTime, EndTime)

SELECT...

New Commer (4/15/2009)

---

Dear Andrew

i am bit confused, with declaration of

DECLARE @StartTime datetime

DECLARE @EndTime datetime

SELECT @StartTime = '10:00', @EndTime = '14:30'

I am unable to modify without this declaration. the values...

Todd,

Did you try my correlated subquery solution? I believe it gives the results you require. If you have a large number of rows in your table and performance is an...

I've tried to simplify the situation to see more clearly what is going on.

CREATE TABLE #MSFO (StorageFee numeric(11, 2))

GO

INSERT INTO #MSFO VALUES(1.0)

GO

CREATE TABLE #FS (StorageMonthFee numeric(11, 2))

GO

INSERT...

This query appears to reproduce your expected results.

It uses a Tally / Numbers table with name Tally that includes the value 0 (zero).

There is a pre-query to retrieve the...

If you're sure you don't need to consider holidays and weekends then the following will give the duration in hours, only counting the time interval (@StartTime, @EndTime) on each day.

/*...