Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Alter this Sql Statement

Posted on 2016-08-11
8
Medium Priority
?
49 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
Randy Peterson earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

783 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