none
DATEADD and MAX() not working unless date is hardcoded

    Question

  • Can someone help me understand why using line 13 in the following code returns the wrong LAST_ENCOUNTER_DATE but lines 14 or 15 returns the correct date?  I cannot figure out why the hardcoded date or datetime works but not the item BTG_INSTANT.  I am running this code using SQL Server Mgmt Studio 2012.  CONTACT_DATE and BTG_INSTANT datatype is DATETIME.  I need to find the last CONTACT_DATE that is less than BTG_INSTANT.

    select DISTINCT BTG.PAT_ID AS 'PAT ID'
    ,II.IDENTITY_ID AS 'BTG Patient MRN'
    ,BTG.LINE AS 'BTG LINE'
    ,BTG.BTG_INSTANT AS 'BTG INSTANT'
    ,LAST_ENC.LAST_ENCOUNTER_DATE AS 'LAST CONTACT DATE'
    from PAT_BTG_AUDIT BTG
    	INNER JOIN IDENTITY_ID II ON BTG.PAT_ID = II.PAT_ID AND II.IDENTITY_TYPE_ID = 1
    	LEFT JOIN PATIENT EPT ON BTG.PAT_ID = EPT.PAT_ID
    	LEFT JOIN (SELECT PE.PAT_ID 
    		  ,MAX(PE.CONTACT_DATE) AS LAST_ENCOUNTER_DATE
    			   FROM PAT_BTG_AUDIT PBA 
    					INNER JOIN PAT_ENC PE ON PBA.PAT_ID = PE.PAT_ID
    --LINE 13				AND PE.CONTACT_DATE < DATEADD(MM,@Months,PBA.BTG_INSTANT)
    --LINE 14				AND PE.CONTACT_DATE < DATEADD(MM,@Months,'2014-03-02 13:53:00')
    --LINE 15				AND PE.CONTACT_DATE < DATEADD(MM,@Months,'2014-03-02')
    			   GROUP BY PE.PAT_ID) LAST_ENC ON EPT.PAT_ID = LAST_ENC.PAT_ID
    WHERE CAST(BTG.BTG_INSTANT AS DATE) BETWEEN @BeginDate and @EndDate
    	AND BTG.PAT_ID NOT IN (SELECT ENC.PAT_ID FROM PAT_ENC ENC
    			       WHERE (ENC.CONTACT_DATE >= DATEADD(MM,@Months,BTG.BTG_INSTANT)
    					AND ENC.CONTACT_DATE <= BTG.BTG_INSTANT)
    				--EXCLUDE ENCOUNTER TYPES
    				--5 Canceled
    				--10 Empty
    				--151 Erroneous Encounter
    				OR ENC.ENC_TYPE_C IN (5,10,151))
    AND II.IDENTITY_ID = '1325183'
    ORDER BY BTG.PAT_ID, BTG.BTG_INSTANT, BTG.LINE
    

    Thursday, April 10, 2014 7:32 PM

