a way to combine/merge multiple continuous rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

a way to combine/merge multiple continuous rows

Author

Topic

jayram
Starting Member

47 Posts

Posted - 2013-01-22 : 12:04:36

Is there a way to combine/merge multiple continuous rows by date into a single row without using a loop/cursor?
the following is my table structure.

CREATE TABLE [dbo].[RATES](
[STATE] [varchar](2) NULL,
[HCPCS] [char](5) NOT NULL,
[MOD1] [char](2) NOT NULL,
[MOD2] [char](2) NOT NULL,
[EFF_BEG] [smalldatetime] NOT NULL,
[EFF_END] [smalldatetime] NOT NULL,
[IMP_BEG] [smalldatetime] NOT NULL,
[IMP_END] [smalldatetime] NOT NULL,
[LINK] [varchar](10) NULL,
[CHANGES] [int] NULL,
[JURIS] [varchar](2) NULL,
[CATG] [char](2) NULL,
[CEILING] [float] NULL,
[FLOOR] [float] NULL,
[RATE] [float] NULL
) ON [PRIMARY]

STATE HCPCS MOD1 MOD2 EFF_BEG EFF_END IMP_BEG IMP_END LINK CHANGES JURIS CATG CEILING FLOOR RATE
US A4214 -- -- 1998-01-01 00:00:00 1998-12-31 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.67 1.42 1.67
US A4214 -- -- 1999-01-01 00:00:00 1999-12-31 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 0 D OS 1.67 1.42 1.67
US A4214 -- -- 2000-01-01 00:00:00 2000-12-31 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 0 D OS 1.67 1.42 1.67
US A4214 -- -- 2001-01-01 00:00:00 2001-12-31 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.79 1.52 1.79
US A4214 -- -- 2002-01-01 00:00:00 2002-12-31 00:00:00 2002-01-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.73 1.47 1.73
US A4214 -- -- 2003-01-01 00:00:00 2003-12-31 00:00:00 2003-01-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.75 1.49 1.75
US A4214 -- -- 2004-01-01 00:00:00 2004-12-31 00:00:00 2003-01-01 00:00:00 2045-12-31 00:00:00 NULL 16 D OS 1.75 1.49 1.75

what i need is the following where row 2 and 3 are merged from above which i get by using the below script

STATE HCPCS MOD1 MOD2 (No column name) (No column name) imp_beg IMP_END LINK changes juris CATG CEILING FLOOR RATE
US A4214 -- -- 1998-01-01 00:00:00 1998-01-01 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.67 1.42 1.67
US A4214 -- -- 1999-01-01 00:00:00 2000-01-01 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 0 D OS 1.67 1.42 1.67
US A4214 -- -- 2001-01-01 00:00:00 2001-01-01 00:00:00 2001-07-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.79 1.52 1.79
US A4214 -- -- 2002-01-01 00:00:00 2002-01-01 00:00:00 2002-01-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.73 1.47 1.73
US A4214 -- -- 2003-01-01 00:00:00 2003-01-01 00:00:00 2003-01-01 00:00:00 2045-12-31 00:00:00 NULL 6 D OS 1.75 1.49 1.75
US A4214 -- -- 2004-01-01 00:00:00 2004-01-01 00:00:00 2003-01-01 00:00:00 2045-12-31 00:00:00 NULL 16 D OS 1.75 1.49 1.75

i use a group by with Min and Max which is straight forward but if there are any gaps in dates for EFF_BEG and EFF_END that is not going to be captured.

select STATE, HCPCS, MOD1, MOD2, MIN(EFF_BEG), MAX(EFF_END), imp_beg, IMP_END, LINK, changes, juris, CATG, CEILING, FLOOR, RATE
from RATES
group by STATE, HCPCS, MOD1, MOD2, imp_beg, IMP_END, LINK, changes, juris, CATG, CEILING, FLOOR, RATE
order by STATE, HCPCS, MOD1, MOD2, MIN(EFF_BEG), MAX(EFF_END), imp_beg, IMP_END, LINK, changes, juris, CATG, CEILING, FLOOR, RATE

is there a way which takes gaps in dates into account?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 01:11:37

in case there are any gaps you still want them to be in different rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jayram
Starting Member

47 Posts

Posted - 2013-01-23 : 10:01:59

Yes. thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources