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