Solved Query to provide annual dates in an interval (1 Viewer)

elannesse

New member
Local time
Today, 13:26
Joined
Jul 2, 2019
Messages
8
Major brain fog here and hope someone can help. I need to generate "annual report due dates" between two dates. Reports start 1 year after RECApprovalDate and continue yearly until ProjectEndDate. I have the below and it is currently producing a single date 1 year after the REC date per project. I need to be able to generate multiple reports until the end of the project but can't think how to refer back to previous report date to generate the next one due. Any ideas? Is it even possible in a query?


SELECT tblProjects.ProjectID, tblProjects.RECApproveDate, tblProjects.ProjectEndDate, DateAdd("yyyy", 1, tblProjects.RECApproveDate) AS AnnualReportDate
FROM tblProjects
WHERE DateAdd("yyyy", 1, tblProjects.RECApproveDate) <= tblProjects.ProjectEndDate
ORDER BY tblProjects.ProjectID;

Many thanks in advance.
 

ebs17

Well-known member
Local time
Today, 22:26
Joined
Feb 7, 2020
Messages
1,991
Create a table with consecutive numbers from 0 to ... and use it like this.
SQL:
SELECT
   P.ProjectID,
   P.RECApproveDate,
   P.ProjectEndDate,
   DateAdd("yyyy", C.Number, P.RECApproveDate) AS AnnualReportDate
FROM
   tblProjects AS P,
   tblCounter AS C
WHERE
   C.Number BETWEEN 1 AND DateDiff("yyyy", P.RECApproveDate, P.ProjectEndDate)
ORDER BY
   P.ProjectID
 
Last edited:

elannesse

New member
Local time
Today, 13:26
Joined
Jul 2, 2019
Messages
8
@ebs17, you are a genius!! And very generous, thank you so much :)
 

June7

AWF VIP
Local time
Today, 12:26
Joined
Mar 9, 2014
Messages
5,497
Be aware that Cartesian product query can be very slow with large dataset.
Do you really want a listing of all annual dates between start and end of project or do you just want to filter records?

If 3 years have passed since start of project then add that to RECApproveDate. Use DateDiff() to calculate elapsed period between project start and current date in place of the 1 in DateAdd expression.

WHERE DateAdd("yyyy", DateDiff("yyyy", RECApproveDate, Date()), RECApproveDate) <= ProjectEndDate
 

elannesse

New member
Local time
Today, 13:26
Joined
Jul 2, 2019
Messages
8
June, yeah, we actually need the listing of all annual dates, it is tedious but needed. But thank you :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:26
Joined
May 7, 2009
Messages
19,246
without using Additional table:
Code:
SELECT P.ProjectID, P.RECApproveDate, P.ProjectEndDate, DateAdd("yyyy",CNT,P.RECApproveDate) AS AnnualReportDate
FROM tblProjects AS P,
(SELECT MsysObjects.Id, (SELECT COUNT("1") FROM MSYSOBJECTS AS T1 WHERE T1.ID <= MSYSOBJECTS.ID)-1 AS CNT
FROM MsysObjects GROUP BY MsysObjects.Id) As T1
WHERE (DateAdd("yyyy",CNT,[P].[RECApproveDate]))<=[P].[ProjectEndDate]
 

ebs17

Well-known member
Local time
Today, 22:26
Joined
Feb 7, 2020
Messages
1,991
Is it an advantage to avoid a one-off table with the required numbers? In good style, this number field would also be indexed and thus promote the best possible performance.

The derivation of the required numbers via calculation in a correlative subquery requires considerable effort, which increases and possibly multiplies the effort of the actual query.
 

Josef P.

Well-known member
Local time
Today, 22:26
Joined
Feb 2, 2023
Messages
848
Note:
@June7: The cartesian product in #2 corresponds to a left join.

Code:
FROM
   tblProjects AS P,
   tblCounter AS C
WHERE
   C.Number BETWEEN 1 AND DateDiff("yyyy", P.RECApproveDate, P.ProjectEndDate)

The Jet showplan should be the same as:
Code:
FROM
   tblProjects AS P
   left join
   tblCounter AS C ON  C.Number BETWEEN 1 AND DateDiff("yyyy", P.RECApproveDate, P.ProjectEndDate)
