Viewing 15 posts - 6,841 through 6,855 (of 8,731 total)
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...
February 20, 2014 at 1:42 pm
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 =...
February 20, 2014 at 12:45 pm
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...
February 20, 2014 at 10:58 am
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...
February 20, 2014 at 10:45 am
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...
February 20, 2014 at 10:35 am
Is the bold part an error on your post or on your query?
SELECT PATINDEX('%[[]' + @filename + '][]%',@FILEPATH)
February 20, 2014 at 10:18 am
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...
February 20, 2014 at 10:16 am
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...
February 20, 2014 at 9:57 am
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...
February 20, 2014 at 9:48 am
sqldba20 (2/20/2014)
Nevermind...I was able to figure out using the link posted by Luis. Came with this SQL and it works.
How on earth did you figure that out from the link...
February 20, 2014 at 9:35 am
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.
February 20, 2014 at 9:31 am
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...
February 20, 2014 at 9:28 am
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...
February 20, 2014 at 9:21 am
sqldba20 (2/20/2014)
It is only for display purpose in reporting because the user doesn't want to see 4 rows for each record if there are 4 email ids for that AID.
So,...
February 20, 2014 at 8:50 am
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
February 20, 2014 at 8:30 am
Viewing 15 posts - 6,841 through 6,855 (of 8,731 total)