Here's the final solution with the gaps problem solved (Hoping you won't have dates that go over 25 years in the future).

WITH E1(N) AS(

SELECT N FROM( VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(N) --10 rows

),

E2(N) AS(

SELECT...

With the Information_Schema views, this kind of queries become easier.

DECLARE @sql VARCHAR(MAX)

SELECT @sql = (SELECT 'SELECT ' +

STUFF( (SELECT ',' + COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS x

WHERE DATA_TYPE = 'int'

AND x.TABLE_NAME =...

It would be as simple as this, as explained in the article:

SELECT t.AID,

Emails = STUFF((SELECT '; ' + t1.EmailID

FROM #TEST1 t1

WHERE t1.AID = t.AID

FOR XML PATH('')), 1, 2, '')

FROM...

This will return the correct results but only if you change your filename data type to varchar:

SELECT CHARINDEX(@filename,@FILEPATH), PATINDEX('%' + REPLACE( @filename, '[', '[[]') + '%',@FILEPATH)

This should show you the...

This might help others to complete the requirement (and maybe improve it). It's missing the gaps problem (shown on store 4).

If the gaps aren't a problem, then this should do...

Is the bold part an error on your post or on your query?

If you format your code, it should be clear.

UPDATE AxysPriceFile

SET CurrentPX = Moxy.Price

WHERE Type = Moxy.SecType

AND Ticker = Moxy.Symbol

IN /*What column should be...

To complete previous post.

If you change your scalar function into an inline table-valued function, your queries should perform a lot better. Check this article for more information:

This is an...

The problem is that @d is a varchar. To use DATEADD, SQL Server converts @d to datetime and then back to varchar to assign it to @d. The second conversion...

How on earth did you figure that out from the link...

It should be more efficient as it only reads the table once instead of twice.

You could test it and check the differences on larger tables.

How do you define that it goes from 10 to 15 and then back to 5?

It looks like a running total problem. Before 2012, those problems have several solutions, the...

As you're quite new with SQL and this site, I'm creating sample data in a way that we can just copy and execute it. You're expected to do this and...

So,...

You don't need to scan the table twice 😉

SELECT Id, layer,

SUM(CASE WHEN test > 1 THEN test * duration END) /

SUM(duration * 1.0)

FROM #test

GROUP BY ID,

layer