SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting Records Based on Date


Selecting Records Based on Date

Author
Message
jamesheslin123
jamesheslin123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26400 Visits: 17557
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.

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)
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18902 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9701 Visits: 9764

;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.


David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9701 Visits: 9764
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.


jamesheslin123
jamesheslin123
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18902 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9701 Visits: 9764
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 :-D

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.


Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18902 Visits: 14900
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 :-D

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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9701 Visits: 9764
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 :-P
Hehe


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

Anon.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search