How to find date difference between two date columns which are not in same row?

Question

We have to find difference of date in a procedure, where in, we will have to use data from a table.
For ex:
Say suppose there are 3 date columns in a table: X, Y and Z.

X has some date values.
Y has some date values.
For Z, as per some logic, we need to find difference of date values between X and Y, which won't be in same row.

Can we have any sql logic to find such difference in a table.

Thanks in advance,

Partha

Answer 1

Here is the complete scenario explained.

Scenario:

A request passes through the below roles in the scenario and they the have authority to approve and reject, except, supplier, who can only submit some documents.

Buyer
Supplier
Supervisor
Manager
Head

Buyer: Before the Buyer approves the request, there are 3 stages where Buyer can put it on Pending status.
1)Pending Buyer acceptance
2)Pending Buyer Approval
3)Buyer Reject
If the Buyer rejects, the request will have to be re-submitted.

Supplier: Who can only submit the documents.

Supervisor: Will first go through the documents and may approve or reject it. If approves then will go to the Manager and if rejected then will go to buyer.

Manager: May approve or reject request.If rejects then will go back to the Supervisor and if approves then will go to Head.

Head: May approve or reject request. If rejects then will go back to the Supervisor.

Superviser will again have to give final approval once there is approval from Head. If the Supervisor approves then request is completed or else rejected then needs to

be re-submitted.

Table:

Req.ID| Req.Status|Prev.Status| Req.Recd.On| Req.Appd.On| Req.Rejd.On

1| 1|0| '2013-02-01'| Null| Null
1| 2|1| '2013-02-03'| Null| Null    
1| 3|2| Null| '2013-02-04'| Null
1| 4|3| '2013-02-05'| Null| Null    
1| 5|4| '2013-02-07'| Null| Null    
1| 6|5| '2013-02-08'| Null| Null
1| 7|6| '2013-02-09'| Null| Null
1| 12|7|  Null| Null| '2013-02-12'

===========

In the above table Req.Status and Prev.Status columns contain the values based on the value given below in Request_status table.

Table: Request_status
Req_Status_ID    Status_name
1        Pending Buyer acceptance
2        Pending Buyer Approval
3        Supplier document submission
4        Pending supervisor Document approval
5        Pending Manager Approval
6        Pending Head approval
7        Pending Supervisor Final approval
8        Buyer Rejected
9        Supervisor Document rejected
10        Manager Rejected
11        Head rejected
12        Supervisor Final rejected
13        Cancelled
14        Completed

What we have to do is based upon the Req.Status and Prev.Status we have to find the date difference that which person(buyer/Supervisor/Manager/Head) took how long to

act on that particular request.
For ex.difference between Req.Recd.On, Req.Appd.On and Req.Rejd.On in different Req.Status.
i.e. as per the Table data, difference between '2013-02-04' and '2013-02-03, which are in different rows of the table, with Req.Status 3 and 2 receptively.

Please advise with any logic to find the date difference as explained above.

Thanks in advance!
Partha

Answer 2

declare @t table (ReqID int,Status int,Prev int, Rec date,Appd date,Rejddate date)

insert into @t
values (1,1,0,'20130201',null,null)
,(1,2,1,'20130203',null,null)
,(1,3,2,null,'20130204',null)
,(1,4,3,'20130205',null,null)
,(1,5,4,'20130207',null,null)
,(1,6,5,'20130208',null,null)
,(1,7,6,'20130209',null,null)
,(1,12,7,null,null,'20130212')
 ;with CTE as
 (
 select *,ROW_NUMBER() over (Partition by ReqID order by status asc) as RN from @t )
 --select * from CTE
 
 Select A.*,
 case when A.Rn =1 then NULL ELSE DateDiff(day,COALESCE(B.REC,B.APpD,B.REJddate),COALESCE(A.REC,A.APpD,A.REJddate)) END as DateDiffer
 from CTE A  LEFT OUTER JOIN CTE B on A.RN=B.RN+1 and A.ReqID=B.ReqID

Answer 3

Hi

See below link:

http://stackoverflow.com/questions/5939954/sql-server-between-two-datetime-fields-not-working-correctly

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 4

Thanks a lot Stan...

Answer 5

If you are using SS 2012 then take advantage of the offset functions.

SELECT
	ReqID,
	Status,
	Prev,
	Rec,
	Appd,
	Rejddate,
	DATEDIFF([day],
	COALESCE(
	LAG(Rec) OVER(PARTITION BY ReqID ORDER BY [Status] ASC),
	LAG(Appd) OVER(PARTITION BY ReqID ORDER BY [Status] ASC),
	LAG(Rejddate) OVER(PARTITION BY ReqID ORDER BY [Status] ASC)),
	COALESCE(Rec, Appd, Rejddate)
	) AS DateDiffer
FROM
	@t;
GO

Having an index by (ReqID, [Status]) and including the rest of columns being referenced could help to improve the performance of this solution and avoid the sort iterator.

Example:

SET NOCOUNT ON;
USE tempdb;
GO
declare @t table (
ReqID int not null,
Status int not null,
Prev int, 
Rec date,
Appd date,
Rejddate date,
UNIQUE CLUSTERED (ReqID, [Status], Prev)
);

Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions
http://shop.oreilly.com/product/0790145323088.do

Edit: Thanks to Stan210 for providing table schema and sample data.

 

---

AMB

Some guidelines for posting questions...