May 6, 2014 at 9:14 am
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
May 6, 2014 at 9:31 am
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/
May 6, 2014 at 10:19 am
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
Consultant - Straight Path Solutions
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
May 6, 2014 at 10:29 am
;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.
May 6, 2014 at 10:35 am
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.
May 6, 2014 at 10:38 am
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.
May 6, 2014 at 11:18 am
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
Consultant - Straight Path Solutions
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
May 7, 2014 at 6:39 am
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.
May 7, 2014 at 7:06 am
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
Consultant - Straight Path Solutions
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
May 7, 2014 at 7:16 am
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