Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Where clause with conditions

    Code:
    Drop table #table
    Drop table #table_with_groupid
    -- Prepare test data
    CREATE TABLE #table
    (	[Admissions_key] bigint NOT NULL PRIMARY KEY,
    MRN nvarchar(10) NOT NULL,
    hosp_code nvarchar(10) NOT NULL,
    adm_datetime datetime NOT NULL,
    sep_datetime datetime NOT NULL,
    Sequence nvarchar(10) NOT NULL
    )
    
    SET DATEFORMAT DMY
    INSERT INTO #table(	Admissions_key,MRN, hosp_code, adm_datetime, sep_datetime,Sequence)
    VALUES
    (7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First'), 
    (7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle'), 
    (7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle'),
    (7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First'),
    (7099226,'0000544607','0003','27/07/2011 19:31','28/07/2011 11:30','Final'),
    (7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First'),
    (7099228,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle'),
    (7099229,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final'),
    (7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle'),
    (7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final'),
    (7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle'),
    (7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final'),
    (7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First'),
    (7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First'),
    (7099236,'0000446435','0003','12/08/2011 12:09','12/08/2011 14:58','Final')
    
    
    ;WITH cur_prev AS 
    (	-- Match current row with row above
    
    	SELECT cur.*, prev_Sequence = prev.Sequence, pre_sep_datetime = prev.sep_datetime 
    	   FROM #table cur
    		OUTER APPLY -- we use OUTER APPLY instead of CROSS APPLY to get a row even if previous row does not exist
    		(	-- Find previous row to current row.
    			SELECT TOP 1 tt.*
    			FROM #table tt
    			WHERE tt.MRN = cur.MRN AND tt.hosp_code = cur.hosp_code -- within the same group
    				and -- only rows above
    				(	tt.adm_datetime < cur.adm_datetime
    					OR tt.adm_datetime = cur.adm_datetime AND tt.Admissions_key < cur.Admissions_key
    				)
    			ORDER BY tt.adm_datetime DESC, tt.Admissions_key DESC -- sort should be unique, so we added a PK column
    		) prev
    )
    
    SELECT c.*,
    	GroupID =
    	(	-- Find the first row above that starts a group
    		SELECT TOP 1 cc.Admissions_key
    		FROM cur_prev cc
    		WHERE cc.MRN = c.MRN AND cc.hosp_code = c.hosp_code -- within the same group
    			AND -- only rows above, but this time INCLUDING current row!
    			(	cc.adm_datetime < c.adm_datetime
    				OR cc.adm_datetime = c.adm_datetime AND cc.Admissions_key <= c.Admissions_key
    			)
    
    		ORDER BY CASE WHEN cc.Sequence='First' OR cc.prev_Sequence='Final' THEN 0 ELSE 1 END, -- first try to find a row that starts a group
    			cc.adm_datetime DESC, cc.Admissions_key DESC -- sort should be unique, so we added a PK column
    	)
    INTO #table_with_groupid
    FROM cur_prev c
    
    SELECT * FROM #table_with_groupid
    
    
    SELECT t.*, g.IsGroupCorrect   
    FROM #table_with_groupid t
    	LEFT JOIN
    	(	-- Find which group is correct and which is not
    		SELECT tg.GroupID, 
    			IsGroupCorrect = CASE -- correct is group that have 'First' and 'Final' in it.
    				WHEN MAX(CASE WHEN tg.Sequence='First' THEN 1 ELSE 0 END)=1 AND MAX(CASE WHEN tg.Sequence='Final' THEN 1 ELSE 0 END)=1 THEN 'C'
    				ELSE 'E'
    			END
    		FROM #table_with_groupid tg
    	--where  datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1	
    		GROUP BY tg.GroupID
    	) g on t.GroupID = g.GroupID




    How can I compare dates with conditions.
    I only want to Mark C where the difference between adm_datetime and prevsep_datetime is <= 1 otherwise E as well

    where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1 ??
    is it correct ? where should I put this to implement correctly ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not really sure what you are trying to accomplish, but in tinkering with your code I came up with:
    Code:
    Drop table #table
    --  Drop table #table_with_groupid
    
    -- Prepare test data
    CREATE TABLE #table (
       [Admissions_key]	bigint          NOT NULL
       PRIMARY KEY
    ,  MRN              nvarchar(10)    NOT NULL
    ,  hosp_code        nvarchar(10)    NOT NULL
    ,  adm_datetime     datetime        NOT NULL
    ,  sep_datetime     datetime        NOT NULL
    ,  Sequence         nvarchar(10)    NOT NULL
       )
    
    SET DATEFORMAT DMY
    
    INSERT INTO #table(
       Admissions_key, MRN, hosp_code
    ,  adm_datetime, sep_datetime,Sequence)
       VALUES
       (7099222,'0000544607','0003','22/07/2011 04:55','22/07/2011 10:44','First')
    ,  (7099223,'0000544607','0003','22/07/2011 10:45','25/07/2011 19:43','Middle')
    ,  (7099224,'0000544607','0003','25/07/2011 19:44','26/07/2011 11:29','Middle')
    ,  (7099225,'0000544607','0003','27/07/2011 13:30','27/07/2011 19:30','First')
    ,  (7099226,'0000544607','0003','27/07/2011 19:31','28/07/2011 11:30','Final')
    ,  (7099227,'0000559282','0003','03/07/2011 22:50','03/07/2011 23:51','First')
    ,  (7099228,'0000559282','0003','03/07/2011 23:52','04/07/2011 15:30','Middle')
    ,  (7099229,'0000559282','0003','04/07/2011 15:31','04/07/2011 17:59','Final')
    ,  (7099230,'0000559282','0003','05/07/2011 18:00','05/07/2011 18:05','Middle')
    ,  (7099231,'0000559282','0003','05/07/2011 18:06','09/07/2011 14:58','Final')
    ,  (7099232,'0000999271','0003','07/08/2011 01:00','07/08/2011 18:05','Middle')
    ,  (7099233,'0000999271','0003','07/08/2011 18:06','09/08/2011 14:58','Final')
    ,  (7099234,'0000999271','0003','10/08/2011 18:00','10/08/2011 18:05','First')
    ,  (7099235,'0000446435','0003','11/08/2011 18:06','12/08/2011 12:08','First')
    ,  (7099236,'0000446435','0003','12/08/2011 12:09','12/08/2011 14:58','Final')
    
    SELECT 
       CASE
          WHEN DATEDIFF(minute, t.adm_datetime
    ,        p.sep_datetime) BETWEEN -1 AND 1 THEN 'C'
          ELSE 'E'
       END AS Mark
    ,  CASE
          WHEN p.MRN IS NULL THEN 'First'
          WHEN n.MRN IS NULL THEN 'Final'
          ELSE 'Middle'
       END AS 'New Seq'
    ,  Row_Number() OVER (
          PARTITION BY t.MRN
    ,        t.hosp_code
          ORDER BY t.adm_datetime
    ,        t.Admissions_key) AS event_row
    ,  Row_Number() OVER (ORDER BY t.adm_datetime
    ,     t.[Admissions_key]) AS gross_row
    ,  t.*, p.*, n.*
       FROM #table AS t
       LEFT JOIN #table AS n  -- next row in group
          ON (n.MRN = t.MRN
          AND n.hosp_code = t.hosp_code
          AND n.adm_datetime = (SELECT MIN(z1.adm_datetime)
             FROM #table AS z1
             WHERE  z1.MRN = t.MRN
                AND z1.hosp_code = t.hosp_code
                AND z1.adm_datetime > t.adm_datetime))
       LEFT JOIN #table AS p  --  prev row in group
          ON (p.MRN = t.MRN
          AND p.hosp_code = t.hosp_code
          AND p.adm_datetime = (SELECT Max(z2.adm_datetime)
             FROM #table AS z2
             WHERE  z2.MRN = t.MRN
                AND z2.hosp_code = t.hosp_code
                AND z2.adm_datetime < t.adm_datetime))
       ORDER BY 4
    Does any of this help?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •