CODEWITHSUNDEEP
sqlsql-server
Erikah
Erikah

Reputation: 13

If EndDate in (DateDiff) is Null then Use Today

I am not able to determine how long an OBJNR has been in a particular status if there is no corresponding end date. The result is "0" due to the status still being active. I would like to use ongoing today's date to calculate how long an object number has been in that status. See current results below. 

(
SELECT 
JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ02T.TXT30, DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE),MAX(JCDS_SOGR.UDATE))
AS DIF
FROM JCDS_SOGR
JOIN TJ02T ON JCDS_SOGR.STAT = TJ02T.ISTAT
WHERE JCDS_SOGR.OBJNR = 'IE000000000010003791'
AND TJ02T.SPRAS = 'E'
GROUP BY JCDS_SOGR.OBJNR,JCDS_SOGR.STAT,TJ02T.TXT30) 

UNION ALL

(
SELECT JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ30T.TXT30, DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE),MAX(JCDS_SOGR.UDATE))
AS DIF
FROM JCDS_SOGR
JOIN TJ30T ON JCDS_SOGR.STAT = TJ30T.ESTAT
WHERE JCDS_SOGR.OBJNR = 'IE000000000010003791'
AND TJ30T.SPRAS = 'E'
AND TJ30T.MANDT='400'
AND TJ30T.STSMA = 'VEHICLE'
GROUP BY JCDS_SOGR.OBJNR,JCDS_SOGR.STAT,TJ30T.TXT30)

Expected Results is Where there is no end date I can use today's date to calculate date diff formula. The results with "0" do not have a corresponding end date in that respective status.

Currently, This is the Result:

     OBJNR  STAT    TXT30                      DIF
1    3791   I1099   Available                  0
2    3791   E0002   Awaiting Acceptance        110
3    3791   E0004   Operational                1473
4    3791   E0019   Operational Back-order     0

Upvotes: 0

Views: 239

Answers (2)

Erikah
Erikah

Reputation: 13

SELECT COUNT(*) as COUNT, JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ30T.TXT30, DIF =
CASE
  WHEN COUNT(*) > 1 THEN DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE),MAX(JCDS_SOGR.UDATE))
  WHEN COUNT(*) = 1 THEN DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE),GETDATE())
END
FROM JCDS_SOGR
JOIN TJ30T 
ON JCDS_SOGR.STAT = TJ30T.ESTAT
WHERE JCDS_SOGR.OBJNR = 'IE000000000010003137'
AND TJ30T.SPRAS='E'
AND TJ30T.MANDT='400'
AND STSMA = 'VEHICLE'
GROUP BY JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ30T.TXT30

Upvotes: 0

userfl89
userfl89

Reputation: 4790

The COALESCE function can be used to substitute the current date, GETDATE(), for null values. This can also be done using the ISNULL function, however COALESCE is ANSI standard which allows for use across more database platforms if necessary. From your question, it looks like you only want to use the current date when all values in this column are null, thus COALESCE is outside of the MAX function. If you want to replace any nulls with the current date, then compare these to the non-null values the COALESCE function will need to be placed inside MAX.

(
SELECT 
JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ02T.TXT30, DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE), COALESCE(MAX(JCDS_SOGR.UDATE), GETDATE()))
AS DIF
FROM JCDS_SOGR
JOIN TJ02T ON JCDS_SOGR.STAT = TJ02T.ISTAT
WHERE JCDS_SOGR.OBJNR = 'IE000000000010003791'
AND TJ02T.SPRAS = 'E'
GROUP BY JCDS_SOGR.OBJNR,JCDS_SOGR.STAT,TJ02T.TXT30) 

UNION ALL

(
SELECT JCDS_SOGR.OBJNR, JCDS_SOGR.STAT, TJ30T.TXT30, DATEDIFF(DAY,MIN(JCDS_SOGR.UDATE), COALESCE(MAX(JCDS_SOGR.UDATE), GETDATE()))
AS DIF
FROM JCDS_SOGR
JOIN TJ30T ON JCDS_SOGR.STAT = TJ30T.ESTAT
WHERE JCDS_SOGR.OBJNR = 'IE000000000010003791'
AND TJ30T.SPRAS = 'E'
AND TJ30T.MANDT='400'
AND TJ30T.STSMA = 'VEHICLE'
GROUP BY JCDS_SOGR.OBJNR,JCDS_SOGR.STAT,TJ30T.TXT30)

Upvotes: 1

Related Questions