Calculate Min Date

[pragyasood28]

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

[ameyasoft]

pragyasood28:

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

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 

[pragyasood28]

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

[ameyasoft]

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

[pragyasood28]

Thanks this worked

[andrew_bowman · Accepted solution]

pragyasood28:

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

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.