INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get Min and Max Time until it changes - Please Help

Get Min and Max Time until it changes - Please Help

(OP)
Hi Guys,

I am trying to extract minimum maximum time from active terminal until it changes the ownership.
The input as per below.

CODE

TERMINAL_ID	TERMINAL_NAME	                   START_DATE	END_DATE	CHANGE_FLAG
1128565	        JAPANPOST BANK(705910) OKINAWA JP  1/05/2016	31/12/2016	1
1128565	        JAPANPOST BANK(003500) TOKYO JP	   1/01/2017	30/06/2017	0
1128565	        JAPAN RAMEN	                   1/07/2017	31/08/2017	1
4184558	        POST Diamond Creek F VicAU	   1/05/2016	31/10/2016	1
4184558	        BRIDGE CLINIC MURRAY BRIDGESA AU   1/05/2017	30/06/2017	1 

As you can see there is terminal id can change the ownership with the change_flag = 1, if the change_flag = 0 means same ownership from previous record.
I would like to get entire length of duration from the same owner as per below output:

CODE

TERMINAL_ID	TERMINAL_NAME	                   START_DATE	END_DATE	CHANGE_FLAG
1128565	        JAPANPOST BANK(705910) OKINAWA JP  1/05/2016	30/06/2017	1
1128565	        JAPAN RAMEN	                   1/07/2017	31/08/2017	1
4184558	        POST Diamond Creek F VicAU	   1/05/2016	31/10/2016	1
4184558	        BRIDGE CLINIC MURRAY BRIDGESA AU   1/05/2017	30/06/2017	1 

Thanks alot in advance

RE: Get Min and Max Time until it changes - Please Help

CODE

DECLARE @Test TABLE (TERMINAL_ID int,
                     TERMINAL_NAME varchar(200),
                     START_DATE datetime,
                     END_DATE datetime,
                     CHANGE_FLAG bit)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(705910) OKINAWA JP','20160501','20161231',1)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170101','20170630',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN','20170701','20170831',1)
INSERT INTO @Test VALUES(4184558, 'POST Diamond Creek F VicAU','20170501','20161031',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170501','20170630',1)

SELECT Test.TERMINAL_ID
      ,Test.TERMINAL_Name
      ,Test.START_DATE
      ,ISNULL(Tst.END_DATE, Test.END_DATE) AS End_Date
      ,Test.Change_Flag
FROM @TEst Test
LEFT JOIN @TEst Tst ON Test.TERMINAL_ID = Tst.TERMINAL_ID
                   AND DATEADD(dd,1,Test.End_Date) = Tst.Start_Date
                   AND Tst.CHANGE_FLAG = 0
WHERE Test.CHANGE_FLAG = 1 

NOT TESTED PROPERLY!!!!

Borislav Borissov
VFP9 SP2, SQL Server

RE: Get Min and Max Time until it changes - Please Help

(OP)
Thanks Boris,

but it only works when the change flag=0 only 1 records, if it's more than one it doesn't work.

DECLARE @Test TABLE (TERMINAL_ID int,
TERMINAL_NAME varchar(200),
START_DATE datetime,
END_DATE datetime,
CHANGE_FLAG bit)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(705910) OKINAWA JP','20160501','20161231',1)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170101','20170630',0)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170701','20170831',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN','20170901','20171031',1)
INSERT INTO @Test VALUES(4184558, 'POST Diamond Creek F VicAU','20170501','20161031',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170501','20170630',1)

--select * from @Test

SELECT Test.TERMINAL_ID
,Test.TERMINAL_Name
,Test.START_DATE
,ISNULL(Tst.END_DATE, Test.END_DATE) AS End_Date
,Test.Change_Flag
FROM @TEst Test
LEFT JOIN @TEst Tst ON Test.TERMINAL_ID = Tst.TERMINAL_ID
AND DATEADD(dd,1,Test.End_Date) = Tst.Start_Date
AND Tst.CHANGE_FLAG = 0
WHERE Test.CHANGE_FLAG = 1

RE: Get Min and Max Time until it changes - Please Help

CODE

DECLARE @Test TABLE (TERMINAL_ID int,
                     TERMINAL_NAME varchar(200),
                     START_DATE datetime,
                     END_DATE datetime,
                     CHANGE_FLAG bit)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(705910) OKINAWA JP','20160501','20161231',1)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170101','20170630',0)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170701','20170831',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN','20170701','20170831',1)
