Time increments

  • Hi,

    I want to have a calculated field "TimeIncrements' that will increment 20 minutes starting at 8:00 and ending at 5:00 using sort order FieldSort1 ,FieldSort2  and FieldSort3

    CREATE TABLE #TimeIncrements

    (

    FieldSort1 VARCHAR(100),

    FieldSort2 VARCHAR(100),

    FieldSort3 VARCHAR(100),

    TimeIncrements VARCHAR(20)

    )

    INSERT INTO #TimeIncrements ( FieldSort1,FieldSort2,FieldSort3 )

    SELECT 'Scenario 1','00 Set',' TC1' UNION

    SELECT 'Scenario 1','01 Set',' TC2' UNION

    SELECT 'Scenario 1','02 Set',' TC3' UNION

    SELECT 'Scenario 1','03 Set',' TC4' UNION

    SELECT 'Scenario 1','04 Set',' TC5' UNION

    SELECT 'Scenario 2','00 Set 2',' TC1' UNION

    SELECT 'Scenario 2','01 Set 3',' TC2' UNION

    SELECT 'Scenario 2','02 Set 3',' TC3' UNION

    SELECT 'Scenario 2','03 Set 4',' TC4' UNION

    SELECT 'Scenario 2','04 Set 5',' TC5' UNION

    SELECT 'Scenario 2','00 Set 6',' TC6' UNION

    SELECT 'Scenario 2','01 Set 7',' TC7' UNION

    SELECT 'Scenario 2','02 Set 8',' TC8' UNION

    SELECT 'Scenario 2','03 Set 9',' TC9' UNION

    SELECT 'Scenario 2','04 Set 10','TC10'

    SELECT * FROM #TimeIncrements

    --Results

    SELECT 'Scenario 1','00 Set',' TC1','8:00' UNION

    SELECT 'Scenario 1','01 Set',' TC2','8:20' UNION

    SELECT 'Scenario 1','02 Set',' TC3' ,'8:40' UNION

    SELECT 'Scenario 1','03 Set',' TC4','9:00' UNION

    SELECT 'Scenario 1','04 Set',' TC5','9:20' UNION

    SELECT 'Scenario 2','00 Set 2',' TC1' ,'8:00' UNION

    SELECT 'Scenario 2','01 Set 3',' TC2' ,'8:20' UNION

    SELECT 'Scenario 2','02 Set 3',' TC3' ,'8:40' UNION

    SELECT 'Scenario 2','03 Set 4',' TC4' ,'9:00' UNION

    SELECT 'Scenario 2','04 Set 5',' TC5' ,'9:20' UNION

    SELECT 'Scenario 2','00 Set 6',' TC6' ,'9:40' UNION

    SELECT 'Scenario 2','01 Set 7',' TC7' ,'10:00' UNION

    SELECT 'Scenario 2','02 Set 8',' TC8' ,'10:20' UNION

    SELECT 'Scenario 2','03 Set 9',' TC9' ,'10:40' UNION

    SELECT 'Scenario 2','04 Set 10','TC10','11:00'

    DROP TABLE #TimeIncrements

    Thanks,
    PSB

  • Please define the calculation.
    How do you get from 'Scenario 1','00 Set',' TC1' to '8:00'?
    Does TC1 always correspond with '8:00'? If so, why not just create a lookup table & join on FieldSort3?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • WITH Numbered AS (
        SELECT
             FieldSort1
        ,    FieldSort2
        ,    FieldSort3
        ,    ROW_NUMBER() OVER (PARTITION BY FieldSort1 ORDER BY FieldSort2, FieldSort3) AS RowNo
        FROM #TimeIncrements
        )
    SELECT
         FieldSort1
    ,    FieldSort2
    ,    FieldSort3
    ,    CAST(DATEADD(minute,20 * (RowNo - 1),'19000101 08:00') AS time) AS TimeIncrements
    FROM Numbered;

    John

  • This is one solution. One point, if you are storing a time, store it as a time not a varchar, then you can do date math on it, without having to implicitly cast it.
    WITH Times AS (
      SELECT FieldSort1, FieldSort2, FieldSort3,
             CAST(DATEADD(MINUTE, ((ROW_NUMBER() OVER (PARTITION BY FieldSort1 ORDER BY FieldSort1, FieldSort2, FieldSort3) - 1) * 20), '08:00') AS Time) AS TimeIncrement
      FROM #TimeIncrements)
    UPDATE #TimeIncrements
    SET TimeIncrements = T.TimeIncrement
    FROM Times T
    WHERE T.FieldSort1 = #TimeIncrements.FieldSort1
    AND T.FieldSort2 = #TimeIncrements.FieldSort2
    AND T.FieldSort3 = #TimeIncrements.FieldSort3;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • John, Thom, these are not 'calculated fields'. Or perhaps the OP used the wrong term.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • FieldSort1 and FieldSort2 should be sorted alphabetically and FielSort2 always has a number in front . So the correct sort order is FieldSort1,FieldSort2 and FieldSort3

    CREATE TABLE #TimeIncrements

    (

    FieldSort1 VARCHAR(100),

    FieldSort2 VARCHAR(100),

    FieldSort3 VARCHAR(100),

    TimeIncrements VARCHAR(20)

    )

    INSERT INTO #TimeIncrements ( FieldSort1,FieldSort2,FieldSort3 )

    SELECT 'Scenario 1','00 Set',' AC1' UNION

    SELECT 'Scenario 1','01 Set',' BC2' UNION

    SELECT 'Scenario 1','02 Set',' CC3' UNION

    SELECT 'Scenario 1','03 Set',' DC4' UNION

    SELECT 'Scenario 1','04 Set',' EC5' UNION

    SELECT 'Scenario 2','00 Set 2',' AC2' UNION

    SELECT 'Scenario 2','01 Set 3',' BC2' UNION

    SELECT 'Scenario 2','02 Set 4',' CC3' UNION

    SELECT 'Scenario 2','03 Set 5',' DC4' UNION

    SELECT 'Scenario 2','04 Set 6',' EC5' UNION

    SELECT 'Scenario 2','05 Set 7',' FC6' UNION

    SELECT 'Scenario 2','06 Set 8',' GC7' UNION

    SELECT 'Scenario 2','07 Set 9',' HC8' UNION

    SELECT 'Scenario 2','08 Set 10',' IC9' UNION

    SELECT 'Scenario 2','09 Set 11','JC10'

    SELECT * FROM #TimeIncrements ORDER BY 1,2,3

    --Results

    SELECT 'Scenario 1','00 Set',' AC1','8:00' UNION

    SELECT 'Scenario 1','01 Set',' BC2','8:20' UNION

    SELECT 'Scenario 1','02 Set',' CC3' ,'8:40' UNION

    SELECT 'Scenario 1','03 Set',' DC4','9:00' UNION

    SELECT 'Scenario 1','04 Set',' EC5','9:20' UNION

    SELECT 'Scenario 2','00 Set 2',' AC2' ,'8:00' UNION

    SELECT 'Scenario 2','01 Set 3',' BC2' ,'8:20' UNION

    SELECT 'Scenario 2','02 Set 4',' CC3' ,'8:40' UNION

    SELECT 'Scenario 2','03 Set 5',' DC4' ,'9:00' UNION

    SELECT 'Scenario 2','04 Set 6',' EC5' ,'9:20' UNION

    SELECT 'Scenario 2','05 Set 7',' FC6' ,'9:40' UNION

    SELECT 'Scenario 2','06 Set 8',' GC7' ,'10:00' UNION

    SELECT 'Scenario 2','07 Set 9',' HC8' ,'10:20' UNION

    SELECT 'Scenario 2','08 Set 10',' IC9' ,'10:40' UNION

    SELECT 'Scenario 2','09 Set 11','JC10','11:00'

    ;WITH Numbered AS (

    SELECT

    FieldSort1

    , FieldSort2

    , FieldSort3

    , ROW_NUMBER() OVER (PARTITION BY FieldSort1 ORDER BY FieldSort2, FieldSort3) AS RowNo

    FROM #TimeIncrements

    )

    SELECT

    FieldSort1

    , FieldSort2

    , FieldSort3

    , CAST(DATEADD(minute,20 * (RowNo - 1),'19000101 08:00') AS time) AS TimeIncrements

    FROM Numbered;

    DROP TABLE #TimeIncrements

  • Phil Parkin - Friday, August 11, 2017 8:21 AM

    John, Thom, these are not 'calculated fields'. Or perhaps the OP used the wrong term.

    Good point, Phil, although I don't think "calculated field" is defined anywhere, is it?  Maybe he means computed column, in which case it's not possible, since you can only compute the value from other values in the same row.  A view would be the closest he could get.

    John

  • Phil Parkin - Friday, August 11, 2017 8:21 AM

    John, Thom, these are not 'calculated fields'. Or perhaps the OP used the wrong term.

    Hmm, you're right, the OP does say they want a "Calculated field", but what also threw me is the OP declares the column [TimeIncrements] in the DDL (as a varchar). If they wanted it computed, then why does it already exist as a static column?

    Now I'm not actually sure if the OP wants to calculate and insert, or if they require a computed column (which, like John said, you can't do, so best bet would be a VIEW).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Condition 1)

    If FieldSort1 is a large, and the TimeIncrement runs past 4:40 and there are still more values for FieldSort1 , just move on tothe next day (8am) (StartDate + 1 ) in the TimeIncrement field

    SELECT 'Scenario 2','27 Set 29',' IC9' ,'2017-08-12 08:00:00.000' ,'2017-08-11',5 ---- New date as we ecxceeded 14:40

    2) 

    the clock resets back to 8am when you move on to a new FieldSort1

    CREATE TABLE #TimeIncrements

    (

    FieldSort1 VARCHAR(100),

    FieldSort2 VARCHAR(100),

    FieldSort3 VARCHAR(100),

    StartDate DATETIME,

    Duration INT,

    TimeIncrements VARCHAR(20)

    )

    INSERT INTO #TimeIncrements ( FieldSort1,FieldSort2,FieldSort3,StartDate,Duration )

    SELECT 'Scenario 1','00 Set',' AC1','2017-08-09',4 UNION

    SELECT 'Scenario 1','01 Set',' BC2','2017-08-09',4 UNION

    SELECT 'Scenario 1','02 Set',' CC3','2017-08-09',4 UNION

    SELECT 'Scenario 1','03 Set',' DC4','2017-08-09',4 UNION

    SELECT 'Scenario 1','04 Set',' EC5','2017-08-09',4 UNION

    SELECT 'Scenario 2','00 Set 2',' AC2' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','01 Set 3',' BC2' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','02 Set 4',' CC3' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','03 Set 5',' DC4' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','04 Set 6',' EC5' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','05 Set 7',' FC6' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','06 Set 8',' GC7' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','07 Set 9',' HC8' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','08 Set 10',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','09 Set 11','JC10','2017-08-11',5 UNION

    SELECT 'Scenario 2','10 Set 12',' AC2' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','11 Set 13',' BC2' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','12 Set 14',' CC3' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','13 Set 15',' DC4' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','14 Set 16',' EC5' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','15 Set 17',' FC6' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','16 Set 18',' GC7' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','17 Set 19',' HC8' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','18 Set 20',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','19 Set 21','JC10','2017-08-11',5 UNION

    SELECT 'Scenario 2','20 Set 22',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','21 Set 23','JC10','2017-08-11',5 UNION

    SELECT 'Scenario 2','22 Set 24',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','23 Set 25','JC10','2017-08-11',5 UNION

    SELECT 'Scenario 2','24 Set 26',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','25 Set 27','JC10','2017-08-11',5 UNION

    SELECT 'Scenario 2','26 Set 28',' IC9' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','27 Set 29',' IC9' ,'2017-08-11',5

    SELECT * FROM #TimeIncrements ORDER BY 1,2,3

    --Results

    SELECT 'Scenario 1','00 Set',' AC1','2017-08-09 08:00:00.000','2017-08-09',4 UNION

    SELECT 'Scenario 1','01 Set',' BC2','2017-08-09 08:20:00.000','2017-08-09',4 UNION

    SELECT 'Scenario 1','02 Set',' CC3' ,'2017-08-09 08:40:00.000','2017-08-09',4 UNION

    SELECT 'Scenario 1','03 Set',' DC4','2017-08-09 09:00:00.000','2017-08-09',4 UNION

    SELECT 'Scenario 1','04 Set',' EC5','2017-08-09 09:20:00.000','2017-08-09',4 UNION

    SELECT 'Scenario 2','00 Set 2',' AC2' ,'2017-08-11 08:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','01 Set 3',' BC2' ,'2017-08-11 08:20:00.000' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','02 Set 4',' CC3' ,'2017-08-11 08:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','03 Set 5',' DC4' ,'2017-08-11 09:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','04 Set 6',' EC5' ,'2017-08-11 09:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','05 Set 7',' FC6' ,'2017-08-11 09:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','06 Set 8',' GC7' ,'2017-08-11 10:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','07 Set 9',' HC8' ,'2017-08-11 10:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','08 Set 10',' IC9' ,'2017-08-11 10:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','09 Set 11','JC10','2017-08-11 11:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','10 Set 12',' AC2' ,'2017-08-11 11:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','11 Set 13',' BC2' ,'2017-08-11 11:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','12 Set 14',' CC3' ,'2017-08-11 12:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','13 Set 15',' DC4' ,'2017-08-11 12:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','14 Set 16',' EC5' ,'2017-08-11 12:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','15 Set 17',' FC6' ,'2017-08-11 13:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','16 Set 18',' GC7' ,'2017-08-11 13:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','17 Set 19',' HC8' ,'2017-08-11 13:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','18 Set 20',' IC9' ,'2017-08-11 14:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','19 Set 21','JC10','2017-08-11 14:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','20 Set 22',' IC9' ,'2017-08-11 14:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','21 Set 23','JC10','2017-08-11 15:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','22 Set 24',' IC9' ,'2017-08-11 15:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','23 Set 25','JC10','2017-08-11 15:40:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','24 Set 26',' IC9' ,'2017-08-11 16:00:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','25 Set 27','JC10', '2017-08-11 16:20:00.000','2017-08-11',5 UNION

    SELECT 'Scenario 2','26 Set 28',' IC9' ,'2017-08-11 16:40:00.000' ,'2017-08-11',5 UNION

    SELECT 'Scenario 2','27 Set 29',' IC9' ,'2017-08-12 08:00:00.000' ,'2017-08-11',5 ---- New date as we ecxceeded 14:40 timestamp for the day

    Thanks,
    PSB

  • TimeIncrement field should be datetime . sorry for the wrong information .

  • Why are you refusing to answer the question about how this new column is calculated? Options suggested so far.
    1) A view
    2) An UPDATE query
    3) A computed column (as suggested by your first post)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Computed column based on the sort the conditions 1 and 2

  • PSB - Friday, August 11, 2017 9:58 AM

    Computed column based on the sort the conditions 1 and 2

    As John as pointed out, a Computed Column won't work here. You can't reference other rows in the table in a computed column, only the row itself (any others are out of scope). This leaves you with the former 2 options:
    1. A View
    2. An update statement

    A view will enable you to always get the latest correct result, but you will need to reference a different object. An update statement will store the values in your table, but they will be static. If you, therefore, make changes make changes to any data (or insert), you'll need to ensure that the update statement is rerun at some point before you use the data to get the results you want.

    A view, in my opinion, would be a better option. Otherwise you might have to consider putting a trigger on the table, or similar, to ensure that the TimeIncrements column is always up to date.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • I will be using a stored procedure and it has a temp table . I would like to add the TimeIncrement field at the final select query of the reporting procedure.

  • Thom A - Friday, August 11, 2017 10:10 AM

    PSB - Friday, August 11, 2017 9:58 AM

    Computed column based on the sort the conditions 1 and 2

    As John as pointed out, a Computed Column won't work here. You can't reference other rows in the table in a computed column, only the row itself (any others are out of scope). This leaves you with the former 2 options:
    1. A View
    2. An update statement

    A view will enable you to always get the latest correct result, but you will need to reference a different object. An update statement will store the values in your table, but they will be static. If you, therefore, make changes make changes to any data (or insert), you'll need to ensure that the update statement is rerun at some point before you use the data to get the results you want.

    A view, in my opinion, would be a better option. Otherwise you might have to consider putting a trigger on the table, or similar, to ensure that the TimeIncrements column is always up to date.

    Thom, this is not 100% correct. A computed column can include a function call which can reference other rows in the table (see here, for example).
    The performance is likely to be awful if there are many rows in the table, however.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 32 total)

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