Merge records to get sequence in TIME with condition, Please help...

[peac3]

Hello all,

I am trying to extract minimum maximum time from terminal ID until it changes the business according to our flag.

1 = means changing business
0 = same business

and I need to merge into one record from multiple records in multiple records in the sequence of time.
as per below codes

sample data

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 1','20170101','20170531',0)
INSERT INTO @Test VALUES(1128565, 'JAPANPOST BANK(003500) TOKYO JP 2','20170601','20170630',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN','20170701','20170831',1)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN AU','20170901','20170930',0)
INSERT INTO @Test VALUES(1128565, 'JAPAN RAMEN AUS','20171001','99991231',0)
INSERT INTO @Test VALUES(4184558, 'POST Diamond Creek F VicAU','20160501','20161031',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170501','20170630',1)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20170701','20171231',0)
INSERT INTO @Test VALUES(4184558, 'BRIDGE CLINIC MURRAY BRIDGESA AU','20180101','99991231',0)

Expected result:

1128565	JAPANPOST BANK(705910) OKINAWA JP	2016-05-01 00:00:00.000	2017-06-30 00:00:00.000	1
1128565	JAPAN RAMEN	                        2017-07-01 00:00:00.000	9999-12-31 00:00:00.000	1
4184558	POST Diamond Creek F VicAU	        2016-05-01 00:00:00.000	2016-10-31 00:00:00.000	1
4184558	BRIDGE CLINIC MURRAY BRIDGESA AU	2017-05-01 00:00:00.000	9999-12-31 00:00:00.000	1

any help would be much appreciated!

 

[peac3]

one way I was thinking to create additional column sequence first to handle within the same terminal I D.
So, if I see the Change Flag = 1, I will add number but if I see Change Flag = 0 I populate the same number as before

TERM_ID TERM_NAME                               STRT_D                   END_D                  CHANGE_F  SEQ
1128565	JAPANPOST BANK(705910) OKINAWA JP	2016-05-01 00:00:00.000	2016-12-31 00:00:00.000	1        1
1128565	JAPANPOST BANK(003500) TOKYO JP 1	2017-01-01 00:00:00.000	2017-05-31 00:00:00.000	0        1
1128565	JAPANPOST BANK(003500) TOKYO JP 2	2017-06-01 00:00:00.000	2017-06-30 00:00:00.000	0        1
1128565	JAPAN RAMEN	                        2017-07-01 00:00:00.000	2017-08-31 00:00:00.000	1        2
1128565	JAPAN RAMEN AU	                        2017-09-01 00:00:00.000	2017-09-30 00:00:00.000	0        2
1128565	JAPAN RAMEN AUS	                        2017-10-01 00:00:00.000	9999-12-31 00:00:00.000	0        2
4184558	POST Diamond Creek F VicAU	        2016-05-01 00:00:00.000	2016-10-31 00:00:00.000	1        3
4184558	BRIDGE CLINIC MURRAY BRIDGESA AU	2017-05-01 00:00:00.000	2017-06-30 00:00:00.000	1        4
4184558	BRIDGE CLINIC MURRAY BRIDGESA AU	2017-07-01 00:00:00.000	2017-12-31 00:00:00.000	0        4
4184558	BRIDGE CLINIC MURRAY BRIDGESA AU	2018-01-01 00:00:00.000	9999-12-31 00:00:00.000	0        4

Any idea to do this without using loop?
Thanks.

 

[fredericofonseca]

what version of SQL server you going to use for this?

for 2008 R2 + the following will work - depending on your volumes spliting the 2 CTE's onto temp tables with support indexes may be better on performance

;
with rownumbers
as
(select *
      , row_number() over (partition by TERMINAL_ID order by START_DATE) as rownum
       from @Test
),
groupnumbers
as
(select *
      , row_number() over (partition by TERMINAL_ID order by START_DATE) as grp

       from rownumbers w1
       where w1.CHANGE_FLAG = 1
)
select w1.TERMINAL_ID
     , w1.TERMINAL_NAME
     , w1.START_DATE
     --, w1.END_DATE
     , w1.CHANGE_FLAG
     --, w1.rownum
     --, w1.grp
     --, endgroup.TERMINAL_ID
     --, endgroup.TERMINAL_NAME
     --, endgroup.START_DATE
     , endgroup.END_DATE
     --, endgroup.CHANGE_FLAG
     --, endgroup.rownum
from groupnumbers w1
outer apply (select top 1 *
       from groupnumbers g2
       where g2.TERMINAL_ID = w1.TERMINAL_ID
           and g2.grp = w1.grp + 1
) nextgroup

outer apply (select top 1 *
       from rownumbers rn1
       where rn1.TERMINAL_ID = w1.TERMINAL_ID
           and rn1.rownum < coalesce(nextgroup.rownum, 9999999999)
   order by rn1.rownum desc
) endgroup
order by w1.TERMINAL_ID
       , w1.START_DATE

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886