Click here to Skip to main content
14,366,974 members
   

Database

 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
k50548-Feb-19 8:21
memberk50548-Feb-19 8:21 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661310-Feb-19 22:30
professionalsteve_949661310-Feb-19 22:30 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
k505413-Feb-19 8:14
memberk505413-Feb-19 8:14 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661320-Feb-19 21:55
professionalsteve_949661320-Feb-19 21:55 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
k505421-Feb-19 8:15
memberk505421-Feb-19 8:15 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661325-Feb-19 5:25
professionalsteve_949661325-Feb-19 5:25 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
Mycroft Holmes8-Feb-19 13:50
memberMycroft Holmes8-Feb-19 13:50 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661310-Feb-19 22:56
professionalsteve_949661310-Feb-19 22:56 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
Mycroft Holmes11-Feb-19 11:53
memberMycroft Holmes11-Feb-19 11:53 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
Richard MacCutchan8-Feb-19 23:44
protectorRichard MacCutchan8-Feb-19 23:44 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
steve_949661310-Feb-19 22:58
professionalsteve_949661310-Feb-19 22:58 
Questionfinding periods Pin
joost.versteegen8-Feb-19 5:22
memberjoost.versteegen8-Feb-19 5: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 11:40
memberjoost.versteegen9-Feb-19 11:40 
Questionupdate temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 4:32
memberjoost.versteegen7-Feb-19 4:32 
AnswerRe: update temp table with results from sub query (actually it is a function) Pin
ZurdoDev7-Feb-19 5:36
professionalZurdoDev7-Feb-19 5:36 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 5:45
memberjoost.versteegen7-Feb-19 5:45 
AnswerRe: update temp table with results from sub query (actually it is a function) Pin
Richard Deeming7-Feb-19 9:34
mveRichard Deeming7-Feb-19 9:34 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 12:20
memberjoost.versteegen7-Feb-19 12:20 
GeneralRe: update temp table with results from sub query (actually it is a function) Pin
joost.versteegen7-Feb-19 21:33
memberjoost.versteegen7-Feb-19 21:33 
QuestionTables left and right Pin
Member 141362635-Feb-19 6:21
memberMember 141362635-Feb-19 6:21 
AnswerRe: Tables left and right Pin
Richard MacCutchan5-Feb-19 7:10
protectorRichard MacCutchan5-Feb-19 7:10 
AnswerRe: Tables left and right Pin
ZurdoDev7-Feb-19 5:41
professionalZurdoDev7-Feb-19 5:41 
QuestionUsing JSON functions with Always Encrypted data in SSMS Pin
Hypermommy4-Feb-19 7:06
memberHypermommy4-Feb-19 7:06 
AnswerRe: Using JSON functions with Always Encrypted data in SSMS Pin
Richard Deeming4-Feb-19 10:20
mveRichard Deeming4-Feb-19 10:20 
GeneralRe: Using JSON functions with Always Encrypted data in SSMS Pin
Hypermommy6-Feb-19 3:38
memberHypermommy6-Feb-19 3:38 

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.