Creating temp table??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating temp table???

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-27 : 22:26:17

I am getting frustrated with the accounting database table structure. I am really struggling to get the ‘par hours’. This is the amount of time that someone is assigned to work based on their workgroup in the workhour table.

Table 1 –Workhour table

Hours Resourceid workhourgroupcode dayid

NULL TJOHNSO TJOHNSO 1

8.0 TJOHNSO TJOHNSO 2

NULL TJOHNSO TJOHNSO 3

8.0 TJOHNSO TJOHNSO 4

NULL TJOHNSO TJOHNSO 5

8.0 TJOHNSO TJOHNSO 6

NULL TJOHNSO TJOHNSO 7

Table 2 - Time

Work DayHours

WorkGroup Resourceid DayID Non TimeDayWorked Hours worked

NULL SMB ABOOKWALTER 1 NULL 2007-12-26 00:00:00.000 7.50

NULL SMB ABOOKWALTER 1 NULL 2007-12-27 00:00:00.000 7.50

NULL SMB ABOOKWALTER 1 NULL 2007-12-28 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 2 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 2 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 2 1.00 2007-12-28 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 3 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 3 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 3 1.00 2007-12-28 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 4 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 4 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 4 1.00 2007-12-28 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 5 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 5 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 5 1.00 2007-12-28 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 6 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 6 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKWALTER 6 1.00 2007-12-28 00:00:00.000 7.50

NULL SMB ABOOKWALTER 7 NULL 2007-12-26 00:00:00.000 7.50

NULL SMB ABOOKWALTER 7 NULL 2007-12-27 00:00:00.000 7.50

NULL SMB ABOOKWALTER 7 NULL 2007-12-28 00:00:00.000 7.50

When I link these together by the resourceid dates worked, but since there is no date in the workhour table I cannot even do an outer join. The only think I could think of was creating a temp table (or a view) to return a line item for every day during the timeframe given. I created a dayID for they second table, I am not sure how to proceed. I need a line item to return for every day of the month so it will look at the workhours table to see how many hours someone is supposed to work.That will allow me to bring into Crystal, group and total.

8.30 SMB ABOOKW 6 1.00 2007-12-26 00:00:00.000 7.50

8.30 SMB ABOOKW 6 1.00 2007-12-27 00:00:00.000 7.50

8.30 SMB ABOOKW 6 1.00 2007-12-28 00:00:00.000 7.50

NULL SMB ABOOK 7 NULL 2007-12-29 00:00:00.000 0

NULL SMB ABOOKW 7 NULL 2007-12-30 00:00:00.000 7.50

NULL SMB ABOOKW 7 NULL 2007-12-31 00:00:00.000 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 23:25:46

I think you can search within this forums for a function called FTABLE which inserts all dates between two given dates.This might prove to be useful to you.

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-28 : 08:47:50

Thanks for pointing me in the right direction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources