Click here to Skip to main content
14,242,618 members
   

Database

 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661325-Feb-19 4:25
professionalsteve_949661325-Feb-19 4:25 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
Mycroft Holmes8-Feb-19 12:50
memberMycroft Holmes8-Feb-19 12:50 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661310-Feb-19 21:56
professionalsteve_949661310-Feb-19 21:56 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
Mycroft Holmes11-Feb-19 10:53
memberMycroft Holmes11-Feb-19 10:53 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
Richard MacCutchan8-Feb-19 22:44
protectorRichard MacCutchan8-Feb-19 22:44 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661310-Feb-19 21:58
professionalsteve_949661310-Feb-19 21:58 
Questionfinding periods Pin
joost.versteegen8-Feb-19 4:22
memberjoost.versteegen8-Feb-19 4:22 
AnswerRe: finding periods Pin
joost.versteegen9-Feb-19 10:40
memberjoost.versteegen9-Feb-19 10:40 
i finally figured it out, here's the result:

DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)

INSERT INTO @PERIOD(start_time)
SELECT t.start_time FROM (
  SELECT act_start_time_utc AS start_time, state_cd AS new_state, LAG(state_cd, 1) OVER (ORDER BY act_start_time_utc ASC) AS last_state
  FROM SRPMES901007.WWMESDB.dbo.job
  WHERE act_start_time_utc IS NOT NULL) AS t
WHERE t.new_state = 4 AND t.last_state <> 4

UPDATE period
SET end_time = (SELECT TOP(1) act_start_time_utc FROM SRPMES901007.WWMESDB.dbo.job WHERE state_cd <> 4 AND act_start_time_utc > period.start_time ORDER BY act_start_time_utc ASC)
FROM @PERIOD period 

SELECT * FROM @PERIOD ORDER BY start_time ASC

But the query takes 19 seconds, can it be speeded up?

modified 11-Feb-19 4:38am.

Questionupdate temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 3:32
memberjoost.versteegen7-Feb-19 3:32 
AnswerRe: update temp table with results from sub query (actually it is a function) Pin
ZurdoDev7-Feb-19 4:36
professionalZurdoDev7-Feb-19 4:36 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 4:45
memberjoost.versteegen7-Feb-19 4:45 
AnswerRe: update temp table with results from sub query (actually it is a function) Pin
Richard Deeming7-Feb-19 8:34
mveRichard Deeming7-Feb-19 8:34 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 11:20
memberjoost.versteegen7-Feb-19 11:20 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 20:33
memberjoost.versteegen7-Feb-19 20:33 
QuestionTables left and right Pin
Member 141362635-Feb-19 5:21
memberMember 141362635-Feb-19 5:21 
AnswerRe: Tables left and right Pin
Richard MacCutchan5-Feb-19 6:10
protectorRichard MacCutchan5-Feb-19 6:10 
AnswerRe: Tables left and right Pin
ZurdoDev7-Feb-19 4:41
professionalZurdoDev7-Feb-19 4:41 
QuestionUsing JSON functions with Always Encrypted data in SSMS Pin
Hypermommy4-Feb-19 6:06
memberHypermommy4-Feb-19 6:06 
AnswerRe: Using JSON functions with Always Encrypted data in SSMS Pin
Richard Deeming4-Feb-19 9:20
mveRichard Deeming4-Feb-19 9:20 
GeneralRe: Using JSON functions with Always Encrypted data in SSMS Pin
Hypermommy6-Feb-19 2:38
memberHypermommy6-Feb-19 2:38 
Questiondatabase Pin
Member 141284154-Feb-19 6:03
memberMember 141284154-Feb-19 6:03 
AnswerRe: database Pin
Jörgen Andersson4-Feb-19 8:41
protectorJörgen Andersson4-Feb-19 8:41 
QuestionHaving difficulty to replicate entries in SQL query result Pin
paul4everyone28-Jan-19 2:37
memberpaul4everyone28-Jan-19 2:37 
AnswerRe: Having difficulty to replicate entries in SQL query result Pin
Richard MacCutchan28-Jan-19 2:56
protectorRichard MacCutchan28-Jan-19 2:56 
GeneralRe: Having difficulty to replicate entries in SQL query result Pin
paul4everyone28-Jan-19 4:20
memberpaul4everyone28-Jan-19 4:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.