Answers

  • I finally figured out the right code for my sub-query which gives me the maximum encounter date that is less than the BTG INSTANT.

    DECLARE @BeginDate AS DATE
    DECLARE @EndDate AS DATE
    DECLARE @RunTimeOption AS VarChar(25)
    DECLARE @CurrDate DATE
    DECLARE @Months AS INT
    
    SET @CurrDate = CAST(CURRENT_TIMESTAMP AS DATE)
    SET @BeginDate = '2014-01-01'
    SET @EndDate = '2014-03-31'
    SET @RunTimeOption = 'Last Full Month'
    SET @Months = 6
    SET @Months = @Months * -1
    
    
    If @RunTimeOption = 'Last Full Week'
    	BEGIN
    	--LAST FULL WEEK - SATURDAY THRU SUNDAY
    	SET @BeginDate = DATEADD(ww,DATEDIFF(ww,0,@CurrDate)-1,-1) 
    	SET @EndDate = DATEADD(ww,DATEDIFF(ww,0,@CurrDate),-2)
    	END
    ELSE
    	IF @RunTimeOption = 'Last Full Month'
    	BEGIN
    	--LAST FULL MONTH
    	SET @BeginDate = DATEADD(mm,DATEDIFF(mm,0,@CurrDate)-1,0) 
    	SET @EndDate = DATEADD(mm,DATEDIFF(mm,0,@CurrDate),-1)
    	END
    
    select DISTINCT BTG.PAT_ID AS 'PAT ID'
    ,EPT.PAT_NAME AS 'BTG Patient Name'
    ,II.IDENTITY_ID AS 'BTG Patient MRN'
    ,ZPT.TITLE AS 'BTG Patient Type'
    ,BTG.LINE AS 'BTG LINE'
    ,EMP.NAME AS 'BTG User Name'
    ,BTG.USER_ID AS 'BTG User Record ID'
    ,EMP.SYSTEM_LOGIN AS 'BTG User Network ID'
    ,BTG.USER_LOGON_DEPT_ID AS 'BTG USER Logged On DEPT ID' --DEP ID of the department the user was actually logged in to when Breaking the Glass
    ,DEP1.DEPARTMENT_NAME AS 'BTG USER Logged On DEPT Name'
    ,EMP.LGIN_DEPARTMENT_ID as 'BTG User Default Login DEPT ID' --EMP Hyperspace Default Login DEP (Item 20660)
    ,DEP2.DEPARTMENT_NAME AS 'BTG User Default Login DEPT Name'
    ,EMP.PROV_ID AS 'BTG USER SER ID'
    ,BTG.USER_DEPT_ID AS 'BTG User Provider Default DEPT ID' --DEP ID of the user's default login department (first department listed in SER)
    ,DEP3.DEPARTMENT_NAME AS 'BTG User Provider Default DEPT Name'
    ,BTG.BTG_INSTANT AS 'BTG INSTANT'
    ,CAST(BTG.BTG_INSTANT AS Date) AS 'BTG Event Date'
    ,CAST(BTG.BTG_INSTANT AS Time) AS 'BTG Event Time'
    ,BTG.BTG_REASON_C AS 'BTG REASON CODE'
    ,ZBR.TITLE AS 'BTG Reason'
    ,BTG.BTG_EXPLANATION AS 'BTG Explanation'
    ,LAST_ENC.LAST_ENCOUNTER_DATE AS 'Last Encounter Date'
    from PAT_BTG_AUDIT BTG
    	INNER JOIN IDENTITY_ID II ON BTG.PAT_ID = II.PAT_ID AND II.IDENTITY_TYPE_ID = 1
    	INNER JOIN PATIENT_TYPE PT ON BTG.PAT_ID = PT.PAT_ID
    	LEFT JOIN PATIENT EPT ON BTG.PAT_ID = EPT.PAT_ID
    	LEFT JOIN ZC_PATIENT_TYPE ZPT ON PT.PATIENT_TYPE_C = ZPT.PATIENT_TYPE_C
    	LEFT JOIN CLARITY_EMP EMP ON BTG.USER_ID = EMP.USER_ID
    	LEFT JOIN ZC_BTG_REASON ZBR ON BTG.BTG_REASON_C = ZBR.BTG_REASON_C
    	LEFT JOIN CLARITY_DEP DEP1 ON BTG.USER_LOGON_DEPT_ID = DEP1.DEPARTMENT_ID
    	LEFT JOIN CLARITY_DEP DEP2 ON EMP.LGIN_DEPARTMENT_ID = DEP2.DEPARTMENT_ID
    	LEFT JOIN CLARITY_SER_DEPT CSD ON EMP.PROV_ID = CSD.PROV_ID AND CSD.LINE = 1
    	LEFT JOIN CLARITY_DEP DEP3 ON CSD.DEPARTMENT_ID = DEP3.DEPARTMENT_ID
    	LEFT JOIN PATIENT_3 EPT3 ON EPT.PAT_ID = EPT3.PAT_ID
    	LEFT JOIN (SELECT PE.PAT_ID
    				,PBA.LINE
    				,MAX(PE.CONTACT_DATE) AS LAST_ENCOUNTER_DATE
    			   FROM PAT_BTG_AUDIT PBA 
    					INNER JOIN PAT_ENC PE ON PBA.PAT_ID = PE.PAT_ID
    			   WHERE CAST(PE.CONTACT_DATE AS DATE) < DATEADD(MM,@Months,CAST(PBA.BTG_INSTANT AS DATE))
    					AND CAST(PE.CONTACT_DATE AS DATE) < CAST(PBA.BTG_INSTANT AS DATE)
    					AND PE.ENC_TYPE_C NOT IN (5,10,151)
    			   GROUP BY PE.PAT_ID, PBA.LINE) LAST_ENC ON EPT.PAT_ID = LAST_ENC.PAT_ID 
    															AND BTG.LINE = LAST_ENC.LINE
    WHERE CAST(BTG.BTG_INSTANT AS DATE) BETWEEN @BeginDate and @EndDate
    	AND BTG.PAT_ID NOT IN (SELECT ENC.PAT_ID FROM PAT_ENC ENC
    						   WHERE (CAST(ENC.CONTACT_DATE AS DATE) >= DATEADD(MM,@Months,CAST(BTG.BTG_INSTANT AS DATE))
    											AND CAST(ENC.CONTACT_DATE AS DATE) <= CAST(BTG.BTG_INSTANT AS DATE))
    								-- EXCLUDE ENCOUNTER TYPES
    								-- 5 Canceled
    								-- 10 Empty
    								-- 151 Erroneous Encounter
    								OR ENC.ENC_TYPE_C IN (5,10,151))
    ORDER BY BTG.PAT_ID, BTG.BTG_INSTANT, BTG.LINE
    

    • Marked as answer by mrbsaved Friday, April 18, 2014 3:37 PM
    Friday, April 18, 2014 3:36 PM

