how to calculate the days to next holiday for each row

Question

Hello,
I'm trying to update my Date Dimension with TSQL. The attribute is days_to_holiday. At the moment my table looks like this:

DateID; IsHoliday; days_to_holiday
20121234; 0; NULL
20121225; 1; NULL
20121226; 0; NULL
....
20131201;1; NULL

I tried it with an update statement, but cant imagine how i have to build the while clause. Any suggestions what i have to do, that it looks like this?

DateID; IsHoliday; days_to_holiday
...
20121224; 0; 1
20121225; 1; 0
20121226; 0; 6
20121227;0;5
20121228;0;4
20121229;0;3
20121230;0;2
20121231;0;1
20131201;1; 0
...

Many Thanks in advance.
zero

Answer 1

UPDATE tbl
SET    days_to_holiday = (SELECT datediff(DAY, a.DateID, b.DateID)
                          FROM   tbl b
                          WHERE  b.DateID >= a.DateID
                            AND  b.IsHoliday)
FROM   tbl a

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Ok, i got the following error:
Arithmetic overflow error converting expression to data type datetime.

Furthermore i have to write "... and b.IsHoliday = 1"  ?

Answer 3

Ok, i got the following error:
Arithmetic overflow error converting expression to data type datetime.

Furthermore i have to write "... and b.IsHoliday = 1"  ?

@Erland: with your statement i will only update the days_to_holiday for the rows where IsHoliday = true (or 1). Wouldn't I?

Is it possible to do this also with the one's, where IsHoliday is set to false (0)
btw: i'm using SQL Server 2008 R2, the DateID column is in integer datatype

Answer 4

DECLARE	@Sample TABLE
	(
		DateID INT PRIMARY KEY CLUSTERED,
		IsHoliday TINYINT NOT NULL,
		DaysToHoliday TINYINT
	)

INSERT	@Sample
VALUES	(20121224, 0, NULL),
	(20121225, 1, NULL),
	(20121226, 0, NULL),
	(20121227, 0, NULL),
	(20121228, 0, NULL),
	(20121229, 0, NULL),
	(20121230, 0, NULL),
	(20121231, 0, NULL),
	(20130101, 1, NULL)

-- SwePeso
UPDATE		s
SET		s.DaysToHoliday = DATEDIFF(DAY, STR(s.DateID), STR(f.DateID))
FROM		@Sample AS S
OUTER APPLY	(
			SELECT TOP(1)	w.DateID
			FROM		@Sample AS w
			WHERE		w.DateID >= s.DateID
					AND w.IsHoliday = 1
			ORDER BY	w.DateID
		) AS f(DateID)

SELECT	*
FROM	@Sample

---

N 56°04'39.26"
E 12°55'05.63"

Answer 5

Many thanks to both of you.

@SwePeso: I don't understand the full script, but it works fine.

Answer 6

Ok, i got the following error:
Arithmetic overflow error converting expression to data type datetime.

Furthermore i have to write "... and b.IsHoliday = 1"  ?

When you ask this type of questions it is always a good idea to include:

1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data.
3) The desired result given the sample.

If you don't, there is always the risk that you get misguided guesses - how would I know which data type your column has? - and spurious syntax errors.

@Erland: with your statement i will only update the days_to_holiday for the rows where IsHoliday = true (or 1). Wouldn't I?

No, there is no WHERE clause in the UPDATE statement, so all rows are updated.

Peter's solution is similar to mine, but he uses Microsoft's proprietary syntax, while my statement is almost ANSI-compliant. And he did a better gues of the data type.

But I would suggest that you change the data type to date. That's three bytes per value instead of four, and there is no risk that incorrect values like 20120230 sneaks in.

---

Erland Sommarskog, SQL Server MVP, [e-mail]