Show Next/Last date in query results 1

[hlkelly]

Hi.

I have two related tables:

Project
Project Items

I want to query and show the "next" project item related to a project. The query should show the result of all projects and their next project item date.

Each project is structured so that it has many project items with various due dates.

Ultimately, I need one query to show the "next" date and another query to show the "last" date. Two total queries is my goal.

I have tried Min/Max...but I am not getting the results I want. The query is only showing the Min of the entire project item set and I want to see each project's "next" project item date. NOT the overall "next" date in the entire project set.

Make sense?
Thanks

Heather

 

[PHV]

Why not posting some input samples, your actual SQL code with actual result vs expected output ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

I'm a bit fuzzy on what you mean by "next".

I infer that it has something to do with the dates. Is it
- The "next" one after the current date?
- The "next" one after some record that you are viewing?

Similarly, does "last" mean the project item with the greatest date?

Assuming that "next" means the first one after today ...

Select ProjectCode, 
       MIN(DueDate) As [NextDate],
       MAX(DueDate) As [LastDate]

FROM [Project Items]

WHERE DueDate > Date()

GROUP BY ProjectCode

You may need to elaborate a bit on this in case there are projects that don't have a "next" date (i.e. all the due dates are before today.)

 

[hlkelly]

*Update*

This query works and will show the MAX date (Last date of all related dates).

SELECT [Active project list].JOBID, Max([Project items].Itemduedate) AS MaxOfItemduedate
FROM [Active project list] LEFT JOIN [Project items] ON [Active project list].JOBID = [Project items].JOBID
GROUP BY [Active project list].JOBID, [Project items].Billed
HAVING ((([Project items].Billed)=No));

And this query will show the MIN date (next date after the date I run the query):

SELECT [Active project list].JOBID, Min([Project items].Itemduedate) AS MinOfItemduedate
FROM [Active project list] LEFT JOIN [Project items] ON [Active project list].JOBID = [Project items].JOBID
GROUP BY [Active project list].JOBID, [Project items].Billed
HAVING ((([Project items].Billed)=No));


When I try to bring the results of the two queries together related to each project by JOBID, I get this error:

"The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data."

I finally got the queries to work but can't bring the two dates together into one place. Need to be able to see the next date (after today) and the last date (in the set of dates related to the project).



Heather

 

[Golom]

Hmmmm ...

I don't see how the second query gives you the next date after the date you run the query since there is no reference to the current date in that query. Looks like it will give you the first date for the project regardless of when (i.e. on which date) you run it.

Also I don't see why you are including [Project items].Billed in the GROUP BY since you are constraining it to be a specific value.

I would expect the error that you are seeing to result from UPDATE or APPEND queries but not from a SELECT query as these are. Can you show the SQL that is failing rather than just the ones that are working?

 

[hlkelly]

I only want to look at items that have not been invoiced yet. The purpose is to show the next billing date and last billing date in a set of unbilled items related to the project. Maybe that helps?

I see what you are saying regarding the "next" date. I guess it doesn't really need to be "next" necessarily. I suppose my terminology is getting me messed up. I want the MIN date and MAX date and these queries get that info for me.

The error is throwing me off too. I don't think I have ever seen it before.

 

[Golom]

OK ... then this should give you both dates in one query.

SELECT A.JOBID, 
       Min(P.Itemduedate) AS MinOfItemduedate,
       Max(P.Itemduedate) AS MaxOfItemduedate
FROM [Active project list] As A LEFT JOIN [Project items] As P ON A.JOBID = P.JOBID
WHERE P.Billed=No
GROUP BY A.JOBID, P.Billed

or ... if you want the next one after today

SELECT A.JOBID, 
       Min(P.Itemduedate) AS MinOfItemduedate,
       Max(P.Itemduedate) AS MaxOfItemduedate
FROM [Active project list] As A LEFT JOIN [Project items] As P ON A.JOBID = P.JOBID
WHERE P.Billed=No AND P.Itemduedate > Date()
GROUP BY A.JOBID, P.Billed

I changed HAVING to WHERE because you are constraining an individual field value and not an aggregate function.

Because of the LEFT JOIN, you may get records with NULLs in the MIN and MAX fields.

 

[hlkelly]

That worked! Thank you.

 

[PHV]

Because of the LEFT JOIN, you may get records with NULLs in the MIN and MAX fields
I don't think so, due the WHERE clause ...