(This join expression cannot be displayed in the query editor - only editable in SQL view)

Since projects rarely run for several hundred years, the amount of data processed should remain manageable. ;)
 

ebs17

Well-known member
Local time
Today, 22:26
Joined
Feb 7, 2020
Messages
1,991
that Cartesian product query
This term is flippant and imprecise.
SQL:
SELECT A.FieldX, B.FieldY
FROM A INNER JOIN B
ON A.ID = B.ID
'--------------------------------
SELECT A.FieldX, B.FieldY
FROM A, B
WHERE A.ID = B.ID
Functionally and in terms of process, exactly the same thing happens here, the result is also the same, and the performance is practically the same if you do the same thing (comparisons, indexes).

In order to find key pairings A.ID = B.ID, in both variants and other conceivable variants, every record in table A must be combined and compared with every record in table B. Seen in this way, every comparison of tables against each other is, in the very first step, a CROSS JOIN.

The expression DateDiff("yyyy", P.RECApproveDate, P.ProjectEndDate) in the criterion is more likely to influence performance here. This must be calculated per record in tblProjects (instead of simply taken from the table), and the calculated expression does not provide index usage.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:26
Joined
May 7, 2009
Messages
19,246
The derivation of the required numbers via calculation in a correlative subquery requires considerable effort, which increases and possibly multiplies the effort of the actual query.
what effort? how many millions of year does a project ends?
the most 10 records will be returned (for 10 years project period, but doubt it would take that long).
 

ebs17

Well-known member
Local time
Today, 22:26
Joined
Feb 7, 2020
Messages
1,991
Pointing out the admittedly low number of years and excluding large numbers is nothing other than admitting a volume-dependent problem with effort.

In the project constellations that are likely to be expected, this will not be obviously noticeable, but the potential problem does exist. Without necessity.

You might also call it a question of style: If a data set changes from 5 to 500,000 (which is not unthinkable in the database area), I don't want to be in the position of having to reprogram in order to consciously (?) eliminate built-in weaknesses.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:26
Joined
May 7, 2009
Messages
19,246
500,000 (which is not unthinkable in the database area)
no it is unthinkable to have the OP that much years.
you are tooo arrogant!
always pointing your offered solution is the Absolute one.
 

ebs17

Well-known member
Local time
Today, 22:26
Joined
Feb 7, 2020
Messages
1,991
always pointing your offered solution is the Absolute one
Does that annoy you?

And no, I am not claiming at any point that any of my suggestions are the absolute best solution. Never.
I give arguments why I do something myself in a certain way and therefore suggest it to others. Everyone can compare these arguments with their own arguments. Free choice.

It also applies to the number 3: I see no sensible reason to recalculate this number every time I can just take it.
One thing should be undisputed: queries love tables and can handle them well, better than subqueries.

without using Additional table
You can also think of an existing table with autonum values. But that would be an unsafe business because deletions can occur. Not practical.

In my thoughts and uses, a number table as mentioned as well as other helpful tables (calendar table) often appear. A one-off and well-planned creation of such a table does not require much effort, but the resulting benefits are extremely great.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:26
Joined
May 7, 2009
Messages
19,246
Does that annoy you?
i gave a suggestion to the OP and expect to hear from the OP, yet
here you are commenting on another volunteer contributor.
And no, I am not claiming at any point that any of my suggestions are the absolute best solution. Never.
you don't need to claim, since it goes out naturally without you knowing.
 

elannesse

New member
Local time
Today, 13:26
Joined
Jul 2, 2019
Messages
8
Peeps, please don't give each other stress, we are all here to help each other - or in my case, get help from you lovely contributors. Myself and others learn so much from all of you, seriously, I am awed by the expertise. It is interesting to compare different approaches for us relative newbies. I would have never thought of the solution ebs17 and it works for my needs for now. I don't need a big table - longest project period I found in my data dump is 12 years, so it may be an overkill but works for the moment. I haven't had a chance to try out your suggestion arnelgp, will try this evening when I get in from work (again another way of formulating a query that I never would have figured out, look forrward to taking it apart and see what it's doing). So, thanks to all of you. Peace!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:26
Joined
May 7, 2009
Messages
19,246
translate this in german, Egocentric.
 

Users who are viewing this thread

Top Bottom