A conditional SUM() based on a calendar table should do it.

Would you please post the query in total as well as some more test data (including expected result)?

I'm confident it still can be done using a single CTE but I'd...

Glad I could help! 😀

What type of index did you create (clustered or non-clustered)? If it's the clustered index you still perform a table scan...

As a side note: why do you use VARCHAR(3) instead...

Here's a slightly more complicated solution:

; WITH cte1 AS

(

SELECT

*,

DATEADD(mi,DATEPART(MINUTE,phonetime)/15 *15,DATEADD(hh,DATEDIFF(hh,0,Phonetime),0)) AS INTERVAL

FROM #PhoneCalls

), cte2 AS

(

SELECT

customerid,

INTERVAL,

ROW_NUMBER() OVER(PARTITION BY INTERVAL ORDER BY phonetime)-

ROW_NUMBER() OVER(PARTITION BY...

If you have any further question or if you get stuck, please post back and I'm confident we can help you.

From my point of view the query cost in the actual execution plan is even more misleading than the recommended indexes DTA comes up with (comparing apples and oranges, I...

There are several issue to be adressed:

1) it seems like the code snippet you posted is part of a dynamic SQL statement... (otherwise there's no reason to use two single...

When you're asking for the difference in months, SQL Server basically truncates the days of the dates you compare:

For example:

SELECT DATEDIFF(mm,'20101231','20110101')

SELECT DATEDIFF(mm,'20101201','20110131')

Both queries return the same value (1), since the...

Glad I could help 😀

A second set of eyes helps a lot from time to time. Especially, if there's no mouth connected to it... 😉

I don't think you need the UNION ALL in your CTE:

SELECT DTConID, TransactionType,

Sum(Convert(decimal(12,2),CASE WHEN AmtSign = '-' THEN 0 -...

What is the order criteria to get the values for I_Serial?

Regarding getting ready to use sample data out of SSMS: please have a look at the first article referenced in...

Please provide some sample data in the form "INSERT INTO ... SELECT ... UNION ALL" and your expected result based on the sample. Especially, please make sure to explain what...

Here's a slightly different approach:

SELECT SUBSTRING(URLs,8,charindex('/',URLs+'/',8)-8) AS URLs

FROM @URLTable

Step 1: Understand normalization. You could google for "database design normalization" to get several links to cover the basics.

Step 2: Understand the meaning and purpose of the data you're dealing...