dwain.c (2/16/2014)
If you don't have a numbers table at your disposal like Jason does, this will also work.
DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))
INSERT INTO @sometab
( Person, Date, Hours )
VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);
WITH Tally (n) AS
(
SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
)
SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))
FROM @sometab a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[Hours]
) b
ORDER BY Person, [Date], n;
Shhh... I was waiting for somebody to ask about that 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events