Question regarding date logic

• Briceston

  SSCrazy

  Points: 2899

  More actions

  June 13, 2017 at 11:16 am

  #337557

  Hi guys,

  I need some help to derive some date fields.  

   For the "EvaluationDate field": I need most recent EvaluationDate completed between the previous Tuesday and the previous Monday. 
    The run date of the script is every Tuesday of the week.

  For the "Evaluation_RN_Date" field:  I need the most recent Evaluation_RN_Date between the previous Tuesday and the previous Monday. The EvaluationDate from the prior reporting week, Tuesday of 2 weeks ago through the Monday of 2 weeks ago. The run date of the script is every Tuesday of the week.
  
  Sample data

  
CREATE TABLE [dbo].[PatientEvaluation](
[Evaluation_Id] [numeric](20, 0) NULL,
[EvaluationDate] [nvarchar](3000) NULL,
[EvaluationType] [tinyint] NULL,
[Evaluation_RN_Date] [nvarchar](3000) NULL
)

  insert into [dbo].[PatientEvaluation] values ('120', '12/13/2013', '1', '12/23/2013');
insert into [dbo].[PatientEvaluation] values ('176', '02/13/2014', '1', '02/18/2014');
insert into [dbo].[PatientEvaluation] values ('246', '01/21/2014', '1', '01/28/2014');
insert into [dbo].[PatientEvaluation] values ('310', '03/19/2014', '1', '03/19/2014');
insert into [dbo].[PatientEvaluation] values ('381', '02/27/2014', '1', '03/02/2014');
insert into [dbo].[PatientEvaluation] values ('441', '12/20/2013', '1', '12/27/2013');
insert into [dbo].[PatientEvaluation] values ('491', '01/27/2014', '1', '01/30/2014');
insert into [dbo].[PatientEvaluation] values ('547', '02/12/2014', '1', '02/28/2014');
insert into [dbo].[PatientEvaluation] values ('611', '12/23/2013', '1', '12/25/2013');
insert into [dbo].[PatientEvaluation] values ('675', '03/24/2014', '1', '04/02/2014');
insert into [dbo].[PatientEvaluation] values ('732', '11/22/2013', '1', '11/27/2013');
insert into [dbo].[PatientEvaluation] values ('791', '11/13/2013', '1', '01/10/2014');
insert into [dbo].[PatientEvaluation] values ('853', '11/14/2013', '1', '11/21/2013');
insert into [dbo].[PatientEvaluation] values ('912', '11/13/2013', '1', '11/22/2013');
insert into [dbo].[PatientEvaluation] values ('976', '12/31/2013', '1', '01/18/2014');
insert into [dbo].[PatientEvaluation] values ('1034', '02/10/2014', '1', '03/25/2014');
insert into [dbo].[PatientEvaluation] values ('1090', '12/04/2013', '1', '01/09/2014');
insert into [dbo].[PatientEvaluation] values ('1150', '12/12/2013', '1', '12/20/2013');
insert into [dbo].[PatientEvaluation] values ('1218', '04/09/2014', '1', '04/14/2014');

  
  

  
Here is what I attempted:
Select 
  t1.Evaluation_Id,
  DATEADD(Day,-9, GETDATE()) as EvaluationDate,
Case when EvaluationDate BETWEEN DATEADD(day,-15,GETDATE()) AND DATEADD(day,-9,GETDATE()) and [Evaluation_RN_Date]> DATEADD(day,-9,GETDATE())end Evaluation_RN_Date

  From dbo.PatientEvaluation t1

  
  

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  June 13, 2017 at 12:37 pm

  #1946630

  Try something like:

  
DECLARE @PatientEvaluation table
   (
[Evaluation_Id] [numeric](20, 0) NULL,
[EvaluationDate] [nvarchar](3000) NULL,
[EvaluationType] [tinyint] NULL,
[Evaluation_RN_Date] [nvarchar](3000) NULL
) ;
INSERT INTO @PatientEvaluation
  VALUES
       ('120', '12/13/2013', '1', '12/23/2013')
     , ('176', '02/13/2014', '1', '02/18/2014')
     , ('246', '01/21/2014', '1', '01/28/2014')
     , ('310', '03/19/2014', '1', '03/19/2014')
     , ('381', '02/27/2014', '1', '03/02/2014')
     , ('441', '12/20/2013', '1', '12/27/2013')
     , ('491', '01/27/2014', '1', '01/30/2014')
     , ('547', '02/12/2014', '1', '02/28/2014')
     , ('611', '12/23/2013', '1', '12/25/2013')
     , ('675', '03/24/2014', '1', '04/02/2014')
     , ('732', '11/22/2013', '1', '11/27/2013')
     , ('791', '11/13/2013', '1', '01/10/2014')
     , ('853', '11/14/2013', '1', '11/21/2013')
     , ('912', '11/13/2013', '1', '11/22/2013')
     , ('976', '12/31/2013', '1', '01/18/2014')
     , ('1034', '02/10/2014', '1', '03/25/2014')
     , ('1090', '12/04/2013', '1', '01/09/2014')
     , ('1150', '12/12/2013', '1', '12/20/2013')
     , ('1218', '04/09/2014', '1', '04/14/2014');

  SELECT
       pe.Evaluation_Id
     , pe.EvaluationDate
     , pe.EvaluationType
     , pe.Evaluation_RN_Date   , Max(pe.EvaluationDate)     OVER (PARTITION BY pe.EvaluationType)  Evaluation_RN_Date
     , Max(pe.Evaluation_RN_Date) OVER (PARTITION BY pe.EvaluationType) EvaluationDate2WkPrev
  FROM @PatientEvaluation pe
  WHERE pe.EvaluationDate BETWEEN DateAdd(dd, 2, DateAdd(WEEK, DateDiff(WEEK, 0, pe.EvaluationDate)-1,0) )
                                                         AND DateAdd(dd, 2, DateAdd(WEEK, DateDiff(WEEK, 0, GetDate())-1,0))
  OR pe.Evaluation_RN_Date BETWEEN DateAdd(dd, 2, DateAdd(WEEK, DateDiff(WEEK, 0, pe.EvaluationDate)-1,0) )
                                                         AND DateAdd(dd, 2, DateAdd(WEEK, DateDiff(WEEK, 0, GetDate())-1,0));

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 13, 2017 at 1:29 pm

  #1946644

  Can you post dates that would actually work?
  Also, can you post the expected results based on the new sample data?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2