Parse In/Out Times into one or more Shift Codes

  • Giorgio Vidali

    Ten Centuries

    Points: 1116

    Once again, I come to rely on this helpful community.

    For a while now, I have been struggling with a problem that (even if it is more or less solved with application procedural code) would greatly benefit if it could be solved at the database level.

    Explaining the business case is a bit difficult but let me try:

    We have a table that contains “SHIFT CODES”, where each shift code is defined by, you know, a code… and by “time boundaries”. For example, shift code 1ST is “delimited” by the time boundaries “9:00 AM” – “5:00 PM”

    We have another table that contains “EMPLOYEE TIMECARDS” for a certain week ending. The table has room for 4 sets of IN/OUT times – and employees enter their own data. IN and OUT time entered here by the employee may not really match the time boundaries of a certain shift. (Incidentally: no control over data types)

    It is up to a “process” to parse EACH DAY and EACH IN/OUT block to (possibly) split the total hours over different shifts.

    For example:

    • shift code 1ST is “delimited” by the time boundaries “9:00 AM” – “5:00 PM” (and there are other codes covering the entire 24 hours day)
    • employee enters a time in/out block 08:30 AM – 05:30 PM
    • that block would have to be parsed as something like

      • SHIFT CODE 3RD = 0.5 hours
      • SHIFT CODE 1ST = 8 hours
      • SHIFT CODE 2ND = 0.5 hours

    (we use decimal representation for hours… common in payroll and time tracking)

    If you were to run the attached script you could quickly recreate a simple test case and observe the expected results.Could this be actually solved with a procedure / function…?

    I have been browsing and searching on this topic but haven't been able to find anything related...

    Thanks in advance for any help or suggestion

    Giorgio

    IF UPPER(DB_NAME()) = 'MASTER'
    SET NOEXEC ON;

    SET ANSI_NULLS ON;
    GO
    SET QUOTED_IDENTIFIER ON;
    GO
    SET ANSI_PADDING ON;
    GO


    /*

    Run this script ONCE to create test framework...

    The table “SHIFTS” holds the description of work shifts.
    Each shift is identified by a code and by a “time in” and “time out”.

    The table “TIMECARDS” holds the timecards of employees, with in/out times.
    In and out times may or may not exactly match the in and out time of a predefined shift code.

    We must “parse” each day in the time card and generate one or more rows, and
    calculate the total hours for each shift.

    For example:


    Given the shift codes boundaries in SHIFTS, and given the employee time in/out in TIMECARDS
    we must write these lines into RESULTS:

    SELECT * FROM SHIFTS WHERE customer_id = 1 ORDER BY shift_code

    SELECT * FROM [dbo].[TIMECARDS] WHERE timecard_week_of = '2019-04-21'
    AND customer_id = 1 AND employee_id = 100 AND timecard_day = '2019-04-15'

    SELECT * FROM RESULTS where timecard_week_of = '2019-04-21'
    AND customer_id = 1 AND employee_id = 100 AND timecard_day = '2019-04-15'
    ORDER BY shift_code

    */

    IF NOT EXISTS
    (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'SHIFTS'
    AND TABLE_SCHEMA = 'dbo'
    )
    BEGIN
    CREATE TABLE [dbo].[SHIFTS]
    ([id] [INT] IDENTITY(1, 1) NOT NULL,
    [customer_id] [INT] NOT NULL,
    [shift_code] [VARCHAR](10) NOT NULL,
    [time_in] [VARCHAR](10) NULL,
    [time_out] [VARCHAR](10) NULL,
    CONSTRAINT [PK_SHIFTS] PRIMARY KEY CLUSTERED([customer_id] ASC, [shift_code] ASC)
    WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
    ON [PRIMARY];
    END;
    GO

    IF NOT EXISTS
    (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'TIMECARDS'
    AND TABLE_SCHEMA = 'dbo'
    )
    CREATE TABLE [dbo].[TIMECARDS]
    ([id] [INT] IDENTITY(1, 1) NOT NULL,
    [timecard_week_of] [DATE] NOT NULL,
    [customer_id] [INT] NOT NULL,
    [employee_id] [INT] NOT NULL,
    [timecard_day] [DATE] NOT NULL,
    [time_in_1] [VARCHAR](10) NULL,
    [time_out_1] [VARCHAR](10) NULL,
    [time_in_2] [VARCHAR](10) NULL,
    [time_out_2] [VARCHAR](10) NULL,
    [time_in_3] [VARCHAR](10) NULL,
    [time_out_3] [VARCHAR](10) NULL,
    [time_in_4] [VARCHAR](10) NULL,
    [time_out_4] [VARCHAR](10) NULL,
    CONSTRAINT [PK_TIMECARDS] PRIMARY KEY CLUSTERED([timecard_week_of] ASC, [customer_id] ASC, [employee_id] ASC, [timecard_day] ASC)
    WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
    ON [PRIMARY];

    IF NOT EXISTS
    (
    SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'RESULTS'
    AND TABLE_SCHEMA = 'dbo'
    )
    CREATE TABLE [dbo].[RESULTS]
    ([id] [INT] IDENTITY(1, 1) NOT NULL,
    [timecard_week_of] [DATE] NULL,
    [customer_id] [INT] NULL,
    [employee_id] [INT] NULL,
    [timecard_day] [DATE] NULL,
    [shift_code] [VARCHAR](10) NULL,
    [hours] [NUMERIC](3, 2) NULL
    )
    ON [PRIMARY];

    SET ANSI_PADDING OFF;
    GO


    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'2ND', CAST(0.50 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'3RD', CAST(5.50 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'3RD', CAST(1.50 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'3RD', CAST(5.50 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'3RD', CAST(1.75 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'3RD', CAST(6.00 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'3RD', CAST(2.00 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'2ND', CAST(0.25 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'3RD', CAST(6.00 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'3RD', CAST(2.00 AS Numeric(3, 2)))
    GO
    INSERT [dbo].[RESULTS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [shift_code], [hours]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'1ST', CAST(1.50 AS Numeric(3, 2)))
    GO

    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'1ST', N'09:00 AM', N'05:30 PM')
    GO
    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'2ND', N'05:30 PM', N'12:30 AM')
    GO
    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (1, N'3RD', N'12:30 AM', N'09:00 AM')
    GO
    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'1ST', N'08:00 AM', N'04:00 PM')
    GO
    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'2ND', N'04:00 PM', N'12:00 AM')
    GO
    INSERT [dbo].[SHIFTS] ([customer_id], [shift_code], [time_in], [time_out]) VALUES (2, N'3RD', N'12:00 AM', N'08:00 AM')


    INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-15' AS Date), N'12:00 AM', N'06:00 AM', N'06:30 AM', N'08:00 AM', NULL, NULL, NULL, NULL)
    GO
    INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-16' AS Date), N'12:15 AM', N'06:00 AM', N'06:30 AM', N'08:15 AM', NULL, NULL, NULL, NULL)
    GO
    INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-17' AS Date), N'12:15 AM', N'06:30 AM', N'07:00 AM', N'09:00 AM', NULL, NULL, NULL, NULL)
    GO
    INSERT [dbo].[TIMECARDS] ([timecard_week_of], [customer_id], [employee_id], [timecard_day], [time_in_1], [time_out_1], [time_in_2], [time_out_2], [time_in_3], [time_out_3], [time_in_4], [time_out_4]) VALUES (CAST(N'2019-04-21' AS Date), 1, 100, CAST(N'2019-04-18' AS Date), N'12:15 AM', N'06:30 AM', N'07:00 AM', N'10:30 AM', NULL, NULL, NULL, NULL)
    GO


    /*
    This is what the results of the "processing"
    should look like
    */
    SELECT * from dbo.RESULTS ORDER BY timecard_week_of, customer_id, employee_id, timecard_day, shift_code


    SET NOEXEC OFF;
  • jonathan.crawford

    SSCertifiable

    Points: 6575

    This isn't right, and you'll want to convert to a function that takes in your punches and test it thoroughly, but it's the time I have at the moment. You can use case statements and dateadd/datediff to do math to get what you need

    DECLARE @InPunch datetime
    DECLARE @outPunch datetime
    DECLARE @dateStart datetime
    SET @InPunch = dateadd(hh,-4,getdate());
    SET @outPunch = getdate()
    SET @dateStart = DATEADD(dd,DATEDIFF(dd,'1/1/1900',@InPunch),'1/1/1900')

    SELECT @InPunch AS inPunch,@outPunch AS outPunch,
    @dateStart AS dateStart,
    --datediff(minute,@dateStart,@InPunch
    --)/60.0 AS timeBeforeStarting,
    CASE
    WHEN dateadd(hh,3,@dateStart) >= @InPunch
    AND @dateStart <= @outPunch
    THEN (
    datediff(minute,dateadd(hh,3,@dateStart),@OutPunch)
    -
    datediff(minute,dateadd(hh,3,@dateStart),@InPunch)
    )
    /60.0
    ELSE 0
    END
    +
    CASE
    WHEN dateadd(hh,24,@dateStart) >= @InPunch
    AND dateadd(hh,17,@dateStart) <= @outPunch
    THEN (
    datediff(minute,dateadd(hh,17,@dateStart),@OutPunch)
    )
    /60.0
    ELSE 0
    END
    AS ThirdShiftHours,
    CASE
    WHEN dateadd(hh,9,@dateStart) >= @InPunch
    AND dateadd(hh,3,@dateStart) <= @outPunch
    THEN (
    datediff(minute,dateadd(hh,9,@dateStart),@OutPunch)
    -
    datediff(minute,dateadd(hh,9,@dateStart),@InPunch)
    )
    /60.0
    ELSE 0
    END AS SecondShiftHours,
    CASE
    WHEN dateadd(hh,17,@dateStart) >= @InPunch
    AND dateadd(hh,9,@dateStart) <= @outPunch
    THEN (
    datediff(minute,dateadd(hh,17,@dateStart),@OutPunch)
    -
    datediff(minute,dateadd(hh,17,@dateStart),@InPunch)
    )
    /60.0
    ELSE 0
    END AS FirstShiftHours


    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Giorgio Vidali

    Ten Centuries

    Points: 1116

    Jonathan,

    Thank you. I have tested the code you have provided and is somewhat similar to what I came up with... but it falls into the same problems... I'll keep working on it...

    Giorgio

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • fahey.jonathan

    Hall of Fame

    Points: 3567

    I keep trying to post on this thread with my solution, but the post is not being shown.  If I post without code, the reply shows.  I then try to add code, and the reply disappears again.  I sent a personal message with my email address, so you can contact me directly so I can send my solution to you.

  • jcelko212 32090

    SSCrazy Eights

    Points: 9012

    >> We have a table that contains “SHIFT CODES”, where each shift code is defined by, you know, a code… and by “time boundaries”. For example, shift code 1ST is “delimited” by the time boundaries “09:00:00’ to “17:00:00” <<

    SQL currently has both a DATE and a TIME data type. This is in addition to the DATETIME2(n) data type.

    CREATE TABLE Shifts

    (shift_code CHAR(3) NOT NULL PRIMARY KEY,

    start_time TIME NOT NULL,

    end_time TIME NOT NULL,

    CHECK (start_time < end_time));

    I’ve left off any constraints to make sure the shifts don’t overlap.

    >> We have another table that contains “EMPLOYEE TIMECARDS” for a certain week ending. The table has room for 4 sets of IN/OUT times – and employees enter their own data. IN and OUT time entered here by the employee may not really match the time boundaries of a certain shift. <<

    are you familiar with the ISO standard weekly date format? Instead of month and day it looks like this: “yyyyWww-d” where yyyy is the year, the W is punctuation, ww is between 01 and 52 or 53, the dash is more punctuation and d is the day (1= Monday, 7= Sunday). You can download calendars in this format and map them to the usual yyyy-mm-dd formatted dates

    >> (Incidentally: no control over data types)<<

    Unfortunately this means you’re screwed. You’re actually keeping temporal data in character strings! You might want to run over him in the parking lot, find out what Cody’s actually written, and then spent a couple of months replacing all of his bad stuff.

    >> It is up to a “process” to parse EACH DAY and EACH IN/OUT block to (possibly) split the total hours over different shifts.

    shift code 1ST is “delimited” by the time boundaries ‘09:00:00’ – ‘17:00:00’ (and there are other codes covering the entire 24 hours day)

    employee enters a time in/out block 08:30:00– 17:30:00

    that block would have to be parsed as something like

    SHIFT CODE 3RD = 0.5 hours

    SHIFT CODE 1ST = 8 hours

    SHIFT CODE 2ND = 0.5 hours

    (we use decimal representation for hours… common in payroll and time tracking) <<

    Many decades ago, I had a similar problem. We rounded the durations to 15 minute blocks (xx:00:00 thru xx:14:59, xx:15:00 thru xx:29:59, xx:30:00 thru xx:44:59, xx:45:00 thru xx:59:59) as per union rules. 24 hrs per day* 4 blocks per hour = 96 blocks per day, 5 days per work week = 480, 50 work weeks per year = 24,000; a decade of lookups = 240,000 rows). Put a running total of blocks in each row, then subtract the starting block count from the ending block count.

    We found that a simple lookup table was easier than trying to do temporal math. After all, SQL is a data language, not a computational language. Use a spreadsheet and load the table, so you are done for a decade.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • fahey.jonathan

    Hall of Fame

    Points: 3567

    WITH	AllShifts
    AS (/* If the start date is before the end date, then the shift crosses a date boundary; adjust either the start or end date as needed, but not both. */
    SELECT s.id,
    s.customer_id,
    s.shift_code,
    /* Shift the end date one day later. */
    --CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_in AS DATETIME) AS shift_start_time,
    --DATEADD(DAY, CASE WHEN CAST(time_out AS TIME) < CAST(time_in AS TIME) THEN 1 ELSE 0 END,
    -- CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_out AS DATETIME)) AS shift_end_time
    /* Shift the start date one day earlier. */
    DATEADD(DAY, CASE WHEN CAST(time_out AS TIME) < CAST(time_in AS TIME) THEN -1 ELSE 0 END,
    CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_in AS DATETIME)) AS shift_start_time,
    CAST(CONVERT(VARCHAR(10), t.timecard_day, 121) + ' ' + s.time_out AS DATETIME) AS shift_end_time
    FROM Shifts s
    CROSS JOIN (
    SELECT DISTINCT timecard_day
    FROM TimeCards
    ) t
    ),
    AllTimeCards
    AS (
    SELECT id, timecard_week_of, customer_id, employee_id, timecard_day,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_1 AS DATETIME) AS time_in,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_1 AS DATETIME) AS time_out
    FROM TimeCards
    WHERE time_in_1 IS NOT NULL

    UNION

    SELECT id, timecard_week_of, customer_id, employee_id, timecard_day,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_2 AS DATETIME) AS time_in,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_2 AS DATETIME) AS time_out
    FROM TimeCards
    WHERE time_in_2 IS NOT NULL

    UNION

    SELECT id, timecard_week_of, customer_id, employee_id, timecard_day,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_3 AS DATETIME) AS time_in,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_3 AS DATETIME) AS time_out
    FROM TimeCards
    WHERE time_in_3 IS NOT NULL

    UNION

    SELECT id, timecard_week_of, customer_id, employee_id, timecard_day,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_in_4 AS DATETIME) AS time_in,
    CAST(CONVERT(VARCHAR(10), timecard_day, 121) + ' ' + time_out_4 AS DATETIME) AS time_out
    FROM TimeCards
    WHERE time_in_4 IS NOT NULL
    )
    SELECT CAST(ROW_NUMBER() OVER (ORDER BY c.timecard_day, c.employee_id, c.time_in) AS INTEGER) AS id,
    c.timecard_week_of,
    c.customer_id,
    c.employee_id,
    c.timecard_day,
    s.shift_code,
    DATEDIFF(MINUTE, CASE WHEN c.time_in > s.shift_start_time THEN c.time_in ELSE s.shift_start_time END, CASE WHEN c.time_out < s.shift_end_time THEN c.time_out ELSE s.shift_end_time END) / 60.0 AS Hours,
    s.shift_start_time,
    s.shift_end_time,
    c.time_in,
    c.time_out

    FROM AllTimeCards c
    JOIN AllShifts s
    ON s.customer_id = c.customer_id
    AND c.time_in <= s.shift_end_time
    AND c.time_out >= s.shift_start_time
    WHERE DATEDIFF(MINUTE, CASE WHEN c.time_in > s.shift_start_time THEN c.time_in ELSE s.shift_start_time END, CASE WHEN c.time_out < s.shift_end_time THEN c.time_out ELSE s.shift_end_time END) > 0
    ORDER BY c.timecard_week_of,
    c.customer_id,
    c.employee_id,
    c.timecard_day,
    s.shift_code,
    c.time_in,
    s.shift_start_time
  • Giorgio Vidali

    Ten Centuries

    Points: 1116

    Thanks for your suggestions and sorry about delay of reply - have been out of office for a couple of days/

    Unfortunately, as good as they are, the suggestions do not really apply to the specific "business case".  To simplify the question, I left out important details - for example the fact that the "shifts" table is filled by our clients with data related to their customers and, as such, can contain thousands of different variations... because shifts can vary by customer, and by customer departments...

    Not to mention the fact that the application I am referring to has been in use for more than 10 years and has generated (is generating) tens of millions in revenues... so... it is a bit difficult to propose dramatic changes. Especially considering that this particular issue just emerged - because of a new "category" of clients... all dealing with specific businesses...

    In any case, thanks again for the suggestions !

     

    Giorgio Vidali

    mailto:giorgio.vidali@abd.net

     

     

  • Jeff Moden

    SSC Guru

    Points: 996665

    Giorgio Vidali wrote:

    Thanks for your suggestions and sorry about delay of reply - have been out of office for a couple of days/ Unfortunately, as good as they are, the suggestions do not really apply to the specific "business case".  To simplify the question, I left out important details - for example the fact that the "shifts" table is filled by our clients with data related to their customers and, as such, can contain thousands of different variations... because shifts can vary by customer, and by customer departments... Not to mention the fact that the application I am referring to has been in use for more than 10 years and has generated (is generating) tens of millions in revenues... so... it is a bit difficult to propose dramatic changes. Especially considering that this particular issue just emerged - because of a new "category" of clients... all dealing with specific businesses... In any case, thanks again for the suggestions !   Giorgio Vidali mailto:giorgio.vidali@abd.net    

    So what is the "new category" of clients and what are the specific business cases that you're trying to solve?  Or is that actually covered in your original post?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply