CODEWITHSUNDEEP
sql-server
Aced
Aced

Reputation: 109

TSQL - Get Last Successful Date

I am struck with the below logic. Whenever the status is failed, I need to max(EndTime) when it was previously successful. For example in the below data, for Batch = 3987 the lastrefreshdate should be 23-01-2017 but not 13-02-2017.

Below is the snippet of the data and the query.

Batch   TableName   IsFullLoad  Status  EndTime     LastRefreshDate
5524    SA                 1    Loaded  13-02-2017  13-02-2017
4746    SA                 1    Loaded  06-02-2017  06-02-2017
4174    SA                 1    Loaded  31-01-2017  31-01-2017
3987    SA                 1    Failed  30-01-2017  13-02-2017
3372    SA                 1    Loaded  23-01-2017  23-01-2017
2651    SA                 1    Loaded  16-01-2017  16-01-2017
1987    SA                 1    Loaded  09-01-2017  09-01-2017
1379    SA                 1    Loaded  02-01-2017  02-01-2017
835     SA                 1    Loaded  26-12-2016  26-12-2016
189     SA                 1    Loaded  19-12-2016  19-12-2016
52      SA                 1    Loaded  17-12-2016  17-12-2016

SQL:

SELECT TOP 40*
FROM (
SELECT
bi.Id,
bi.TableName,
bi.IsFullLoad,
bi.JobId,
[Status],
bi.CompletTime,
CASE 
WHEN bi.IsFullLoad = 1 AND bi.[Status] = 'Failed' 
THEN
(
SELECT MAX(CompletTime) FROM  Audit.T1 bb
  WHERE [Status]= 'Loaded' AND bb.IsFullLoad = 1  AND bb.TableName = bi.TableName 
  GROUP BY bb.TableName
  )
 ELSE 
 bi.CompletTime 
 END AS Validto
FROM Audit.T1 bi
WHERE TableName = 'Salesforce_Account' AND bi.IsFullLoad = 1
) x 
ORDER BY x.CompletTime desc

Any help is really appreciated. Thanks in Advance.

Upvotes: 2

Views: 80

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Your inner query is selecting the last completTime from the table where the conditions are met, but no where in the conditions you reference the current record's CompletTime.

Change the inner query from this:

SELECT MAX(CompletTime) FROM  Audit.T1 bb
WHERE [Status]= 'Loaded' AND bb.IsFullLoad = 1  AND bb.TableName = bi.TableName 
GROUP BY bb.TableName

To this:

SELECT TOP 1 CompletTime 
FROM  Audit.T1 bb
WHERE [Status]= 'Loaded' 
AND bb.IsFullLoad = 1  
AND bb.TableName = bi.TableName 
AND CompletTime < b1.CompletTime
ORDER BY CompletTime DESC

Of course, you can still use MAX - but add the relevant condition. Group by is not needed.

SELECT MAX(CompletTime)
FROM  Audit.T1 bb
WHERE [Status]= 'Loaded' 
AND bb.IsFullLoad = 1  
AND bb.TableName = bi.TableName 
AND CompletTime < b1.CompletTime

Upvotes: 1

Related Questions