Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Selecting Records Based on Date Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:39 PM
Points: 9, Visits: 30
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
Post #1568026
Posted Tuesday, May 6, 2014 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:54 PM
Points: 13,221, Visits: 12,699
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1568042
Posted Tuesday, May 6, 2014 10:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 10,342, Visits: 13,347
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.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1568071
Posted Tuesday, May 6, 2014 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,003, Visits: 7,161
;WITH cte  ([Order#],ReceiptDate) AS (
SELECT [Order#],MAX(ReceiptDate)
FROM [table] GROUP BY [Order#])
SELECT t.Order#,t.ReceiptDate
FROM cte
JOIN [table] 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.

Post #1568076
Posted Tuesday, May 6, 2014 10:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,003, Visits: 7,161
Or
;WITH cte  ([Order#],ReceiptDate,MaxDate) AS (
SELECT [Order#],ReceiptDate,
MAX(ReceiptDate) OVER (PARTITION BY [Order#])
FROM [table])
SELECT [Order#],ReceiptDate
FROM cte
WHERE ReceiptDate >= DATEADD(month,-6,MaxDate)




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

Anon.

Post #1568082
Posted Tuesday, May 6, 2014 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 12:39 PM
Points: 9, Visits: 30
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.
Post #1568085
Posted Tuesday, May 6, 2014 11:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 10,342, Visits: 13,347
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:

ID	Part	PartDescription	ReceiptDate	    TimeBetween	maxDate	              rowNo	Months Between
1 A Nut 2014-05-20 00:00:00.000 -4 2014-05-20 00:00:00.000 1 0
2 A Nut 2014-04-20 00:00:00.000 -3 2014-05-20 00:00:00.000 2 1
5 B Bolt 2013-08-20 00:00:00.000 0 2013-08-20 00:00:00.000 1 0
6 B Bolt 2013-07-20 00:00:00.000 4 2013-08-20 00:00:00.000 2 1
8 B Bolt 2013-06-20 00:00:00.000 6 2013-08-20 00:00:00.000 3 2
9 C Rod 2014-04-20 00:00:00.000 7 2014-04-20 00:00:00.000 1 0
10 C Rod 2013-11-20 00:00:00.000 8 2014-04-20 00:00:00.000 3 5
12 C Rod 2014-04-20 00:00:00.000 5 2014-04-20 00:00:00.000 2 0





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1568106
Posted Wednesday, May 7, 2014 6:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,003, Visits: 7,161
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.

Post #1568419
Posted Wednesday, May 7, 2014 7:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:19 PM
Points: 10,342, Visits: 13,347
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1568445
Posted Wednesday, May 7, 2014 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,003, Visits: 7,161
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



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

Anon.

Post #1568449
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse