Home Ignoring rows within daterange
Reply: 0

Ignoring rows within daterange

user8794
1#
user8794 Published in September 19, 2018, 6:44 pm

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?

share|improve this question
  • and what if an order overlaps so that it results to true for the second and third bullet statement? – scsimon Nov 13 '17 at 20:35
  • your insert statement is missing the orderid field – maSTAShuFu Nov 13 '17 at 20:41
  • @scsimon, If the name and date are the same, then the row with the lowest orderid is the superior order – SparteyNL Nov 13 '17 at 20:44
  • @maSTAShuFu, sharp! I removed the IDENTITY, that's why – SparteyNL Nov 13 '17 at 20:45

2 Answers 2

active oldest votes
up vote 0 down vote
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO