September 3, 2015 at 8:11 pm
Hi All,
I have 2 tables as defined below. I want to calculate PS1time and Ps2 time.
Table 1
O_IDP_TYPEP_startdateP_enddate
ABCP8/24/2015 13:148/24/2015 13:41
ABCP8/24/2015 14:038/24/2015 15:31
ABCP8/25/2015 12:098/25/2015 13:25
XYZP8/28/2015 13:108/28/2015 21:44
Table 2
O_IDS_TYPES_startdateS_enddate
ABCS28/24/2015 13:148/24/2015 19:22
ABCS28/24/2015 19:228/30/2015 21:34
XYZS28/27/2015 22:228/28/2015 13:10
XYZS28/28/2015 13:108/28/2015 15:34
XYZS18/28/2015 15:348/28/2015 22:44
OUTPUT
O_IDSP_TYPEPS1_starttimePS1_starttime
ABCPS18/24/2015 13:148/24/2015 19:22
XYZPS18/28/2015 15:348/28/2015 21:44
XYZPS28/28/2015 13:108/28/2015 15:34
For Each O_Id How much time spent for Ps1 and PS2.I tried but not able to reach expecting results asmentioned.
Requesting you to help me in this issue.
Thanks in advance
September 3, 2015 at 9:35 pm
rsrvas (9/3/2015)
Hi All,I have 2 tables as defined below. I want to calculate PS1time and Ps2 time.
Table 1
O_IDP_TYPEP_startdateP_enddate
ABCP8/24/2015 13:148/24/2015 13:41
ABCP8/24/2015 14:038/24/2015 15:31
ABCP8/25/2015 12:098/25/2015 13:25
XYZP8/28/2015 13:108/28/2015 21:44
Table 2
O_IDS_TYPES_startdateS_enddate
ABCS28/24/2015 13:148/24/2015 19:22
ABCS28/24/2015 19:228/30/2015 21:34
XYZS28/27/2015 22:228/28/2015 13:10
XYZS28/28/2015 13:108/28/2015 15:34
XYZS18/28/2015 15:348/28/2015 22:44
OUTPUT
O_IDSP_TYPEPS1_starttimePS1_starttime
ABCPS18/24/2015 13:148/24/2015 19:22
XYZPS18/28/2015 15:348/28/2015 21:44
XYZPS28/28/2015 13:108/28/2015 15:34
For Each O_Id How much time spent for Ps1 and PS2.I tried but not able to reach expecting results asmentioned.
Requesting you to help me in this issue.
Thanks in advance
Hi and welcome to the forum.
There are few things missing in the question such as DDL (create table), consumable data (as an insert script) and last but not least, a clear definition of the logic as the only thing we see is what is posted.
😎
There are few ways of doing this kind of correlation and here is a quick example
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @Table_1 TABLE
(
O_ID CHAR(3) NOT NULL
,P_TYPE VARCHAR(3) NOT NULL
,P_startdate DATETIME NOT NULL
,P_enddate DATETIME NOT NULL
);
INSERT INTO @Table_1 (O_ID ,P_TYPE,P_startdate ,P_enddate )
VALUES ('ABC','P' ,'8/24/2015 13:14','8/24/2015 13:41')
,('ABC','P' ,'8/24/2015 14:03','8/24/2015 15:31')
,('ABC','P' ,'8/25/2015 12:09','8/25/2015 13:25')
,('XYZ','P' ,'8/28/2015 13:10','8/28/2015 21:44')
;
DECLARE @Table_2 TABLE
(
O_ID CHAR(3) NOT NULL
,S_TYPE VARCHAR(3) NOT NULL
,S_startdate DATETIME NOT NULL
,S_enddate DATETIME NOT NULL
);
INSERT INTO @Table_2 (O_ID ,S_TYPE,S_startdate ,S_enddate )
VALUES ('ABC','S2' ,'8/24/2015 13:14','8/24/2015 19:22')
,('ABC','S2' ,'8/24/2015 19:22','8/30/2015 21:34')
,('XYZ','S2' ,'8/27/2015 22:22','8/28/2015 13:10')
,('XYZ','S2' ,'8/28/2015 13:10','8/28/2015 15:34')
,('XYZ','S1' ,'8/28/2015 15:34','8/28/2015 22:44')
;
;WITH BASE_DATA AS
(
SELECT
T1.O_ID
,T1.P_TYPE
,DATEDIFF(DAY,T1.P_startdate,T2.S_startdate) AS SD_DIFF
,T1.P_startdate
,T1.P_enddate
,T2.S_TYPE
,T2.S_startdate
,T2.S_enddate
FROM @Table_1 T1
CROSS APPLY @Table_2 T2
WHERE T1.O_ID = T2.O_ID
)
SELECT
BD.O_ID AS O_ID
,BD.P_TYPE + BD.S_TYPE AS SP_TYPE
,MIN(BD.S_startdate) AS PS1_starttime
,MAX(BD.S_enddate ) AS PS1_starttime
FROM BASE_DATA BD
WHERE BD.SD_DIFF = 0
AND
(
BD.P_startdate BETWEEN BD.S_startdate AND BD.S_enddate
OR
BD.P_enddate BETWEEN BD.S_startdate AND BD.S_enddate
)
GROUP BY BD.O_ID
,BD.P_TYPE
,BD.S_TYPE
;
Results
O_ID SP_TYPE PS1_starttime PS1_starttime
---- ------- ----------------------- -----------------------
ABC PS2 2015-08-24 13:14:00.000 2015-08-24 19:22:00.000
XYZ PS1 2015-08-28 15:34:00.000 2015-08-28 22:44:00.000
XYZ PS2 2015-08-28 13:10:00.000 2015-08-28 15:34:00.000
September 4, 2015 at 3:56 am
Thanks a lot..it really helped me..It solved my issue
September 4, 2015 at 8:21 am
rsrvas (9/4/2015)
Thanks a lot..it really helped me..It solved my issue
You are very welcome!
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply