Create a series of dates out of Start & End values

[Tskutnik]

All, happy thanksgiving and thanks in advance.
Attached is a data for this question. The requirement is to project monthly income for a user-selected From/To date.
In the attached Database there are 3 tables:
Assignments: This is the source data I have to work with. Fields descriptions in the table will tell what each is used for. The query result I need will turn those dates into a date series, with the InvoiceRate value listed for every month end between the Assignment’s start and end dates. The user will pick a From and To date range to run the query for and any Assignments with Start and End dates within the From/To range will be listed. E.g. if From = 3/31 and To = 6/30 only Assignments 1 & 3 will have results, since Assignment #2 is only in January.
SampleQueryResult_Option1: Is an example of how the query output could look. This one assumes the query is smart enough to only list the dates between Start and End. In the example From = 1/31/15, To = 12/31/15.
SampleQueryResult_Option2: Is a second option of how the query output could look. In the example From = 1/31/15, To = 12/31/15 and therefore some of the periods will show InvoiceRate = 0 since they are out of the Start and End date range.
I’ve tried a few things and cannot get this to work. Thanks again for your help.

 

[MarkK]

One object in one row in a database represents one discrete thing. The fields in that row are the dimensions of that object. The error you have made is that you have implicitly storing two time dimensions in a single row, and they are in conflict with each other. In this row, for instance...

ID	StartDate	EndDate		Rate	
1	1/1/2015	12/31/2015	$1,800.00

...the explicit time dimension of the dates is one year, but the implicit time dimension of the rate is one month. This is like having someone's birth date and their company name in the same row. Impossible. There no object that has both of those dimensions.

Your problem is more subtle, but essentially the same, there is no object that is one year long that has a monthly rate. This error in how the data is represented gives you your problem calculating your desired result.

 

[stopher]

See attached to get you started. Essentially create a table of all the month ends then do a Cartesian product query which will produce all possible rows and filter only on rows where monthend is between the two given dates. See qryOutput.

By a similar principle I can't see why you can't generate the zero values also.

As to sequencing this is a slightly separate problem but has been answered in the forums before so you should be able to solve this too.

 

[Tskutnik]

stopher - I tried that method and could not get the query to work. I cross referenced the Assignments.YYYY_MM_StartDate and YYYY_MM_EndDate fields with a table of Month ends, using the same YYYY_MM format and no luck. Do you have any recommendations on how to get the results to come out as in the [SampleQuery.... ] examples?

And you are correct, extra zeros (as in the [Sample...Option2] are fine.

 

[Tskutnik]

I got it. Simple iif statement. Thanks everyone for the help