Home Forums SQL Server 2005 T-SQL (SS2K5) TSQL Return start/end price and start/end datetime by product name. RE: TSQL Return start/end price and start/end datetime by product name.

  • June 2, 2010 at 6:52 am

    #1175252

    This should be reasonably quick, and it's simple to understand and use, too:

    DROP TABLE #Temp

    CREATE TABLE #Temp (id INT, [Name] VARCHAR(40), Price Money, [DateTime] DATETIME)

    INSERT INTO #Temp (id, [Name], Price, [DateTime])

    SELECT 4,'$49.95 Value Pack', 0.1693, 'Feb 6 2010 10:24PM' UNION ALL -- start of price/product

    SELECT 69,'$49.95 Value Pack', 0.1693, 'Feb 6 2010 11:04PM' UNION ALL

    SELECT 95,'$49.95 Value Pack', 0.1693, 'Feb 6 2010 11:09PM' UNION ALL

    SELECT 289,'$49.95 Value Pack', 0.1693, 'Feb 7 2010 1:00PM' UNION ALL

    SELECT 313,'$49.95 Value Pack', 0.1693, 'Feb 7 2010 3:00PM' UNION ALL

    SELECT 373,'$49.95 Value Pack', 0.1693, 'Feb 7 2010 8:00PM' UNION ALL

    SELECT 397,'$49.95 Value Pack', 0.1693, 'Feb 7 2010 10:00PM' UNION ALL -- end of price/product

    SELECT 2674,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 5:05AM' UNION ALL -- start of price/product

    SELECT 2698,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 7:05AM' UNION ALL

    SELECT 2782,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 11:09AM' UNION ALL

    SELECT 2806,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 1:05PM' UNION ALL

    SELECT 2866,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 6:05PM' UNION ALL

    SELECT 2890,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 8:05PM' UNION ALL

    SELECT 2914,'$49.95 Value Pack', 0.1505, 'Feb 15 2010 10:05PM' UNION ALL

    SELECT 2974,'$49.95 Value Pack', 0.1505, 'Feb 16 2010 3:05AM' UNION ALL

    SELECT 2998,'$49.95 Value Pack', 0.1505, 'Feb 16 2010 5:05AM' UNION ALL -- end of price/product

    SELECT 5354,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 10:35AM' UNION ALL -- start of price/product

    SELECT 5380,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 11:05AM' UNION ALL

    SELECT 5471,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 12:50PM' UNION ALL

    SELECT 5497,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 1:20PM' UNION ALL

    SELECT 5562,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 2:35PM' UNION ALL

    SELECT 5588,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 3:05PM' UNION ALL

    SELECT 5614,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 3:35PM' UNION ALL

    SELECT 5679,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 4:50PM' UNION ALL

    SELECT 5705,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 5:20PM' UNION ALL

    SELECT 5796,'$49.95 Value Pack', 0.1411, 'Feb 18 2010 7:05PM' UNION ALL -- OPEN end of price/product

    SELECT 89977,'Flower Power Top', 0.2007, 'Apr 25 2010 3:35AM' UNION ALL -- start of price/product

    SELECT 90103,'Flower Power Top', 0.2007, 'Apr 25 2010 5:50AM' -- OPEN end of price/product

    ;WITH AggregatedData AS (

    SELECT Seq = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY MIN([DateTime])),

    [Name], Price, MIN([DateTime]) AS StartDate

    FROM #Temp

    GROUP BY [Name], Price

    )

    SELECT a.Name, a.Price, a.StartDate, b.StartDate AS EndDate

    FROM AggregatedData a

    LEFT JOIN AggregatedData b ON b.Name = a.Name AND b.seq = a.Seq+1

    ORDER BY a.[Name], a.Price

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden