Post reply

Query that generates values by date range

  • December 12, 2014 at 7:07 am

    #299413

    Hi,

    I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.

    SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)

    ,d.fmc_prodlnk, d. fmc_cost

    INTO #TestTable

    FROM (

    SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL

    SELECT 1, '2014-12-03 11:33:00.000','A', 2.26 UNION ALL

    SELECT 1, '2014-12-04 00:04:00.000','A', 2.27 UNION ALL

    SELECT 1, '2014-12-05 00:04:00.000','A', 2.28 UNION ALL

    SELECT 1, '2014-12-05 11:04:00.000','A', 2.29 UNION ALL

    SELECT 2, '2014-12-01 15:04:00.000','A', 2.20 UNION ALL

    SELECT 2, '2014-12-02 18:08:00.000','A', 2.31 UNION ALL

    SELECT 2, '2014-12-03 13:13:00.000','A', 2.39 UNION ALL

    SELECT 1, '2014-12-01 00:04:00.000','B', 5.54 UNION ALL

    SELECT 1, '2014-12-01 12:31:00.000','B', 5.55 UNION ALL

    SELECT 1, '2014-12-02 14:04:00.000','B', 5.64 UNION ALL

    SELECT 2, '2014-12-03 12:12:00.000','B', 5.51 UNION ALL

    SELECT 1, '2014-12-01 00:04:00.000','C', 7.23 UNION ALL

    SELECT 1, '2014-12-04 00:04:00.000','C', 6.22 UNION ALL

    SELECT 1, '2014-12-05 00:04:00.000','C', 6.23 UNION ALL

    SELECT 1, '2014-12-06 00:04:00.000','C', 6.24 UNION ALL

    SELECT 1, '2014-12-12 10:04:00.000','C', 3.99

    ) d ( fmc_terminal, fmc_date, fmc_prodlnk, fmc_cost)

    ;

    Expected outcome

    fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate

    1A2.25 NULL2014-12-03 00:04:00.000

    1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000

    1A2.27 2014-12-03 11:33:00.0002014-12-04 00:04:00.000

    1A2.28 2014-12-04 00:04:00.0002014-12-05 00:04:00.000

    1A2.29 2014-12-04 00:04:00.0002014-12-05 11:04:00.000

    2A2.20 NULL2014-12-01 15:04:00.000

    2A2.31 2014-12-01 15:04:00.0002014-12-02 18:08:00.000

    2A2.39 2014-12-02 18:08:00.0002014-12-03 13:13:00.000

    1B5.54 NULL2014-12-01 00:04:00.000

    And so on….

  • December 12, 2014 at 7:30 am

    #1764420

    SELECT fmc_terminal

    , fmc_prodlnk

    , fmc_cost

    , lag(fmc_date)OVER(PARTITION BY fmc_terminal

    , fmc_prodlnk

    ORDER BY fmc_date) AS StartDate

    , fmc_date AS EndDate

    FROM #TestTable

    ORDER BY fmc_terminal, fmc_prodlnk;

    Gerald Britton, Pluralsight courses

  • December 12, 2014 at 7:33 am

    #1764422

    I have SQL 2008, LAG wouldn't work

  • December 12, 2014 at 7:37 am

    #1764424

    legeboka (12/12/2014)

    I have SQL 2008, LAG wouldn't work

    You realise you're in the 2012 forum? 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • December 12, 2014 at 8:56 am

    #1764462

    Something like this should work as well, its not as neat as the LAG function but

    WITH CTE_RN

    AS

    (

    SELECT

    fmc_terminal

    , fmc_prodlnk

    , fmc_cost

    , fmc_date

    , ROW_NUMBER() OVER(PARTITION BY fmc_terminal

    , fmc_prodlnk

    ORDER BY fmc_date) AS RN

    , fmc_date AS EndDate

    FROM

    #TestTable

    )

    SELECT

    Curr.*

    ,Prev.fmc_Date as StartDate

    FROM

    CTE_RN Curr

    LEFT JOIN CTE_RN Prev

    ON

    curr.fmc_terminal=prev.fmc_terminal

    AND Curr.fmc_prodlnk=Prev.fmc_prodlnk

    AND Curr.Rn=Prev.Rn+1

    ORDER BY fmc_terminal, fmc_prodlnk;

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply