Solved

Alter this Sql Statement

Posted on 2016-08-11
8
46 Views
Last Modified: 2016-09-05
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?
0
Comment
Question by:zachvaldez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41752651
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
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41752653
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
 

Author Comment

by:zachvaldez
ID: 41752737
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
What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
LVL 8

Accepted Solution

by:
Randy Peterson earned 500 total points (awarded by participants)
ID: 41756584
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
 

Author Comment

by:zachvaldez
ID: 41759663
thanks!!
0
 

Author Comment

by:zachvaldez
ID: 41759668
ok!!
0
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 41784448
Looks like the question was answered
0
 

Author Comment

by:zachvaldez
ID: 41785601
I acknowledge the answer.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question