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

[elannesse]

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]

Create a table with consecutive numbers from 0 to ... and use it like this.

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

 

[elannesse]

@ebs17, you are a genius!! And very generous, thank you so much

 

[June7]

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]

June, yeah, we actually need the listing of all annual dates, it is tedious but needed. But thank you

 

[arnelgp]

without using Additional table:

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]

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.]

Note:
@June7: The cartesian product in #2 corresponds to a left join.

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:

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]

that Cartesian product query

This term is flippant and imprecise.

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]

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]

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]

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]

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]

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]

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!

 

[ebs17]

It is interesting to compare different approaches

Are there any other points of interest in a comparison other than “it works”?

 

[arnelgp]

translate this in german, Egocentric.