CREATING TABLE USING SQL BASED ON SEQUENTIAL DATES

[Michael Hardy]

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!

[Erland Sommarskog]

Michael Hardy (mi***********@kerzner.com) writes:

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.

I only vaguely recall your original thread, so I don't recall all
requirments. It is not clear from your example how different ratecodes,
prodnames and ratedefid would affect the result. Neither do I understand
the purpose of that parameter to the stored procedure. Here is some code
that finds the date intervals, that you may be able to work from.

SELECT x.BRAID, startdate = x.BRADATE, stopdate = y.BRADATE
INTO #temp
FROM (SELECT a.BRAID, a.BRADATE
FROM BASERATEAVAIL_Tmp a
WHERE NOT EXISTS
(SELECT *
FROM BASERATEAVAIL_Tmp b
WHERE b.BRADATE = dateadd(DAY, -1, a.BRADATE))) x
CROSS JOIN
(SELECT a.BRADATE
FROM BASERATEAVAIL_Tmp a
WHERE NOT EXISTS
(SELECT *
FROM BASERATEAVAIL_Tmp b
WHERE b.BRADATE = dateadd(DAY, +1, a.BRADATE))) y
WHERE x.BRADATE < y.BRADATE

DELETE #temp
FROM #temp a
WHERE EXISTS (SELECT *
FROM #temp b
WHERE b.startdate = a.startdate
AND b.stopdate < a.stopdate)

SELECT * FROM #temp

DROP TABLE #temp

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp