CODEWITHSUNDEEP
sqlsql-server
Luís Baptista Lourenço
Luís Baptista Lourenço

Reputation: 65

Calculate Estimation Time Row

I Have been trying to solve this issue but my "Oriented Language" mind is not letting me figure out how to sum days in a SQL Query.

enter image description here

Basically i have in my database a table with the requests and which days the status of the request was Open, Pending or Closed.

I want to return a query that informs how many days the status was open and how many was pending.

The open days are calculated by the DATEDIFF between an open request and the moment the request moved to pending/closed. The pending requests are the DATEDIFF between the moment a request is moved to Pending and the moment is Open again.

I have been working in some solutions but have to be honest and say i'm not even close.

Thank you for your support!

Upvotes: 3

Views: 95

Answers (2)

gofr1
gofr1

Reputation: 15987

;WITH cte AS(
SELECT  ID,
        [Request ID],
        [Status],
        CreationDate,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CreationDate) as rn
FROM YourTable y
)

SELECT *
FROM (
    SELECT  c1.ID, 
            c1.[Status], 
            DATEDIFF(day,c1.CreationDate,c2.CreationDate) as [days]
    FROM cte c1
    LEFT JOIN cte c2
        ON c1.rn+1 = c2.rn AND c1.ID = c2.ID
    ) as p
PIVOT (
    SUM([days]) FOR  [Status] IN ([Open], [Pending])
) as pvt

Output:

ID      Open    Pending
GTGTG6  4       5

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

I am not sure that this will helpful for you since I don't have real data to test this,

SELECT ID,
   MAX(CASE WHEN STATUS='OPEN' THEN DAY(CreationDate) END)- MIN(CASE WHEN STATUS='OPEN' THEN DAY(CreationDate) END)OPENDAYS,
   MAX(CASE WHEN STATUS = 'CLOSE' THEN DAY(CreationDate)-1 END)- (MAX(CASE WHEN STATUS = 'PENDING' THEN DAY(CreationDate) END)- MIN(CASE WHEN STATUS = 'PENDING' THEN DAY(CreationDate) END)) PENDINGDAYS            
FROM TABLENAME                   
GROUP BY ID

Upvotes: 1

Related Questions