cancel
Showing results for 
Search instead for 
Did you mean: 

Join the community at Nodes 2022, our free virtual event on November 16 - 17.

Calculate Min Date

pragyasood28
Node Clone

Hi Everyone,

Again I am stuck at a step, here I want to find the name of the nodes where the date is the least with a particular condition:

this has multiple capacity nodes and I need to find out the least date nodes where Available capacity < Required capacity

I have written this query but this is returning all the nodes with their dates

match (b:Capacity)
where b.Available_Capacity<b.Required_Capacity 
with b,min(b.Date) as date
return b.name order by date ;

Another method using apoc is returning me the nodes in the graph format but I just want the name property of this node:

match (b:Capacity) 
where b.Available_Capacity<b.Required_Capacity 
WITH apoc.agg.minItems(b, b.Date) AS minItems
return  minItems

In this graph the output should be nodes A and B, assuming the where condition is satisfied.

Can someone pls suggest what is the best way to return name property of these nodes?

Thank You

1 ACCEPTED SOLUTION

You almost have it with this query, all you need to do is project, from the minItems structure, the elements of it you want. You could use RETURN [node IN minItems.items | node.name] as names, minItems.value as minDate to get the list of names associated with nodes that had the given minimum data value.

View solution in original post

5 REPLIES 5

ameyasoft
Graph Maven
Try this:
match (b:Capacity)
where b.Available_Capacity<b.Required_Capacity 
with b.name as name, b.Date as date
return name order by date 

Hi ameya,

I tried this but it is returning all the node names with dates in ascending order. But I want that the nodes with the least date should be returned. That is node A with date 08-2021 and node B with date 08-2021.

Hence, I just want the name of the nodes where the date is minimum. In this example there are 2 such nodes but in my graph DB there can be any number of nodes where date is minimum.

Thanks,
Pragya

Try this:

match (b:Capacity)
where b.Available_Capacity<b.Required_Capacity 
with min(b.date) as dt
match (a:Capacity) 
where a.Available_Capacity<a.Required_Capacity
where a.date = dt
return a.name

Thanks this worked

You almost have it with this query, all you need to do is project, from the minItems structure, the elements of it you want. You could use RETURN [node IN minItems.items | node.name] as names, minItems.value as minDate to get the list of names associated with nodes that had the given minimum data value.