Home Ignoring rows within daterange
Reply: 0

Ignoring rows within daterange

user1248
1#
user1248 Published in June 22, 2018, 11:32 am

I have the following data:

CREATE TABLE SampleData
(
    orderid int,
    [name] nvarchar(1),
    [date] date
);
INSERT INTO SampleData
VALUES
(1, 'a', '2017-01-01'),
(2, 'a', '2017-01-05'),
(3, 'a', '2017-02-01'),
(4, 'a', '2017-04-01'),
(5, 'a', '2017-10-01'),
(6, 'b', '2017-04-01');

I need to retrieve each new order according to the following rules:

  • The first date for a name is the 'current order' for that name
  • Orders with the same name, but less than 3 months difference with the 'current order' is considered the same order and needs to be ignored
  • 3 months or more difference with the 'current' order is considered a new order and is now the 'current order' (in the SampleData orderid 1 and 4 need to be compared instead of 3 and 4, because 3 is not the current order)
  • If the name and date are the same, then the row with the lowest orderid is the superior order

So with the sample data I need the following result:

id name, date
1  a     2017-01-01
4  a     2017-04-01
5  a     2017-10-01
6  b     2017-04-01

I tried several approaches, but without success. Any idea's on how I can achieve this?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.341759 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO