none
Date Issue

    Question

  • I have a table that contains a column with a part name, the date the price changed, and the amount of the new price. The table may contain the same part multiple times with different dates and prices. How do I create a statement that will give me the part, price, start and end date?

    Thanks,
    Scott

    Thursday, April 10, 2014 7:17 PM

Answers

All replies

  • What version of SQL Server are you using?

    If you have an entry for when the part was inserted for the first time into the database then you could try:

    select
        part_name,
        price,
        dt as start_dt,
        dateadd([day], -1, lead(dt) over(partition by part_name order by dt)) as end_dt
    from
        parts
    order by
        part_name, dt;

    You will have a row for each part where the [end_dt] will be the NULL mark, meaning it is the last change received for that part. Also, supposing you can't have more than one change per part during a day then substracting one day to the next date when the part was changed will give you the end date.


    AMB

    Some guidelines for posting questions...




    Thursday, April 10, 2014 7:27 PM
  • ;with cte as (select part, min([date]) as StartDate, max([date]) as EndDate

    from PartsPrices 

    GROUP BY Part)

    select P.Part, P.Price as CurrentPrice, Mx.StartDate, Mx.EndDate

    from PartsPrices P inner join cte Mx on P.[Date] = Mx.[EndDate]

    AND P.Part = Mx.Part



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, April 10, 2014 7:32 PM
  • I am on sql 2008 R2.

    Lead is not recognized. I am guessing that is a 2012 function.

    Thanks for the suggestion.

    Thursday, April 10, 2014 8:19 PM
  • Your query works!

    Thank you,

    Scott

    Thursday, April 10, 2014 8:19 PM
  • To track the history of, say, Foobars we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton. 

    CREATE TABLE Foobar_History 
    (foo_id CHAR(9) NOT NULL, 
     start_date DATE NOT NULL, 
     end_date DATE, --null means current 
     CHECK (start_date <= end_date),
     foo_status INTEGER NOT NULL, 
     PRIMARY KEY (foo_id, start_date)); 

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT * 
      FROM Foobar
     WHERE @in_cal_date
         BETWEEN start_date
          AND COALESCE (end_date, CURRENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc

    CREATE TABLE Events
    (event_id CHAR(10) NOT NULL,
     previous_event_end_date DATE NOT NULL  
     CONSTRAINT Chained_Dates  
      REFERENCES Events (event_end_date), 
     event_start_date DATE NOT NULL, 
     event_end_date DATE UNIQUE, -- null means event in progress
      PRIMARY KEY (event_id, event_start_date), 
     CONSTRAINT Event_Order_Valid 
      CHECK (event_start_date <= event_end_date), 
     CONSTRAINT Chained_Dates 
      CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
    -- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
    GO

    -- enable the constraint in the table
    ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, April 11, 2014 1:02 AM
  • It looks like I spoke to soon. I added additional data to the source and reran the query. I am getting a hundred pluss identical rows back. I tried unmarking your response as the answer but the web page through an error.

    Thanks,

    Scott

    Friday, April 11, 2014 1:46 PM
  • I found how to create "LEAD" in SQL 2008.

    http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

    Based on this I was able to get the data.

    Thank you,
    Scott

    Friday, April 11, 2014 3:15 PM
  • I re-read the query and noticed I missed one JOIN condition based on the part. So, I added it and fixed the query. It should work just fine for your purpose although you can have another variation of it using ROW_NUMBER() function, for example. The variation of query I used will return duplicates in case you have several same max dates for the part.

    Take a look at these blogs

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx#Select_Top_N_Rows_per_Group


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Friday, April 11, 2014 3:21 PM
  • Naomi,

    I appreciate your time with this. Unfortunatly your revised query does not work either. It is only generating one row per part and it appears to be for the most recent time frame that has an end date.

    Friday, April 11, 2014 6:19 PM
  • Yes, this is what the query is doing. If this is not what you wanted, can you please provide the DDL, some insert statements and desired output?

    I understood you had a history of prices for the part and wanted to get current price and date+ the start date?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 11, 2014 6:23 PM
  • I want the output to be:

    part, start date, end date, price

    In cases when the part has had different prices at different times there should be one row for each time period. That row would give the price for that part beween those dates. The final row for each part would have a null end date since it is still the current price.

    I was able to solve the problem by using the link I mentioned earlier. It is however very slow and complicated. I would provide you with DDL but my actual data is set up a little differently. I simplified my question for the post.

    Thank you,
    Scott

    Friday, April 11, 2014 6:47 PM
  • I see, you have

    Part1 Date1 Price1

    Part1 Date2 Price2

    Part1 Date3 Price3

    and you want

    Part1 StartDate EndDate Price1

    Part1 StartDate (Date2) EndDate (Date3) Price2

    Part1 StartDate (Date3) EndDate (Null) Price3

    ------------------------------------

    Right? If that's the case, in SQL prior to SQL 2012 row_number or OUTER APPLY solution is the best you can get and it may be a bit slow. Let me know if you want me to write one based on the above assumption.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 11, 2014 6:53 PM
  • That is exactly what I want.

    I have gotten it to work using multiple partition over statements and a cursor. If you have a faster method I would love to use it.

    Thanks,
    Scott

    Friday, April 11, 2014 8:34 PM
  • ;with cte as (select *, row_number() over (partition by Part Order by ChangeDate) as Rn from PartsTable)

    select c1.ChangeDate as StartDate, c2.ChangeDate as EndDate, c1.Part, c1.Price

    from cte c1 LEFT JOIN cte c2 on c1.Part = c2.Part

    and c1.Rn = c2.Rn - 1



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, April 11, 2014 9:49 PM