lundi 3 août 2015

SQL Server 2005 next lowest date greater than today

I’m looking to find the next lowest date, greater than today in a select statement. I’ve simplified the data down as an example.

Example data: table_1

name    order_no    order_date  Due_date    Run_no
customer1   abc1    01/04/2015  02/05/2015  1
customer2   def2    02/04/2015  02/05/2015  2
customer3   ghi1    03/04/2015  02/05/2015  3
customer2   def3    04/04/2015  04/05/2015  2
customer2   def4    05/04/2015  05/05/2015  2

and example query:

select 
    name,
    order_no,
    order_date,
    Due_date,
    Run_no
from 
    table_1
where 
    run_no = '2'
group by 
    name, order_no, Order_date, Due_date, Run_no
having 
    MIN(due_date) > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

so if today was 01/05/2015 i would expect the result to be:

name    order_no    order_date  Due_date    Run_no
customer2   def2    02/04/2015  02/05/2015  2

but what i actually get is:

name    order_no    order_date  Due_date    Run_no
customer2   def2    02/04/2015  02/05/2015  2
customer2   def3    04/04/2015  04/05/2015  2
customer2   def4    05/04/2015  05/05/2015  2

This has to be something to do with the Having clause doesn't it? Thanks

Aucun commentaire:

Enregistrer un commentaire