INSERT INTO @Test VALUES(4184558, 'POST Diamond Creek F VicAU','20170501','20161031',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170501','20170630',1)

SELECT Test.TERMINAL_ID
      ,Test.TERMINAL_Name
      ,Test.START_DATE
      ,MAX(ISNULL(Tst.END_DATE, Test.END_DATE)) AS End_Date
      ,Test.Change_Flag
FROM @TEst Test
LEFT JOIN @TEst Tst ON Test.TERMINAL_ID = Tst.TERMINAL_ID
                   AND DATEADD(dd,1,Test.End_Date) = Tst.Start_Date
                   AND Tst.CHANGE_FLAG = 0
WHERE Test.CHANGE_FLAG = 1
GROUP BY Test.TERMINAL_ID
      ,Test.TERMINAL_Name
      ,Test.START_DATE
      ,Test.Change_Flag 
AGAIN!!!!
NOT PROPERLY TESTED!

Borislav Borissov
VFP9 SP2, SQL Server

RE: Get Min and Max Time until it changes - Please Help

(OP)
Great Answer Boris, Sorry to hassle again!
Forgot to tell you that the date not always sequence in for change_flag = 0, it could be a gap, like example below:

DECLARE @Test TABLE (TERMINAL_ID int,
TERMINAL_NAME varchar(200),
START_DATE datetime,
END_DATE datetime,
CHANGE_FLAG bit)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(705910) OKINAWA JP','20160501','20161231',1)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20170101','20170630',0)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP','20171001','20171231',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN','20180101','20180831',1)
INSERT INTO @Test VALUES(4184558, 'POST Diamond Creek F VicAU','20170501','20161031',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170501','20170630',1)

SELECT Test.TERMINAL_ID
,Test.TERMINAL_Name
,Test.START_DATE
,MAX(ISNULL(Tst.END_DATE, Test.END_DATE)) AS End_Date
,Test.Change_Flag
FROM @TEst Test
LEFT JOIN @TEst Tst ON Test.TERMINAL_ID = Tst.TERMINAL_ID
AND DATEADD(dd,1,Test.End_Date) = Tst.Start_Date
AND Tst.CHANGE_FLAG = 0
WHERE Test.CHANGE_FLAG = 1
GROUP BY Test.TERMINAL_ID
,Test.TERMINAL_Name
,Test.START_DATE
,Test.Change_Flag


expected output would be

CODE

TERMINAL_ID	TERMINAL_NAME	                   START_DATE	END_DATE	CHANGE_FLAG
1128565	        JAPANPOST BANK(705910) OKINAWA JP  1/05/2016	31/12/2017	1
1128565	        JAPAN RAMEN	                   1/07/2017	31/08/2017	1
4184558	        POST Diamond Creek F VicAU	   1/05/2016	31/10/2016	1
4184558	        BRIDGE CLINIC MURRAY BRIDGESA AU   1/05/2017	30/06/2017	1 

RE: Get Min and Max Time until it changes - Please Help

Just change:

CODE

AND DATEADD(dd,1,Test.End_Date) = Tst.Start_Date 
to

CODE

AND Test.End_Date < Tst.Start_Date 

Borislav Borissov
VFP9 SP2, SQL Server

RE: Get Min and Max Time until it changes - Please Help

(OP)
You are such a legend, Thank you Sir!

RE: Get Min and Max Time until it changes - Please Help

smile
Not sure, you must test it very well before use it in production.

Borislav Borissov
VFP9 SP2, SQL Server

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close