How could I "auto-rotate" appended records in SQL (1 goes to 2, 2 goes 3, 3 goes to 4, 4 goes back to 1)?

I'm working on a system (ASP.NET/MSSQL/C#) for scheduling restaurant employees.

The problem I'm having is I need to "auto-rotate" the shift "InTimes" every week.

The user needs to be able to copy one day's schedule to the same day next week with all the employee shift times rotated one slot.

For example, in the table below, Monica has the 10:30am shift this Monday, so she would have the 11:00am next week, and Adam would go from 12:00pm to 10:30am.

The time between shifts is not constant, nor is the number of employees on each shift.

Any ideas on how to do this (ideally with SQL statements) would be greatly appreciated.

Please keep in mind I'm a relative novice.

RecordID EmpType Date      Day    Meal ShiftOrder InTime       EmployeeID 
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

Somehow an employee would need to get his last (few) shifts

SELECT TOP 3 * FROM shift WHERE EmployeeID LIKE 'monica' ORDER BY [date] DESC

Next he/she would need to enter the time and date offset he would like to work next week, relative to a schedule before.

INSERT INTO shift SELECT 
  recordID 
  ,[date]
  ,CASE [Intime]
     WHEN [Intime] BETWEEN 00:00 AND 10:00 THEN 'Breakfast'
     WHEN [Intime] BETWEEN 10:01 AND 04:29 THEN 'Lunch'
     WHEN [Intime] BETWEEN 04:30 AND 23:59 THEN 'Dinner'
   END as Meal
    ,No_idea_how_to_generate_this AS ShiftOrder
   ,[Intime]
   ,EmployeeID
