|
jayram
Starting Member
27 Posts |
 Posted - 01/22/2013 : 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
|
|
a way to combine/merge multiple continuous rows
