Time increments

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 7:39 am

  #327935

  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

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  August 11, 2017 at 7:46 am

  #1954973

  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?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 11, 2017 at 7:55 am

  #1954977

  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

• Thom A

  SSC Guru

  Points: 99130

  More actions

  August 11, 2017 at 8:00 am

  #1954978

  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

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  August 11, 2017 at 8:21 am

  #1954987

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

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 8:22 am

  #1954988

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 11, 2017 at 8:27 am

  #1954990

  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

• Thom A

  SSC Guru

  Points: 99130

  More actions

  August 11, 2017 at 8:36 am

  #1954994

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 9:32 am

  #1955010

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 9:38 am

  #1955011

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

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  August 11, 2017 at 9:53 am

  #1955015

  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)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 9:58 am

  #1955017

  Computed column based on the sort the conditions 1 and 2

• Thom A

  SSC Guru

  Points: 99130

  More actions

  August 11, 2017 at 10:10 am

  #1955019

  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

• PSB

  SSChampion

  Points: 13195

  More actions

  August 11, 2017 at 10:36 am

  #1955027

  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.

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  August 11, 2017 at 10:41 am

  #1955029

  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.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin