Dave,

No offense taken from your post.

Google up split string and check how many times you see the classic looping solution. Baaaad choice!

So the most frequent result is not necessarily...

Don't mention it. Just glad it works for you.

One other caveat though. Your start dates need to be at time 00:00. If they're not, you should truncate...

Mighty weird. I am baffled.

Perhaps some internal setting having to do with the SQL base date or your client's date/timezone setting?

This will take an expert to offer...

The query as you've written it is the old syntax for OUTER APPLY(not INNER JOIN).

Try something like this:

SELECT A.[OrderNo]

,COUNT(A.[OrderNo])

,SUM(A.[Price])

,A.[Descr]

,B.[OrderNo]

,COUNT(B.[OrderNo])

,SUM(B.[Price])

,B.[Descr]

FROM [Test].[dbo].[TestTbl] A

INNER JOIN...

This will work if your intervals don't exceed 16 days.

DECLARE @t TABLE (dteStartDate DATETIME, dteEndDate DATETIME, txtCode VARCHAR(10))

INSERT INTO @t (dteStartDate,dteEndDate,txtCode)

SELECT '2012-03-29 00:00:00.000','2012-03-29 23:59:00.000','PAIDS'

UNION ALL SELECT '2012-03-01 00:00:00.000','2012-03-02 23:59:00.000','AL'

UNION ALL...

Hi dwain.c,

The second result set is just an example not the exact dates in the table, this what I'm trying to achieve

if the dates are as follows:

StartDate EndDate

2012-01-01 2012-01-05

then...

To xenophilia: How did your name change from ChargeIT? I could have sworn that's what I saw at first. Even the OP referred to it in his response.

To...

I'm with you on not reinventing the wheel. I reuse code all the time.

I was just having a bit of fun when I said I'd come up with it...

Try putting your date strings in quotes, like:

'2012-01-01' or '2012-01-01 23:59'

Sorry, but I don't get the data transformation you're after.

I see where the 01-Mar and 02-Mar PAIDS records are within ranges of the input data.

I don't see where the other...

Here's an even better one according to query plan cost:

SELECT product_id

FROM @prd_to_cat p2

WHERE EXISTS (

SELECT product_id

FROM @prd_to_cat p1

WHERE p1.product_id = p2.product_id and p1.cat_id = 30)

and p2.cat_id = 69

Never...

Gee whiz Dave, I thought I came up with that on my own! 😉

Actually I'm itching to try out LAG and LEAD, if only I had a 2012 database to...

ColdCoffee's INTERSECT solution is neat but still not the winner when execution plan costs are compared.

I get: Lowell (36%)/ColdCoffee (27%)/Dwain (22%)/initial INSERT (15%)

Just remember that query plan cost doesn't tell...

Why would you JOIN a table onto itself unless you have to? I've never found this approach to be particularly efficient when you have 1,000,000+ rows in your table.

Take...

I'll confess I'm a little confused by your setup data because you've got a start date that's greater than its corresponding end date. Surely that can't be right. ...