Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get Min and Max Time until it changes - Please Help 1

Status
Not open for further replies.
peac3

peac3

Technical User
Jan 17, 2009
226
AU
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
You are such a legend, Thank you Sir!
 
Upvote 0 Downvote
:)
Not sure, you must test it very well before use it in production.

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
227
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
333
Andrzejek
Andrzejek
peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
709
mikrom
mikrom
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
485
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
565
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top