Alter this Sql Statement

Here is the sql select statement Im using :
SELECT t1.CaseID,
        ,CASE WHEN [DATERECEIVED]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[DATERECEIVED],101)END AS DATERECEIVED  
      ,CASE WHEN [TICKLERDATE]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[TICKLERDATE],101)END AS TICKLERDATE
      ,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,TICKLERDATE)END AS DATEDIFFERENCE
      ,DATEDIFF(DAY,GETDATE(),TICKLERDATE)as DATEDIFFOFDAY
  FROM Table1 t1 inner join Table2  t2 on t1.CaseID=t2.CaseID
  where TICKLERDATE > GETDATE()

and here is the output:
CaseID   DateReceived   TicklerDate    DateDifference   DateDiffOfDay
3          2/16/2016       8/14/2016                      180             3
4          6/13/2016       12/11/2016                    180            122
5          3/1/2016         8/28/2016                      180             17

What I'd like to get is that even though the DateDiffOfDay zero out or becomes negative because 180 days have been reach or over, the records will still display.

What Select statement to use?
zachvaldezAsked:
Who is Participating?
 
Randy PetersonConnect With a Mentor Commented:
This should work for you...

SELECT t1.CaseID,
        ,CASE WHEN [DATERECEIVED]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[DATERECEIVED],101)END AS DATERECEIVED  
      ,CASE WHEN [TICKLERDATE]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[TICKLERDATE],101)END AS TICKLERDATE
      ,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,TICKLERDATE)END AS DATEDIFFERENCE
      ,CONVERT(VARCHAR,ABS(DATEDIFF(DAY,GETDATE(),TICKLERDATE))) + ' days over' AS DATEDIFFOFDAY
  FROM Table1 t1 inner join Table2  t2 on t1.CaseID=t2.CaseID
  ORDER BY DATEDIFFOFDAY DESC
0
 
Randy PetersonCommented:
All you need to do is remove your where clause to get all the records to display.  But then all the records would always display.  You could add an order by clause to show the ones where it hasn't gone past yet first.
0
 
Randy PetersonCommented:
Here is the query with an order clause:

SELECT t1.CaseID,
        ,CASE WHEN [DATERECEIVED]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[DATERECEIVED],101)END AS DATERECEIVED  
      ,CASE WHEN [TICKLERDATE]='1900-01-01' then '' ELSE CONVERT(VARCHAR(10),[TICKLERDATE],101)END AS TICKLERDATE
      ,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,TICKLERDATE)END AS DATEDIFFERENCE
      ,DATEDIFF(DAY,GETDATE(),TICKLERDATE)as DATEDIFFOFDAY
  FROM Table1 t1 inner join Table2  t2 on t1.CaseID=t2.CaseID
  ORDER BY DATEDIFFOFDAY desc
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
zachvaldezAuthor Commented:
Now I get more data. How would I express if DATEDIFFOFDAY has -83 means 83 days over.
I would just like to say 83 days over when it gets over 180 days. Currently the numbers are express
as -7,-78....
 like to say 78 days over, 7 days over.... instead
0
 
zachvaldezAuthor Commented:
thanks!!
0
 
zachvaldezAuthor Commented:
ok!!
0
 
Randy PetersonCommented:
Looks like the question was answered
0
 
zachvaldezAuthor Commented:
I acknowledge the answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.