CREATING TABLE USING SQL BASED ON SEQUENTIAL DATES

I would like to reopen this discussion. I need to create this table
using SQL that will return this dataset.

BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME
614 14 12/1/2002 12/4/2004 Rack Beach
Tower Terrace
618 14 12/8/2002 12/11/2004 Rack Beach
Tower Terrace
622 14 12/15/2002 12/19/2004 Rack Beach
Tower Terrace


I have provided the test data below and the SP I have been working with.
The SP works fine as long as there are 4 seqential dates. I get
unpredictable results when the count <> 4. I am also concerned on what
happens at the end of the month. For example, 1/31/2004, 2/1/2004.
Does this affect the SP? I appreciate your continued support.

Michael

CREATE TABLE [dbo].[BASERATEAVAIL_Tmp] (
[BRAID] [decimal](18, 0) NOT NULL ,
[RATEDEFID] [decimal](18, 0) NOT NULL ,
[BRADATE] [datetime] NOT NULL ,
[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
,
[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]

GO
INSERT INTO dbo.BASERATEAVAIL_Tmp
(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');
INSERT
INTO dbo.BASERATEAVAIL_Tmp(BRAID,
RATEDEFID, BRADATE, RATECODE, PRODNAME)
VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');


CREATE PROCEDURE usp @n INT
AS
SET ANSI_WARNINGS OFF
SELECT MIN( braid) AS "braid",
ratedefid, ratecode, prodname,
MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",
MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"
FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,
t1.bradate,
COUNT( * ) % @n AS "n",
( COUNT( * ) - 1 ) / @n AS "r"
FROM BASERATEAVAIL_Tmp t1
INNER JOIN BASERATEAVAIL_Tmp t2
ON t2.bradate <= t1.bradate
GROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,
t1.bradate
) D
GROUP BY r, ratedefid, ratecode, prodname
HAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;

Usage:

EXEC usp @n = 4

--
Anith


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!