Looping through a table to create a temp table

Question

I have a table that has one date field that is the week number and then 7 decimal fields that represents the hours worked.

I want to loop through this table and create a row in a temp table for each day of the week with the hours worked each day.

I could do a simple ...

SELECT  UserID, WeekNum, Hours1, Hours2, Hours3, Hours4, Hours5, Hours6, Hours7
INTO    #NCExtract
FROM    Timesheet

But I need to have a result

UserID Date Hours
UserID Date Hours
UserID Date Hours
UserID Date Hours
UserID Date Hours
UserID Date Hours
UserID Date Hours

from one row. So I began the following way:

create table #NCExtract
(
    UserID int, 
    WorkDate DateTime, 
    WorkHours decimal
) 

Select *
From   TimeSheetTable

While (Select Count(*) From TimeSheetTable) > 0
Begin
    Create #NCExtract record with 1st date hours
    Create #NCExtract record with 2nd date hours
    Create #NCExtract record with 3rd date hours
    Create #NCExtract record with 4th date hours
    Create #NCExtract record with 5th date hours
    Create #NCExtract record with 6th date hours
    Create #NCExtract record with 7th date hours
End

I am not sure how to provide the info in the loop to create the records.

Cody Hamilton · Answer 1 · 2016-07-11T20:04:30.870

0

I can think of two ways to do what you want (assuming t-sql, but should work for other db's as well).

unpivot :https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

or 7 select statements (see below for rough approximation)

--Since i do not have a table definition of timesheet table might be a little off
create table #NCExtract ( UserID int, WorkDate DateTime, WorkHours decimal )

insert into #NCExtract (UserID, WorkDate, WorkHours)
Select Userid, DateAdd(d,1,WeekNum), Hours1 -- I assumed that the weeknum column was a date/datetime
From TimeSheetTable 
Where Hours1 is not null -- add any needed logic (e.g. not null or <> 0)

insert into #NCExtract (UserID, WorkDate, WorkHours)
Select Userid, DateAdd(d,2,WeekNum), Hours2 --update the date add for each select
From TimeSheetTable
Where Hours2 is not null

--3, 4, 5, 6 ommited

select * from #NCExtract 
order by UserID, WorkDate -- if the final result needs to be sorted, sort when selecting

edited Jul 11 '16 at 20:04

answered Jul 11 '16 at 18:35

Cody Hamilton

31
4

This is a bit off, but I think I get the gist of what you are saying. I will have to run 7 inserts, essentially, one for each day. I can do that. Thanks! – Dlangschied Jul 11 '16 at 19:32
@Dlangschied Glad I could help! I knew it was going to be a little off, but wanted to give you a quick approximation off the provided information to point you in the right direction. – Cody Hamilton Jul 11 '16 at 20:09
@Dlangschied if my answer gave you the information you needed can you please mark it as the accepted answer, and if not what are you stuck on? – Cody Hamilton Jul 12 '16 at 15:48

Looping through a table to create a temp table

1 Answers1