All replies

  • Are you counting with the time part?

    May be casting both dates to [date] data type.

    ... AND cast(PE.CONTACT_DATE as date) < cast(DATEADD(MM,@Months,PBA.BTG_INSTANT) as date)
    ...


    AMB

    Some guidelines for posting questions...

    Thursday, April 10, 2014 7:40 PM
    Moderator
  • What is the data type of BTG_Instant when you are using it with >= with Contact date.

    may be you should use

    (ENC.CONTACT_DATE >= DATEADD(MM,@Months,CAST(BTG.BTG_INSTANT AS Date))
    					AND ENC.CONTACT_DATE <= CAST(BTG.BTG_INSTANT AS Date))

    Please mark as answer, if this has helped you solve the issue.

    Good Luck :) .. visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

    Thursday, April 10, 2014 7:41 PM
  • I have tried using CAST.  It makes no difference.
    Thursday, April 10, 2014 7:52 PM
  • I have tried using CAST.  It makes no difference.
    Thursday, April 10, 2014 7:53 PM
  • What do you get if you run the derived statement alone? 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 8:11 PM
    Moderator
  • I think you mean run just the subquery with the MAX statement.  The following code produces the correct result whether I hardcode the date or not.

    SELECT PE.PAT_ID
    ,MAX(PE.CONTACT_DATE) AS 'LAST CONTACT DATE'
    FROM PAT_BTG_AUDIT PBA 
    	INNER JOIN PAT_ENC PE ON PBA.PAT_ID = PE.PAT_ID
    WHERE PE.CONTACT_DATE < DATEADD(MM,-1,PBA.BTG_INSTANT)
    --WHERE PE.CONTACT_DATE < '2014-03-02 13:53:00'
    AND PE.ENC_TYPE_C NOT IN (5,10,151)
    AND PE.PAT_ID = '1325183'
    AND PBA.LINE = 38
    --ORDER BY PE.CONTACT_DATE DESC
    GROUP BY PE.PAT_ID, PE.CONTACT_DATE
    ORDER BY PE.PAT_ID, PE.CONTACT_DATE DESC

    I get the following results.  The first date is the correct date because it is less than BTG_INSTANT and the latest CONTACT_DATE.  In my original SQL without the hardcoded date I kept getting 2014-03-06 which is greater than BTG_INSTANT:

    PAT_ID                 LAST CONTACT DATE
    1325183                2014-01-09 00:00:00.000
    1325183                2013-11-12 00:00:00.000
    1325183                2013-08-08 00:00:00.000
    1325183                2013-08-01 00:00:00.000
    1325183                2013-07-30 00:00:00.000
    1325183                2013-07-11 00:00:00.000
    1325183                2013-06-20 00:00:00.000
    1325183                2013-05-31 00:00:00.000
    1325183                2013-04-15 00:00:00.000
    1325183                2013-01-30 00:00:00.000
    1325183                2013-01-18 00:00:00.000
    1325183                2013-01-16 00:00:00.000
    1325183                2012-12-27 00:00:00.000
    1325183                2012-12-20 00:00:00.000
    1325183                2012-12-06 00:00:00.000
    1325183                2012-08-03 00:00:00.000
    1325183                2012-06-27 00:00:00.000
    1325183                2012-06-07 00:00:00.000
    1325183                2012-03-22 00:00:00.000
    1325183                2012-03-15 00:00:00.000
    1325183                2012-01-10 00:00:00.000
    1325183                2011-12-31 00:00:00.000
    1325183                2011-12-05 00:00:00.000
    1325183                2011-11-29 00:00:00.000
    1325183                2011-11-28 00:00:00.000
    1325183                2011-03-28 00:00:00.000
    1325183                2011-03-23 00:00:00.000
    1325183                2011-03-21 00:00:00.000
    1325183                2011-03-15 00:00:00.000
    1325183                2011-03-08 00:00:00.000
    1325183                2011-03-07 00:00:00.000
    1325183                2011-03-02 00:00:00.000
    1325183                2011-01-04 00:00:00.000
    1325183                2010-11-11 00:00:00.000
    1325183                2010-10-23 00:00:00.000
    1325183                2010-10-06 00:00:00.000
    1325183                2010-09-30 00:00:00.000
    1325183                2009-10-08 00:00:00.000
    1325183                2009-09-09 00:00:00.000
    1325183                2009-06-11 00:00:00.000
    1325183                2008-12-17 00:00:00.000
    1325183                2008-12-15 00:00:00.000
    1325183                2008-09-17 00:00:00.000
    1325183                2008-08-15 00:00:00.000
    1325183                2008-08-13 00:00:00.000
    1325183                2007-05-23 00:00:00.000
    1325183                2007-05-21 00:00:00.000
    1325183                2007-02-19 00:00:00.000
    1325183                2006-04-10 00:00:00.000
    1325183                2005-10-10 00:00:00.000
    1325183                2005-10-06 00:00:00.000
    1325183                2005-10-03 00:00:00.000
    1325183                2005-09-30 00:00:00.000
    1325183                2005-09-28 00:00:00.000
    1325183                2005-09-26 00:00:00.000
    1325183                2005-09-19 00:00:00.000
    1325183                2005-09-17 00:00:00.000
    1325183                2005-04-18 00:00:00.000
    1325183                2005-04-11 00:00:00.000
    1325183                2005-04-07 00:00:00.000
    1325183                2005-04-05 00:00:00.000
    1325183                2005-04-03 00:00:00.000
    1325183                2004-06-15 00:00:00.000
    1325183                2004-01-30 00:00:00.000
    1325183                2004-01-27 00:00:00.000
    1325183                2003-08-22 00:00:00.000
    1325183                2003-08-20 00:00:00.000
    1325183                2003-06-11 00:00:00.000
    1325183                2003-02-07 00:00:00.000
    1325183                2002-10-02 00:00:00.000
    1325183                2002-03-14 00:00:00.000
    1325183                2002-03-06 00:00:00.000
    1325183                2002-02-28 00:00:00.000
    1325183                2000-09-14 00:00:00.000
    1325183                2000-09-05 00:00:00.000
    1325183                2000-06-13 00:00:00.000
    1325183                2000-04-14 00:00:00.000
    1325183                1999-09-14 00:00:00.000
    

    Thursday, April 10, 2014 8:44 PM
  • This is good, so we know that part works. Your query is a bit complex and I do not see the reason for joining with Patient table. It doesn't seem to be used in the select list.

    So, I would put your first subquery into cte and re-write the query without that extra left join and see what you get.

    Try to simplify the query by removing extra stuff until you find what causes your problem.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, April 10, 2014 8:53 PM
    Moderator
  • The code I sent is a simplified version of a more complex query.  I just forgot to take out the PATIENT table.  It works the same way with or without the patient table.  I will keep investigating.  

    What is cte?  

    I am writing SQL that will eventually be used as an Add Command in Crystal Reports 2008.  I use MS SQL Server Management Studio to develop the SQL.  Maybe that is not truly T-SQL.  Should I have used a different forum?

    Thursday, April 10, 2014 9:02 PM
  • Will you be able to call stored procedure in Crystal Reports?

    CTE is Common Table expression. Basically, it's just the way to make the query easier to read (this is not exactly what CTE is, but for now this explanation will work).

    So, your query will be

    ;with cte as (your derived max query)

    select ...

    LEFT JOIN cte ...

    ------------

    In other words, it's exactly the same query just re-written in another way to simplify it. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 9:05 PM
    Moderator
  • CTE is Common Table Expression. It is helpful structuring complex code.

    CTE example:

    http://www.sqlusa.com/bestpractices2005/cte/

    >LINE 14 AND PE.CONTACT_DATE < DATEADD(MM,@Months,'2014-03-02 13:53:00')

    Are you really interested in the middle of the day? Or 23:59:59 (midnight)?

    Do not hardwire dates into queries. Declare them as local variables for testing or production purposes:

    DECLARE @RefContactDate DATETIME = '2014-03-02 13:53:00'

    >LINE 14 AND PE.CONTACT_DATE < DATEADD(MM,@Months, @RefContactDate)

    Be mindful of the infamous midnight bug for date ranges:

    http://www.sqlusa.com/bestpractices2008/between-dates/


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, April 10, 2014 9:07 PM
    Moderator
  • Thanks for the clarification!!  I am not sure about stored procedures at my client site but I can ask.  My SQL experience is barely at the intermediate level.  Thanks for the help!
    Thursday, April 10, 2014 9:08 PM
  • Yes, I am using variables.  I hardcoded the datetime because I was getting the wrong result when using the actual item/column name.  The hardcoded value is the actual value of the item/column.  This is why I am stumped.  Why would it work as a hardcoded value but not when using the item name?
    Thursday, April 10, 2014 9:18 PM
  • One extra suggestion.

    What is your SQL Server version?

    The query you wrote (the derived table) is a tricky one.

    What will happen if you materialize it into a temp table and use that temp table to join with the rest of the tables?

    How many rows that subquery returns?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 10, 2014 9:31 PM
    Moderator
  • I am using SQL Server 2012.  The subquery is returning only one row for row in the main query.
    Thursday, April 10, 2014 9:43 PM
  • >Why would it work as a hardcoded value but not when using the item name?

    Because when you use a column name, there can be millions of values in that column. Some of those values may cause unexpected results.


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Thursday, April 10, 2014 9:55 PM
    Moderator
  • Ok, try getting the main query into temp table first without subquery, then add a subquery or vs. versa.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 11, 2014 1:48 AM
    Moderator
  • Something in the logic looks funny, and I suspect that what's funny, is that you're overlooking that BTG_Instant has multiple values, including one of '2013/03/02 13:53', but also another at 2013/03/06 [Edit - (or a Patient Encounter)], and the multiple renditions of it in your output (once as BTG.BTG_Instant in the outermost top level query) is making you dwell on the 03/02 value, forgetting that the derived table LAST_ENC has it's own independent reference to BTG_Instant (this time as PBA.BTG_Instant, and that query sees the 2014/03/06 date unless you hard code it to 2013/03/02.  You're crossing dimensions, you want to compare BTG_Instant from the parent query to BTG_Instant in the derived table used in the join, but it doesn't work that way. 

    Thankfully, there's the APPLY statement, and that does (can) work that way...  Apply lets you refer back and up to the parent query "above" the Apply statement, which I think is what you want to do, to filter based on the topmost query's BTG.BTG_Instant.

    Code (written without testing)

    Outer Apply 
      (Select PE.Pat_ID, Max(pe.contact)date) as Last_encounter_date 
         from pat_btg_audit PBA
       inner join PAT_Enc PE on PBA.Pat_ID = PE.Pat_ID 
                              and PE.Contact_Date < DateAdd(MM, Months, BTG.BTG_INSTANT
       Where PE.PatID = BTG.PadID
      ) as LAST_ENC 
    To me, something in lines 18-20 doesn't look quite right either, but it's one of those cases where "Doesn't look right" and "Not wrong" can both happen at the same time.  Step 1, try the Outer Apply, but something else may be wrong too.

    EDIT: Minor correction: there may not be two BTG_Instant values, but I betcha there's a 3/6/2014 Patient Encounter, which is what the Max function is looking up. 


    • Edited by johnqflorida Friday, April 11, 2014 12:51 PM Added edit
    Friday, April 11, 2014 2:41 AM
  • Try this code for the subquery:

    SELECT PE.PAT_ID
     
    ,MAX(PE.CONTACT_DATE) AS LAST_ENCOUNTER_DATE
      
    FROM PAT_BTG_AUDIT PBA
    INNER JOIN PAT_ENC PE ON PBA.PAT_ID = PE.PAT_ID
    WHERE PE.CONTACT_DATE < DATEADD(MM,@Months,PBA.BTG_INSTANT)
    --LINE 14 AND PE.CONTACT_DATE < DATEADD(MM,@Months,'2014-03-02 13:53:00')
    --LINE 15 AND PE.CONTACT_DATE < DATEADD(MM,@Months,'2014-03-02')
      
    GROUP BY PE.PAT_ID

    Saturday, April 12, 2014 4:39 AM
  • This is bad SQL on so many levels. 

    Think about “BTG.pat_id AS 'pat id'” and similar crap in your code. First of all, you do not know that single quotes are MS dialect, not real SQL (we use double quotes; have you read the ANSI/ISO Standards or a good book on SQL?). But more than that, spacing in a data element violates ISO-11179 rules. You 1960's COBOL programers do this to format data for display in the database! We do not; we have a presentation layer in tiered architectures. 

    I see you put the comma in the first column of your punch card! I remember that from my early days of programming It let us re-arrange the deck and re-use cards. We have not done that in 30 years.

    If I used “identity_id” in a book, people would think I was absurd> Do you know that “to be is to be something in particular”? This is the Law of Identity, the foundation of all mathematics AND logic.

    You have two OUTER JOINs in one query; this is what happens when the DDL is a disaster and you have no DRI. Likewise SELECT DISTINCT is seldom used in good DDL. But you have no Netiquette and failed to post any DDL. 

    There is no such thing as a “type_id” in RDBMS. A data element can be a “<something in particular>_type” or “<something in particular>_id”, but never that absurd hybrid. 

    Do you really have only one “Patient”, as you said? 
    Why did you hardwire '2014-03-02 13:53:00' in a query?? 
    Why is “encounter type” an integer? A competent DB designer would  have gone with a hierarchical (Dewey Decimal) model to make reporting  easier. 

    Do you have any idea what the performance hit your nested SELECTs are costing you? 

    You need far more help than you can get in a forum. You need to throe this out and start over. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, April 13, 2014 2:25 AM
  • I finally figured out the right code for my sub-query which gives me the maximum encounter date that is less than the BTG INSTANT.

    DECLARE @BeginDate AS DATE
    DECLARE @EndDate AS DATE
    DECLARE @RunTimeOption AS VarChar(25)
    DECLARE @CurrDate DATE
    DECLARE @Months AS INT
    
    SET @CurrDate = CAST(CURRENT_TIMESTAMP AS DATE)
    SET @BeginDate = '2014-01-01'
    SET @EndDate = '2014-03-31'
    SET @RunTimeOption = 'Last Full Month'
    SET @Months = 6
    SET @Months = @Months * -1
    
    
    If @RunTimeOption = 'Last Full Week'
    	BEGIN
    	--LAST FULL WEEK - SATURDAY THRU SUNDAY
    	SET @BeginDate = DATEADD(ww,DATEDIFF(ww,0,@CurrDate)-1,-1) 
    	SET @EndDate = DATEADD(ww,DATEDIFF(ww,0,@CurrDate),-2)
    	END
    ELSE
    	IF @RunTimeOption = 'Last Full Month'
    	BEGIN
    	--LAST FULL MONTH
    	SET @BeginDate = DATEADD(mm,DATEDIFF(mm,0,@CurrDate)-1,0) 
    	SET @EndDate = DATEADD(mm,DATEDIFF(mm,0,@CurrDate),-1)
    	END
    
    select DISTINCT BTG.PAT_ID AS 'PAT ID'
    ,EPT.PAT_NAME AS 'BTG Patient Name'
    ,II.IDENTITY_ID AS 'BTG Patient MRN'
    ,ZPT.TITLE AS 'BTG Patient Type'
    ,BTG.LINE AS 'BTG LINE'
    ,EMP.NAME AS 'BTG User Name'
    ,BTG.USER_ID AS 'BTG User Record ID'
    ,EMP.SYSTEM_LOGIN AS 'BTG User Network ID'
    ,BTG.USER_LOGON_DEPT_ID AS 'BTG USER Logged On DEPT ID' --DEP ID of the department the user was actually logged in to when Breaking the Glass
    ,DEP1.DEPARTMENT_NAME AS 'BTG USER Logged On DEPT Name'
    ,EMP.LGIN_DEPARTMENT_ID as 'BTG User Default Login DEPT ID' --EMP Hyperspace Default Login DEP (Item 20660)
    ,DEP2.DEPARTMENT_NAME AS 'BTG User Default Login DEPT Name'
    ,EMP.PROV_ID AS 'BTG USER SER ID'
    ,BTG.USER_DEPT_ID AS 'BTG User Provider Default DEPT ID' --DEP ID of the user's default login department (first department listed in SER)
    ,DEP3.DEPARTMENT_NAME AS 'BTG User Provider Default DEPT Name'
    ,BTG.BTG_INSTANT AS 'BTG INSTANT'
    ,CAST(BTG.BTG_INSTANT AS Date) AS 'BTG Event Date'
    ,CAST(BTG.BTG_INSTANT AS Time) AS 'BTG Event Time'
    ,BTG.BTG_REASON_C AS 'BTG REASON CODE'
    ,ZBR.TITLE AS 'BTG Reason'
    ,BTG.BTG_EXPLANATION AS 'BTG Explanation'
    ,LAST_ENC.LAST_ENCOUNTER_DATE AS 'Last Encounter Date'
    from PAT_BTG_AUDIT BTG
    	INNER JOIN IDENTITY_ID II ON BTG.PAT_ID = II.PAT_ID AND II.IDENTITY_TYPE_ID = 1
    	INNER JOIN PATIENT_TYPE PT ON BTG.PAT_ID = PT.PAT_ID
    	LEFT JOIN PATIENT EPT ON BTG.PAT_ID = EPT.PAT_ID
    	LEFT JOIN ZC_PATIENT_TYPE ZPT ON PT.PATIENT_TYPE_C = ZPT.PATIENT_TYPE_C
    	LEFT JOIN CLARITY_EMP EMP ON BTG.USER_ID = EMP.USER_ID
    	LEFT JOIN ZC_BTG_REASON ZBR ON BTG.BTG_REASON_C = ZBR.BTG_REASON_C
    	LEFT JOIN CLARITY_DEP DEP1 ON BTG.USER_LOGON_DEPT_ID = DEP1.DEPARTMENT_ID
    	LEFT JOIN CLARITY_DEP DEP2 ON EMP.LGIN_DEPARTMENT_ID = DEP2.DEPARTMENT_ID
    	LEFT JOIN CLARITY_SER_DEPT CSD ON EMP.PROV_ID = CSD.PROV_ID AND CSD.LINE = 1
    	LEFT JOIN CLARITY_DEP DEP3 ON CSD.DEPARTMENT_ID = DEP3.DEPARTMENT_ID
    	LEFT JOIN PATIENT_3 EPT3 ON EPT.PAT_ID = EPT3.PAT_ID
    	LEFT JOIN (SELECT PE.PAT_ID
    				,PBA.LINE
    				,MAX(PE.CONTACT_DATE) AS LAST_ENCOUNTER_DATE
    			   FROM PAT_BTG_AUDIT PBA 
    					INNER JOIN PAT_ENC PE ON PBA.PAT_ID = PE.PAT_ID
    			   WHERE CAST(PE.CONTACT_DATE AS DATE) < DATEADD(MM,@Months,CAST(PBA.BTG_INSTANT AS DATE))
    					AND CAST(PE.CONTACT_DATE AS DATE) < CAST(PBA.BTG_INSTANT AS DATE)
    					AND PE.ENC_TYPE_C NOT IN (5,10,151)
    			   GROUP BY PE.PAT_ID, PBA.LINE) LAST_ENC ON EPT.PAT_ID = LAST_ENC.PAT_ID 
    															AND BTG.LINE = LAST_ENC.LINE
    WHERE CAST(BTG.BTG_INSTANT AS DATE) BETWEEN @BeginDate and @EndDate
    	AND BTG.PAT_ID NOT IN (SELECT ENC.PAT_ID FROM PAT_ENC ENC
    						   WHERE (CAST(ENC.CONTACT_DATE AS DATE) >= DATEADD(MM,@Months,CAST(BTG.BTG_INSTANT AS DATE))
    											AND CAST(ENC.CONTACT_DATE AS DATE) <= CAST(BTG.BTG_INSTANT AS DATE))
    								-- EXCLUDE ENCOUNTER TYPES
    								-- 5 Canceled
    								-- 10 Empty
    								-- 151 Erroneous Encounter
    								OR ENC.ENC_TYPE_C IN (5,10,151))
    ORDER BY BTG.PAT_ID, BTG.BTG_INSTANT, BTG.LINE
    

    • Marked as answer by mrbsaved Friday, April 18, 2014 3:37 PM
    Friday, April 18, 2014 3:36 PM