Selecting Records Based on Date

  • So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate).

    I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.

    I can't just do something like:

    SELECT *

    FROM Orders

    WHERE ReceiptDate >= add_months(date,-6)

    because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.

    I am unsure of how to this in the most efficient way. I am a new user so I apologize if this question seems like a bad one.

    Thanks

  • jamesheslin123 (5/6/2014)


    So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate).

    I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.

    I can't just do something like:

    SELECT *

    FROM Orders

    WHERE ReceiptDate >= add_months(date,-6)

    because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.

    I am unsure of how to this in the most efficient way. I am a new user so I apologize if this question seems like a bad one.

    Thanks

    Hi and welcome to the forums. In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think you might want something like this:

    DECLARE @orders TABLE

    (

    OrderNo INT,

    ReceiptDate DATE

    );

    SELECT

    *

    FROM

    @orders AS O

    WHERE

    EXISTS ( SELECT

    1

    FROM

    @orders AS O2

    WHERE

    O2.OrderNo = O.OrderNo

    HAVING

    MAX(O2.ReceiptDate) >= DATEADD(MONTH, -6, CURRENT_TIMESTAMP) );

    I'm not saying there isn't a better way and since you are in the 2014 forum you might be able to do something with the windowing functions.

  • ;WITH cte ([Order#],ReceiptDate) AS (

    SELECT [Order#],MAX(ReceiptDate)

    FROM

    GROUP BY [Order#])

    SELECT t.Order#,t.ReceiptDate

    FROM cte

    JOIN

    t ON t.[Order#] = cte.[Order#]

    AND t.ReceiptDate >= DATEADD(month,-6,cte.ReceiptDate)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Or

    ;WITH cte ([Order#],ReceiptDate,MaxDate) AS (

    SELECT [Order#],ReceiptDate,

    MAX(ReceiptDate) OVER (PARTITION BY [Order#])

    FROM

    )

    SELECT [Order#],ReceiptDate

    FROM cte

    WHERE ReceiptDate >= DATEADD(month,-6,MaxDate)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Okay, here is what I got, apologies in advance if something is wrong... I did the best I could. I'm not too sure about the primary key portion.

    --===== If the test table already exists, drop it

    IF OBJECT_ID('TempDB..Deliveries','U') IS NOT NULL

    DROP TABLE Deliveries

    --===== Create the test table with

    CREATE TABLE Deliveries

    (

    ID INT PRIMARY KEY,

    Part VARCHAR(250),

    PartDescription VARCHAR(250),

    ReceiptDate TIMESTAMP(6),

    TimeBetween INT

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT Deliveries ON

    --===== Insert the test data into the test table

    INSERT INTO Deliveries (ID, Part, PartDescription, ReceiptDate, TimeBetween)

    SELECT '1', 'A','Nut','5/20/2014 12:00AM',-4 UNION ALL

    SELECT '2', 'A','Nut','4/20/2014 12:00AM',-3 UNION ALL

    SELECT '3', 'A','Nut','9/20/2013 12:00AM',-2 UNION ALL

    SELECT '4', 'A','Nut','8/20/2013 12:00AM',-1 UNION ALL

    SELECT '5', 'B','Bolt','8/20/2013 12:00AM', 0 UNION ALL

    SELECT '6', 'B','Bolt','7/20/2013 12:00AM', 4 UNION ALL

    SELECT '7', 'B','Bolt','1/20/2013 12:00AM', 15 UNION ALL

    SELECT '8', 'B','Bolt','6/20/2013 12:00AM', 6 UNION ALL

    SELECT '9', 'C','Rod','4/20/2014 12:00AM', 7 UNION ALL

    SELECT '10', 'C','Rod','11/20/2013 12:00AM', 8 UNION ALL

    SELECT '11', 'C','Rod','5/20/2013 12:00AM', -6 UNION ALL

    SELECT '12', 'C','Rod','4/20/2014 12:00AM', 5

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT Deliveries OFF

    For results, I want the query to select the most recent of each part and each part within the past 6 months of that respective most recent part. So, for the data above, this would be returned:

    '1', 'A','Nut','5/20/2014 12:00AM',-4

    '2', 'A','Nut','4/20/2014 12:00AM',-3

    '5', 'B','Bolt','8/20/2013 12:00AM', 0

    '6', 'B','Bolt','7/20/2013 12:00AM', 4

    '8', 'B','Bolt','6/20/2013 12:00AM', 6

    '9', 'C','Rod','4/20/2014 12:00AM', 7

    '10', 'C','Rod','11/20/2013 12:00AM', 8

    Thanks in advance. I'm sure that there is a simple solution, I am just new to SQL and haven't been able to find an efficient way to do it. Let me know if more information is required. Cheers.

  • The example makes what you need much clearer. Neither my nor David's code does exactly what you are looking for. I believe that this is what you want and that your example of desired results is incorrect and should include ID 12 as that is the most recent row for Part C - Rod:

    WITH maxDate

    AS (

    SELECT

    *,

    MAX(ReceiptDate) OVER (PARTITION BY part) AS maxDate,

    ROW_NUMBER() OVER(PARTITION BY part ORDER BY receiptDate DESC) AS rowNo

    FROM

    deliveries AS D

    )

    SELECT

    *,

    DATEDIFF(MONTH, MD.receiptDate, MD.maxDate)

    FROM

    maxDate AS MD

    WHERE

    DATEDIFF(MONTH, MD.receiptDate, MD.maxDate) <= 6

    ORDER BY ID;

    This returns:

    IDPartPartDescriptionReceiptDate TimeBetweenmaxDate rowNoMonths Between

    1ANut 2014-05-20 00:00:00.000-42014-05-20 00:00:00.00010

    2ANut 2014-04-20 00:00:00.000-32014-05-20 00:00:00.00021

    5BBolt 2013-08-20 00:00:00.000 02013-08-20 00:00:00.00010

    6BBolt 2013-07-20 00:00:00.000 42013-08-20 00:00:00.00021

    8BBolt 2013-06-20 00:00:00.000 62013-08-20 00:00:00.00032

    9CRod 2014-04-20 00:00:00.000 72014-04-20 00:00:00.00010

    10CRod 2013-11-20 00:00:00.000 82014-04-20 00:00:00.00035

    12CRod 2014-04-20 00:00:00.000 52014-04-20 00:00:00.00020

  • Jack Corbett (5/6/2014)


    Neither my nor David's code does exactly what you are looking for.

    Not that I would normally disagree with a master 🙂 but my second query produces the same results as yours 😀

    Changed only to use new table def

    ;WITH cte (ID, Part, PartDescription, ReceiptDate, TimeBetween,MaxDate) AS (

    SELECT ID, Part, PartDescription, ReceiptDate, TimeBetween,

    MAX(ReceiptDate) OVER (PARTITION BY Part)

    FROM Deliveries)

    SELECT ID, Part, PartDescription, ReceiptDate, TimeBetween,MaxDate

    FROM cte

    WHERE ReceiptDate >= DATEADD(month,-6,MaxDate)

    ORDER BY ID

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows (5/7/2014)


    Jack Corbett (5/6/2014)


    Neither my nor David's code does exactly what you are looking for.

    Not that I would normally disagree with a master 🙂 but my second query produces the same results as yours 😀

    Changed only to use new table def

    ;WITH cte (ID, Part, PartDescription, ReceiptDate, TimeBetween,MaxDate) AS (

    SELECT ID, Part, PartDescription, ReceiptDate, TimeBetween,

    MAX(ReceiptDate) OVER (PARTITION BY Part)

    FROM Deliveries)

    SELECT ID, Part, PartDescription, ReceiptDate, TimeBetween,MaxDate

    FROM cte

    WHERE ReceiptDate >= DATEADD(month,-6,MaxDate)

    ORDER BY ID

    Hehe, I only tested the first one you posted as a quick verification of what mine was doing.

  • Jack Corbett (5/7/2014)


    Hehe, I only tested the first one you posted as a quick verification of what mine was doing.

    Well the first one works as well 😛

    :hehe:

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 10 posts - 1 through 9 (of 9 total)

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