Click here to Skip to main content
14,669,072 members
Home » Discussions » Database
   

Database

 
GeneralRe: SQLite how to delete rows in a table (C++) Pin
k505421-Feb-19 7:15
mvek505421-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
professionalMycroft 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
professionalMycroft Holmes11-Feb-19 10:53 
AnswerRe: SQLite how to delete rows in a table (C++) Pin
Richard MacCutchan8-Feb-19 22:44
mveRichard 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
mveZurdoDev7-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
mveRichard MacCutchan5-Feb-19 6:10 
AnswerRe: Tables left and right Pin
ZurdoDev7-Feb-19 4:41
mveZurdoDev7-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
professionalJö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
mveRichard 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.