Click here to Skip to main content
14,272,096 members
   

Database

 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
k505421-Feb-19 7:15
memberk505421-Feb-19 7:15 
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 
given a table job with columns time_utc (datetime) and state (integer), i want to find the periods (start-time and end_time) where the consequetive value of the state column equals 4.
i tried to find the start of the periods with the folllowing query, but no success. too many results.
can someone please help me? i cannot figure it out.

SELECT act_start_time_utc, state_cd FROM SRPMES901007.WWMESDB.dbo.job WHERE act_start_time_utc IS NOT NULL ORDER BY act_start_time_utc ASC

DECLARE @PERIOD TABLE (start_time DATETIME, end_time DATETIME)

INSERT INTO @PERIOD(start_time)
SELECT t.start_time/*, t.new_state, t.last_state*/ 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 MIN(act_start_time_utc) endtime FROM SRPMES901007.WWMESDB.dbo.job INNER JOIN @PERIOD period ON job.act_start_time_utc > period.start_time AND state_cd <> 4)
FROM @PERIOD period

SELECT * FROM @PERIOD ORDER BY start_time ASC


modified 9-Feb-19 16:10pm.

AnswerRe: finding periods Pin
joost.versteegen9-Feb-19 10:40
memberjoost.versteegen9-Feb-19 10:40 
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 

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.