dwain.c (10/7/2012)
I find it extremely annoying that this does not work:
DECLARE @Weight FLOAT = 0
,@STDate DATETIME
,@EDate DATETIME
SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE
;WITH Tally AS (
SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) n=number-1
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND number BETWEEN 1 AND 100)
MERGE #SAMPLETABLE t
USING Tally s
ON t.DATECOL = DATEADD(day, n, @STDate)
WHEN MATCHED THEN
UPDATE SET @Weight = WEIGHTS
WHEN NOT MATCHED THEN
INSERT (DATECOL, WEIGHTS)
VALUES (DATEADD(day, n, @STDate), @Weight);
SELECT *
FROM #SAMPLETABLE
ORDER BY DATECOL
DROP TABLE #SAMPLETABLE
When BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) says that you should be able to SET assign to a local variable.
Dwain - I tried this too, a few months ago. It's bl@@dy irritating that it doesn't appear to work when BOL suggests it should.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden