asked on Alter this Sql Statement
Here is the sql select statement Im using :
SELECT t1.CaseID,
,CASE WHEN [DATERECEIVED]='1900-01-01
,CASE WHEN [TICKLERDATE]='1900-01-01'
,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,
,DATEDIFF(DAY,GETDATE(),TI
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?
SELECT t1.CaseID,
,CASE WHEN [DATERECEIVED]='1900-01-01
,CASE WHEN [TICKLERDATE]='1900-01-01'
,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,
,DATEDIFF(DAY,GETDATE(),TI
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?
Microsoft SQL ServerSQL
Last Comment
zachvaldez
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.
Here is the query with an order clause:
SELECT t1.CaseID,
,CASE WHEN [DATERECEIVED]='1900-01-01
,CASE WHEN [TICKLERDATE]='1900-01-01'
,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,
,DATEDIFF(DAY,GETDATE(),TI
FROM Table1 t1 inner join Table2 t2 on t1.CaseID=t2.CaseID
ORDER BY DATEDIFFOFDAY desc
SELECT t1.CaseID,
,CASE WHEN [DATERECEIVED]='1900-01-01
,CASE WHEN [TICKLERDATE]='1900-01-01'
,CASE WHEN ISNULL(DATERECEIVED,'') = '' THEN '' ELSE DATEDIFF(DAY,DATERECEIVED,
,DATEDIFF(DAY,GETDATE(),TI
FROM Table1 t1 inner join Table2 t2 on t1.CaseID=t2.CaseID
ORDER BY DATEDIFFOFDAY desc
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thanks!!
ASKER
ok!!
Looks like the question was answered
ASKER
I acknowledge the answer.