zulfansari (2/16/2014)
Hi Jeff,Actually it's 0.25,0.50, etc. format, I made up the data for the post and used the wrong format.
Thank you,
Then a Tally Table will make the solution to this problem incredibly easy. I left the "Hours" mix in the output just so you can verify the output. You can remove that column whenever you're ready.
--===== Build a test table and populate it on-the-fly.
-- THIS IS NOT A PART OF THE SOLUTION!!!
-- WE JUST NEEDED SOMETHING TO DEMO THE SOLUTION WITH!!!
SET DATEFORMAT MDY;
SELECT d.Person
,[Date] = CAST(d.[Date] AS DATETIME)
,d.Hours
INTO #TestTable
FROM (
SELECT 101,'02/01/2014', 1.00 UNION ALL
SELECT 101,'02/02/2014', 1.25 UNION ALL
SELECT 101,'02/03/2014',11.75
)d(Person,[Date],Hours)
;
--===== This is the solution made incredibly easy
-- by the use of a Tally Table.
SELECT Person,[Date],Hours,IntervalHours = t.N*.25
FROM #TestTable
CROSS JOIN dbo.Tally t
WHERE t.N <= Hours*4
ORDER BY Person, Date, IntervalHours
;
Here's the output from the code above. Like I said, you can simply remove the "Hours" column if you don't really want it.
Person Date Hours IntervalHours
----------- ----------------------- ----- -------------
101 2014-02-01 00:00:00.000 1.00 0.25
101 2014-02-01 00:00:00.000 1.00 0.50
101 2014-02-01 00:00:00.000 1.00 0.75
101 2014-02-01 00:00:00.000 1.00 1.00
101 2014-02-02 00:00:00.000 1.25 0.25
101 2014-02-02 00:00:00.000 1.25 0.50
101 2014-02-02 00:00:00.000 1.25 0.75
101 2014-02-02 00:00:00.000 1.25 1.00
101 2014-02-02 00:00:00.000 1.25 1.25
101 2014-02-03 00:00:00.000 11.75 0.25
101 2014-02-03 00:00:00.000 11.75 0.50
101 2014-02-03 00:00:00.000 11.75 0.75
101 2014-02-03 00:00:00.000 11.75 1.00
101 2014-02-03 00:00:00.000 11.75 1.25
101 2014-02-03 00:00:00.000 11.75 1.50
101 2014-02-03 00:00:00.000 11.75 1.75
101 2014-02-03 00:00:00.000 11.75 2.00
101 2014-02-03 00:00:00.000 11.75 2.25
101 2014-02-03 00:00:00.000 11.75 2.50
101 2014-02-03 00:00:00.000 11.75 2.75
101 2014-02-03 00:00:00.000 11.75 3.00
101 2014-02-03 00:00:00.000 11.75 3.25
101 2014-02-03 00:00:00.000 11.75 3.50
101 2014-02-03 00:00:00.000 11.75 3.75
101 2014-02-03 00:00:00.000 11.75 4.00
101 2014-02-03 00:00:00.000 11.75 4.25
101 2014-02-03 00:00:00.000 11.75 4.50
101 2014-02-03 00:00:00.000 11.75 4.75
101 2014-02-03 00:00:00.000 11.75 5.00
101 2014-02-03 00:00:00.000 11.75 5.25
101 2014-02-03 00:00:00.000 11.75 5.50
101 2014-02-03 00:00:00.000 11.75 5.75
101 2014-02-03 00:00:00.000 11.75 6.00
101 2014-02-03 00:00:00.000 11.75 6.25
101 2014-02-03 00:00:00.000 11.75 6.50
101 2014-02-03 00:00:00.000 11.75 6.75
101 2014-02-03 00:00:00.000 11.75 7.00
101 2014-02-03 00:00:00.000 11.75 7.25
101 2014-02-03 00:00:00.000 11.75 7.50
101 2014-02-03 00:00:00.000 11.75 7.75
101 2014-02-03 00:00:00.000 11.75 8.00
101 2014-02-03 00:00:00.000 11.75 8.25
101 2014-02-03 00:00:00.000 11.75 8.50
101 2014-02-03 00:00:00.000 11.75 8.75
101 2014-02-03 00:00:00.000 11.75 9.00
101 2014-02-03 00:00:00.000 11.75 9.25
101 2014-02-03 00:00:00.000 11.75 9.50
101 2014-02-03 00:00:00.000 11.75 9.75
101 2014-02-03 00:00:00.000 11.75 10.00
101 2014-02-03 00:00:00.000 11.75 10.25
101 2014-02-03 00:00:00.000 11.75 10.50
101 2014-02-03 00:00:00.000 11.75 10.75
101 2014-02-03 00:00:00.000 11.75 11.00
101 2014-02-03 00:00:00.000 11.75 11.25
101 2014-02-03 00:00:00.000 11.75 11.50
101 2014-02-03 00:00:00.000 11.75 11.75
As previously posted, here's where you can learn more about the Tally Table, how to build it, and how it works to avoid loops and cursors.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Once you've mastered using a physical Tally Table, then you can get into what Jason and Dwain where showing... on the fly creation of Tally-Table-like structures, which is also introduced in the article I provided a link to.
The reason why I don't just cough up the very simple method of building a Tally Table is because I really want you to understand how the Tally Table works. My personal belief is that it will improve your career as someone using T-SQL as it has done for nearly everyone that's ever used such a thing. It changes your mind from thinking in rows to thinking in columns and that's the main paradigm shift required to write some really nasty fast code in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.