how to calculate the days to next holiday for each row

# how to calculate the days to next holiday for each row

• Sunday, May 13, 2012 9:26 PM

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
...

zero

• Edited by Sunday, May 13, 2012 9:27 PM
• Edited by Sunday, May 13, 2012 9:27 PM
• Edited by Sunday, May 13, 2012 9:28 PM
•

### All Replies

• Sunday, May 13, 2012 9:56 PM

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, esquel@sommarskog.se
• Sunday, May 13, 2012 10:15 PM

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

Furthermore i have to write "... and b.IsHoliday = 1"  ?
• Sunday, May 13, 2012 10:34 PM

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

• Edited by Sunday, May 13, 2012 10:34 PM
•
• Sunday, May 13, 2012 10:39 PM

```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"

• Marked As Answer by Sunday, May 13, 2012 10:48 PM
•
• Sunday, May 13, 2012 10:48 PM

Many thanks to both of you.

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

• Monday, May 14, 2012 7:42 AM

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, esquel@sommarskog.se