Solved

Alter this Sql Statement

Posted on 2016-08-11
8
38 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loop to go backward 90 days 2 18
export sql results to csv 6 34
Query Help - MSSQL - Averages 5 25
optimize stored procedure 6 23
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now