Updating table based on the interval from another. SQL Server -


I should count how many men were working every hour I have two tables: 

  hour of men ------------ 6 0 7 0 8 0 9 10 10 11 12 12

Where "man" / Code> column men's count is another table: 

  ClockedInHour ClockedOutHour 6 10 7 10 8 12 6 11 9 12

So in the second table For each entry, I need to increase increment in the table between those "ClockedInHour" and "ClockedOutHour" In the first table Modes Each hour "I" column, and, if possible, to do it without the cursor.

Extra Status: This will be part of a table-valued function.

Thank you in advance for your help.

Resolve! 

  announcement @ bell INT SET @ hour6 = (SELECT COUNT (*) FROM @ table2 where ClockedOutHour> = 6 and ClockedInHour & lt; = 6) where ClockedOutHour & gt table2 @ hour7 INT set @ hour7 = (select COUNT (*) bukhan = 7 and ClockedInHour <= 7) Announcement @ Hour8 INT set @ Hour8 = (SELECT COUNT (*) @ table2 where ClockedOutHour> = 8 and ClockedI NHour & lt; = 8) Where ClockOutHour & gt table2 @ Hour9 INT set @ hour9 = (COUNT select (*) Sill = 9 and ClockedInHour & lt; = 9) Announcement @ hour10 INT set @ hour10 = (COUNT of Select (*) from @ table2 where ClockedOutHour> = 10 and ClockedInHour & lt; = 11 and ClockedInHour & lt; = 11) announcement @ hour12 INT = 10) table2 where ClockedOutHour & gt @ announcement @ Hour11 INT set @ Hour11 = ( Select COUNT (*) set @ hour12 = (COUNT select (*) @tc where ClockedOutHour> = 12 and ClockedInHour & lt; = 12) Update @ table1 Set Man = @ Hour6 Where time = 6 update @ Table1 set man = @ hour7 where time = 7 update @ table1 set man = @ hour8 where time = 8 update @ table1 set man = @ hour9 where time = 9 update @ table1 set man = @ hour10 where time = 10 update @ table1 Set Man = @ Hour11 Where Time = 11 Update @ Table 1 SET Man = @ Hours 12 Where Hours = 12

Comments

Post a Comment