Saturday, February 23, 2013 12:32 PM
We have to find difference of date in a procedure, where in, we will have to use data from a table.
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,
Saturday, February 23, 2013 1:21 PM
Here is the complete scenario explained.
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: 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
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
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.
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
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!
Saturday, February 23, 2013 3:00 PM
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
Sunday, February 24, 2013 10:45 AM
HiSee below link:
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/
Sunday, February 24, 2013 6:01 PM
Thanks a lot Stan...
Sunday, February 24, 2013 6:47 PMModerator
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.
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
Edit: Thanks to Stan210 for providing table schema and sample data.
- Edited by HunchbackMVP, Moderator Sunday, February 24, 2013 6:54 PM