Way to find the maximum dates with no futuristic dates

Question

I'm trying to list the latest Quantity (MAX EffectiveDate time avoiding Futuristic EffectiveDate) for each Code in a table

Code     Quantity EffectiveDate
1        2        1-Mar-2018
1        3        1-Apr-2018
1        3        1-May-2018
1        3        1-June-2018
2        5        1-Mar-2018
2        6        1-Apr-2018
3        7        1-Feb-2018
3        8        1-Mar-2018
3        5        1-Apr-2018

The Desired output should be:

Code     Quantity EffectiveDate
1        3        1-May-2018
2        6        1-Apr-2018
3        5        1-Apr-2018

I tried the solution from GROUP BY with MAX(DATE) But it is showing futuristic data.

@Larnu it sure looks like is a probability based on the sample data posted. Which would certainly explain why MAX isn't doing what they want. — Sean Lange, May 16 '18 at 14:57
@SeanLange Possibly. I do wonder though, as if they are storing it as a `varchar` though. If they were, that means that all the dates they have stored are on the first of the month. With `varchar` dates in the format `dd-MMM-yyyy` format, `MAX` is most likely going to be bringing back dates with the day having a value of `30` or `31`. — Thom A, May 16 '18 at 15:06
I figure it must be varchar because not all the months in the sample data have 2 characters. June has four where all the other are 3. ;) — Sean Lange, May 16 '18 at 15:07

Lukasz Szozda · Accepted Answer · 2018-05-16T14:46:44.007

6

To get full rows you could use:

SELECT TOP 1 WITH TIES *
FROM table
WHERE EffectiveDate <= GETDATE()
ORDER BY ROW_NUMBER() OVER(PARTITION BY code ORDER BY EffectiveDate DESC);

DBFiddle Demo

EDIT:

If among one code group ties are possible, then you could use RANK:

SELECT TOP 1 WITH TIES *
FROM tab
WHERE EffectiveDate <= GETDATE()
ORDER BY RANK() OVER(PARTITION BY code ORDER BY EffectiveDate DESC);

DBFiddle Demo 2

edited May 16 '18 at 14:46

answered May 16 '18 at 14:16

Lukasz Szozda

162,964
23
234
275

1

I'm a true fan of the combiation of *`TOP 1 WITH TIES` and `ORDER BY ROW_NUMBER()`*. I've used this quite often myself. – Shnugo May 16 '18 at 14:28
2

@Shnugo I love it too. Especially when combined with `RANK` instead of `ROW_NUMBER`. Then I get ties in groups(PARTITION BY) and for all groups. **[Demo with RANK](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c1712a8ee372a7ad99d8e194d6a84b96)** – Lukasz Szozda May 16 '18 at 14:30

Thom A · Answer 2 · 2018-05-16T14:24:39.780

It depends what your goal is here. If you want to ignore if the MAX date is in the future, you need to use HAVING:

SELECT Code,
       Quantity,
       MAX(EffectiveDate)
FROM YourTable
GROUP BY Code, Quantity
HAVING MAX(EffectiveDate) <= GETDATE();

If, however, you want the MAX date which is less than the current date, use a WHERE:

SELECT Code,
       Quantity,
       MAX(EffectiveDate)
FROM YourTable
WHERE EffectiveDate <= GETDATE()
GROUP BY Code, Quantity;

Your question is unclear on which answer is correct; and we can't infer from any sample data.

Edit:

If we use the following Sample data:

CREATE TABLE YourTable (Code int, Quantity int, EffectiveDate date);
GO

INSERT INTO YourTable
VALUES(1,1,'20180501'),
      (1,1,'20180516'),
      (1,1,'20180521'),
      (2,1,'20180501'),
      (2,1,'20180521'),
      (3,1,'20180501');
GO

For the first query, the result would be:

Code        Quantity    EffectiveDate
----------- ----------- -------------
3           1           2018-05-01

For the second, it would be:

Code        Quantity    EffectiveDate
----------- ----------- -------------
1           1           2018-05-16
2           1           2018-05-01
3           1           2018-05-01

score 0 · Answer 3 · edited May 16 '18 at 14:20

0

Use a WHERE clause with GETDATE() function

where data < getdate()

edited May 16 '18 at 14:20

Thom A

88,727
11
45
75

answered May 16 '18 at 14:15

Rodrigo Benincasa

34
4

Way to find the maximum dates with no futuristic dates

3 Answers3