FROM (SELECT
  NULL as recordID
  ,DATEADD(DAY, 7+@dateoffset, ls.[date]) as [date]
  ,CAST(DATEADD(MINUTE, @timeoffset, ls.[time] AS TIME) as [Intime]
  ,EmployeeId 
FROM Shift WHERE recordID = @recordID ) AS subselect

Here: - @recordID is the record the employee choose as the starting point for the new appointment.
- @dateoffset is the number of days to add the the starting record - @timeoffset is the number of minutes to add to the starting record

All the rest is determined by the row the user used as the starting point.

Here's what I came up with:

CREATE TABLE #tmp
(
  [RecordID] INT ,
  [EmpType] VARCHAR(20) ,
  [Date] DATE ,
  [Day] VARCHAR(10) ,
  [Meal] VARCHAR(10) ,
  [ShiftOrder] INT ,
  [InTime] TIME ,
  [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 CountByShift AS (SELECT *, COUNT(1) OVER (PARTITION BY EmpType, [Day], [Meal]) AS [CountByShiftByDayByEmpType]
FROM [#tmp]
),
NewShiftOrder AS (
    SELECT *, ([ShiftOrder] + 1) % [CountByShiftByDayByEmpType] AS [NewShiftOrder]
    FROM [CountByShift]
)
SELECT  [RecordID] ,
        [EmpType] ,
        [Date] ,
        [Day] ,
        [Meal] ,
        [ShiftOrder] ,
        CASE WHEN [NewShiftOrder] = 0 THEN [CountByShiftByDayByEmpType] ELSE [NewShiftOrder] END AS [NewShiftOrder],
        [InTime] ,
        [EmployeeID]
FROM NewShiftOrder
ORDER BY [RecordID]

You need a table with all of the shifts in it:

create table dbo.Shifts (
  [Day]      varchar(9) not null,
  Meal       varchar(6) not null,
  ShiftOrder integer not null,
  InTime     time not null,
  constraint PK__dbo_Shifts primary key ([Day], Meal, ShiftOrder) 
);

If that table is properly populated you can then run this to get a map of the current Day, Meal, ShiftOrder n-tuple to the next in that Day, Meal pair:

with numbers_per_shift as (
  select [Day], Meal, max(ShiftOrder) as ShiftOrderCount
    from dbo.Shifts s
   group by [Day], Meal
)

select s.[Day], s.Meal, s.ShiftOrder, 
       s.ShiftOrder % n.ShiftOrderCount + 1 as NextShiftOrder
  from dbo.Shifts as s
 inner join numbers_per_shift as n
    on s.[Day] = n.[Day]
   and s.Meal  = n.Meal;

For the table to be properly populated each of the shift orders would have to begin with one and increase by one with no skipping or repeating within a Day, Meal pair.

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:

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)

I'm assuming that the schedule is really tied to a meal and weekday in a below answer.

Also I would like to note that ShiftOrder and Day columns should not be columns. Day is obviously determined by Date so it is a total waste of space (computed column OR determine it on the UI side) and ShiftOrder is determined by Date and InTime columns (probably easy to calculate in a query with RANK() function or on the UI side). That said it will make this query a bit easier :)

declare @dt date = cast('29-Aug-11' as date)
/* note: the date above may be passed from UI or it maybe calculated based on getdate() and dateadd function or s.t. like that */

INSERT INTO [table] (EmpType,Date,Day,Meal,ShiftOrder,InTime,EmployeeID)
SELECT t1.EmpType, dateadd(day, 7, t1.date), t1.day, t1.meal, t2.ShiftOrder, t2.InTime, t1.EmployeeID
FROM [table] t1
INNER JOIN [table] t2
ON (t1.Date = t2.Date 
    and t1.Meal = t2.Meal 
    and (
        t1.ShiftOrder = t2.ShiftOrder + 1
        or
        (
            t1.ShiftOrder = (select max(shiftOrder) from [table] where meal = t1.meal and date =t1.date)
            and
            t2.ShiftOrder = (select min(shiftOrder) from [table] where meal = t1.meal and date =t1.date)
        )
    )
)
WHERE t1.Date = @dt

This is a pretty straight-forward set-oriented problem. Aggregations (count(*) and max()) and lookup tables are unnecessary. You can do it with one SQL statement.

The first step (set) is to identity those employees who simply slide down in the schedule.

The next step (set) is to identity those employees who need to "wrap around" to the head of the schedule.

Here's what I came up with:

/* Set up the temp table for demo purposes */
DROP TABLE #tmp

CREATE TABLE #tmp
(
  [RecordID] INT ,
  [EmpType] VARCHAR(20) ,
  [Date] DATE ,
  [Day] VARCHAR(10) ,
  [Meal] VARCHAR(10) ,
  [ShiftOrder] INT ,
  [InTime] TIME,
  [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');

/* the "fills" CTE will find those employees who "wrap around" */
;WITH fills AS (
    SELECT
        [d2].[EmpType],
        [d2].[Date],
        [d2].[Day],
        [d2].[Meal],
        1 AS [ShiftOrder],
        [d2].[InTime],
        [d2].[EmployeeID]
    FROM
        [#tmp] d1
    RIGHT OUTER JOIN
        [#tmp] d2 ON
            ([d1].[Meal] = [d2].[Meal])
            AND ([d1].[ShiftOrder] = [d2].[ShiftOrder] + 1)
    WHERE
        [d1].[EmployeeID] IS NULL
)
INSERT INTO [table] (EmpType,Date,Day,Meal,ShiftOrder,InTime,EmployeeID)
SELECT
    [d1].[EmpType],
    DATEADD(DAY, 7, [d1].[Date]) AS [Date],
    DATENAME(dw,(DATEADD(DAY, 7, [d1].[Date]))) AS [Day],
    [d1].[Meal],
    [d1].[ShiftOrder],
    [d1].[InTime],
    ISNULL([d2].[EmployeeID], [f].[EmployeeID]) AS [EmployeeID]
FROM
    [#tmp] d1
LEFT OUTER JOIN
    [#tmp] d2 ON
        ([d1].[Meal] = [d2].[Meal]) AND ([d1].[ShiftOrder] = [d2].[ShiftOrder] + 1)
LEFT OUTER JOIN
    [fills] f ON
        ([d1].[Meal] = [f].[Meal]) AND ([d1].[ShiftOrder] = [f].[ShiftOrder])

You can use a subquery (for a tutorial on subqueries, see http://www.databasejournal.com/features/mssql/article.php/3464481/Using-a-Subquery-in-a-T-SQL-Statement.htm) to get the last shift time. After this, its trivial addition and modular division (in case you don't know what that is, have a look at this).

Hope this helped. I'm a bit tired right now, so I can't provide you with an example.

I'm a SQL programmer and DBA for 20 yrs now. With that said, business logic this complex should be in the C# part of the system. Then the TDD built application can handle the inevitable changes, and still be refactor-able and correct.

My recommendation is 'push-back'. Your response should be something along the lines of "This isn't just some look-up/fill-in the blank logic. This kind of complex business logic belongs in the App". It belongs in something that can be unit tested, and will be unit tested every time its changed.

The right answer sometimes is 'No', this is one of them.

How about using a Pivot Table for all employees and then adding shift timings as rows?? Order the names based on Shift for the initial Day.

Something like this..

Date_time Shift_Order  Monica   Sofia    Jenny    Adam      Shauna  
08/29/11  1        10:30AM  11:00AM  11:30AM  12:00PM   NULL  
08/29/11  2        5:30PM   5:15PM   4:45PM   4:30PM    5:00PM