0

I have database with job numbers, scheduled date, and scheduled hours such as this:

J410 | 11/14/2016 | 50|

I have been asked to produce a report with one line for each day of the job like this:

J410 | 11/14/2016 | 10 |
J410 | 11/15/2016 | 10 |
J410 | 11/16/2016 | 10 |
J410 | 11/17/2016 | 10 |
J410 | 11/18/2016 | 10 |

The logic is that we assume 10 hour days, so the total number of hours divided by 10 = the number of days, then the users want a line for each day.

I can easily get the number of days like this:

SELECT CEILING(Hours / 10.0) - Note that some hours don't divide evenly by 10 so I am rounding up.

I don't have the slightest idea how to attack the problem of creating (for reporting only) additional lines for each date.

My initial thoughts are to select the records into a temp table and then select each record and use a WHILE statement to duplicate the records until the number of days have been reached.

Can anyone provide a better idea ?

user3511334
  • 91
  • 9

2 Answers2

1

If it helps

Declare @YourTable table (JobNumber varchar(25),Date date,Hours int)
Insert Into @YourTable values
('J410','11/14/2016',50)

;with cte0(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     ,cteN(N) As (Select Row_Number() over (Order By (Select NULL)) From cte0 N1, cte0 N2, cte0 N3)
Select A.JobNumber
      ,Date = DateAdd(DD,N-1,Date)
      ,Hours = cast(Hours/CEILING(Hours/10.0) as decimal(10,2))
 From  @YourTable A
 Join cteN B on  N<=CEILING(Hours/10.0)

Returns

JobNumber   Date        Hours
J410        2016-11-14  10.00
J410        2016-11-15  10.00
J410        2016-11-16  10.00
J410        2016-11-17  10.00
J410        2016-11-18  10.00
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Yikes !! Yes, it works but I sure don't understand it. I've looked it over and it appears that cte is Common Table Expression (which I'm not familiar with), and I'm struggling with the OVER keyword also. I'm going to do some studying and I may ask a few questions if you don't mind. – user3511334 Nov 14 '16 at 16:25
  • @user3511334 the cte just create an ad-hoc tally table (1 - 1,000). – John Cappelletti Nov 14 '16 at 16:26
  • 1
    @user3511334 cte0 is a dataset with 10 rows cteN does a cross join of that table 3 times which would yeild 1,000 rows (10 * 10) * 10 but then it does a ROW_NUMBER() [look up window functions to understand over] which creates a incremented number from 1 to 1000 then he joins to that table where the number is less than or equal to the CEILING of hours divided by 10 – Matt Nov 14 '16 at 16:30
  • I understand, I never could have done this on my own. Thank you so much. – user3511334 Nov 14 '16 at 16:48
  • @user3511334 Never say never. Happy to help – John Cappelletti Nov 14 '16 at 16:50
0

Use a Numbers Table and add a day to your existing table until the date limit is reached...

Community
  • 1
  • 1
JohnHC
  • 10,935
  • 1
  • 24
  • 40
  • as it stands this seems more like a comment than an answer. The idea is great and pointing in the right direction to be an answer though you should actually show how to use a numbers/tally table to accomplish the task. – Matt Nov 14 '16 at 15:54
  • 1
    @Matt True... My bad – JohnHC Nov 14 '16 at 15:57