Query that gets sets of 3 rows based on given date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query that gets sets of 3 rows based on given date

Author

Topic

andy_d
Starting Member

4 Posts

Posted - 2014-08-16 : 22:01:00

I need to get 3 rows per set based on a date given (must be this date) but I want the rows to be based on this date as...

1 ( row where the date from the date column is the next date after given date )
0 ( row where the date is the closest date prior to the date given )
-1 ( prior to the date at 0 )

And add a column with the relative number.

** The dates for the same name and item will never repeat.

For example, a set of rows...

Row ID, Name, Item, Number, Date
1 Andy, Item1, 12030, 2014-06-30
2 Andy, Item1, 62030, 2014-03-31
3 Andy, Item1, 30300, 2013-12-31
4 Andy, Item1, 40030, 2013-10-31
5 Andy, Item1, 50030, 2013-08-30
6 John, Item2, 50240, 2014-04-30
7 John, Item2, 41400, 2014-03-31
8 John, Item2, 40509, 2014-01-31
9 Andy, Item2, 24004, 2014-03-31
10 Andy, Item2, 20144, 2013-12-31
11 Andy, Item2, 20450, 2013-09-30
12 Andy, Item2, 25515, 2013-06-30

If I have 2014-03-15 as the date and search for 'Andy', I expect...

Row ID, Item, Date, Relative Date
2, Item1, 2014-03-31, 1
3, Item1, 2013-12-31, 0
4, Item1, 2013-10-31, -1
9, Item2, 2014-03-31, 1
10, Item2, 2013-12-31, 0
11, Item2, 2013-09-30, -1

This is what I'm using which I have no issues switching if necessary...

DATEDIFF( quarter, 2014-03-31, date )

date BETWEEN DATEADD( quarter, -1, '20140315' ) AND
DATEADD( day, 1 ( DATEADD ( quarter, 2, '20140315' ) )

which returns...

Row ID, Item, Date, Relative Date
2, Item1, 2014-06-30, 1
3, Item1, 2014-03-31, 0
4, Item1, 2013-12-31, -1
9, Item2, 2014-03-31, 0
10, Item2, 2013-12-31, -1

Not sure if date math is the best option here. Perhaps using row_number() in some way?

I'm trying to avoid having to process the entire table programmatically given the size of the data set.

Let me know if I need to clarify anything.

andy_d
Starting Member

4 Posts

Posted - 2014-08-17 : 00:10:13

I've made a few revisions hoping it's a bit clearer. I can fuss around with the between line to get the dates I want but the relative number I add as a column is then off

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-08-17 : 02:33:39

[code]select *
from
(
select top 1 *
from table
where date > '2014-03-15'
order by date
) a

union all
select *
from
(
select top 2 *
from table
where date < '2014-03-15'
order by date desc
) b
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-17 : 03:18:35

To add the relative date field, add the red text to khtan's query.

quote:

Originally posted by khtan

select b.*
      ,row_number()-2 over(order by b.date desc) as rel_date
from
(
    select top 1 *
    from   table
    where  date > '2014-03-15'
    order by date
) a

union all
select *
from
(
    select top 2 *
    from   table
    where  date < '2014-03-15'
    order by date desc
) b

andy_d
Starting Member

4 Posts

Posted - 2014-08-17 : 08:43:53

Thanks guys! Sorry two things...

1) I've revised the above to clarify a major thing. I don't just want exactly 3 rows. I'm looking for sets of three rows. Don't think "Top" will work for that?
2) row_number() will work but when I tried it I get an error because of the subtracting of two? It doesn't seem to like that. Is there any way to subtract it differently?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-17 : 10:34:50

quote:
Originally posted by andy_d

Thanks guys! Sorry two things...

1) I've revised the above to clarify a major thing. I don't just want exactly 3 rows. I'm looking for sets of three rows. Don't think "Top" will work for that?
2) row_number() will work but when I tried it I get an error because of the subtracting of two? It doesn't seem to like that. Is there any way to subtract it differently?

The subtraction of 2 should be done after the "over" section - my mistake, sorry.
Try this:

select *
from
(
    select *
          ,row_number() over (partition by [name] order by [date]) as rel_date
    from   table
    where  [date]>'2014-03-15'
) a

union all
select *
from
(
    select *
          ,rownumber() over (partition by [name] order by [date] desc)-2 as rel_date
    from   table
    where  [date]<'2014-03-15'
) b
where rel_date<=1

Edit: forgot descending in the second select/order by.

andy_d
Starting Member

4 Posts

Posted - 2014-08-17 : 11:29:23

No worries! I appreciate the help. i got the -2 part but wouldn't the above return all dates instead of the range?

I tried it on my end and the dates included are outside of it.

Is there any way to pick rows instead of going by date? Meaning something like row_number()-1 for the first match. and then + 1 , -1 for the next two?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2014-08-17 : 11:39:13

Hi Andy,

khtan and bitsmed solutions should work, just need some modification for your new requirement.

I think you should include insert statements for your sample data in the post. That is a great help for people who try to help you.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-17 : 11:53:34

quote:
Originally posted by namman

Hi Andy,

khtan and bitsmed solutions should work, just need some modification for your new requirement.

I think you should include insert statements for your sample data in the post. That is a great help for people who try to help you.

Again I'm sorry - I must confess, I didn't test it on my own db, so there were both syntax errors and the result was not as I expected.

Try this instead (this time I have tested it):

select *
from
(
    select *
          ,row_number() over (partition by [name] order by [date]) as rel_date
    from   table
    where  [date]>'2014-03-15'
    union all
    select *
          ,row_number() over (partition by [name] order by [date] desc)-2 as rel_date
    from   table
    where  [date]<'2014-03-15'
) a
where rel_date<=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources