StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Borrowing most of the #tmp table definition from @Ben Thul, assuming you have an identity field, not assuming you are storing dates and times as dates and times...this should run well over and over, copying the latest date into the following week:</p> <pre><code>CREATE TABLE #tmp ( [RecordID] INT , [EmpType] VARCHAR(20) , [Date] VARCHAR(9) , [Day] VARCHAR(10) , [Meal] VARCHAR(10) , [ShiftOrder] INT , [InTime] VARCHAR(11) , [EmployeeID] VARCHAR(50) ) INSERT INTO [#tmp] ( [RecordID] , [EmpType] , [Date] , [Day] , [Meal] , [ShiftOrder] , [InTime] , [EmployeeID] ) VALUES (1,'Server','29-Aug-11','Monday','Lunch',1,'10:30:00 AM','Monica'), (2,'Server','29-Aug-11','Monday','Lunch',2,'11:00:00 AM','Sofia'), (3,'Server','29-Aug-11','Monday','Lunch',3,'11:30:00 AM','Jenny'), (4,'Server','29-Aug-11','Monday','Lunch',4,'12:00:00 PM','Adam'), (5,'Server','29-Aug-11','Monday','Dinner',1,' 4:30:00 PM','Adam'), (6,'Server','29-Aug-11','Monday','Dinner',2,' 4:45:00 PM','Jenny'), (7,'Server','29-Aug-11','Monday','Dinner',3,' 5:00:00 PM','Shauna'), (8,'Server','29-Aug-11','Monday','Dinner',4,' 5:15:00 PM','Sofia'), (10,'Server','29-Aug-11','Monday','Dinner',5,' 5:30:00 PM','Monica'); with Shifts as ( select EmpType, [Day], Meal, ShiftOrder, InTime from #tmp where [Date] = (select max(cast([Date] as datetime)) from #tmp) ), MaxShifts as ( select EmpType, [Day], Meal, max(ShiftOrder) as MaxShiftOrder from #tmp where [Date] = (select max(cast([Date] as datetime)) from #tmp) group by EmpType, [Day], Meal ) insert into #tmp (EmpType, [Date], [Day], Meal, ShiftOrder, InTime, EmployeeID) select s.EmpType , replace(convert(varchar(11), dateadd(dd, 7, cast(a.[Date] as datetime)), 6), ' ', '-') as [Date] , s.Day , s.Meal , s.ShiftOrder , s.InTime , a.EmployeeID from #tmp as a join MaxShifts as m on a.EmpType = m.EmpType and a.[Day] = m.[Day] and a.Meal = m.Meal join Shifts as s on a.EmpType = s.EmpType and a.[Day] = s.[Day] and a.Meal = s.Meal and 1 + a.ShiftOrder % m.MaxShiftOrder = s.ShiftOrder where a.[Date] = (select max(cast([Date] as datetime)) from #tmp) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload