SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 a way to combine/merge multiple continuous rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

47 Posts

Posted - 01/22/2013 :  12:04:36  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/23/2013 :  01:11:37  Show Profile  Reply with Quote
in case there are any gaps you still want them to be in different rows?

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

Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 01/23/2013 :  10:01:59  Show Profile  Reply with Quote
Yes. thanks.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000