List months between two dates 1

[liltechy]

Here is the problem: AuthStDate is a date field. AuthEDate is a date field. I need to create in a query another field that will list out the individual dates between the two dates. For example: AuthStDate = 6/25/2003 and AuthEndDate = 9/30/2004, I need to see 6/26/03, 6/27/03 and so on. So how do I show individual dates(mm/dd/yyyy)? The reason I think I need this data is because I need to create a report that will show the unused amount of dollars per month for a given time frame. For instance if my AuthStDate is 6/25/03 and my AuthEndDate is 9/30/04 and the auditor wants to see the unused dollars for 1/01/04 - 8/30/04 I don't have those dates in my table. PLEASE HELP!!!

liltechy


liltechy

 

[SkipVought]

Hi,

One method that I have used in circumstances such as this (for instance, to create a crosstab report with some columns that may not be included from data in in the table) is to create a complete set of data, I call it a dummy set, that acts essentially as place holders.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[liltechy]

I'm not quite sure I understand. How will a crosstab give me the dates between to two dates?

liltechy

 

[SkipVought]

A crosstab does NOT. I have used the dummy record WITH a crosstab to list items in the crosstab columns that were NOT present in the table, but were in the DUMMY record
[tt]
Item Value
A 5
C 6
A
B
C
[/tt]
Crosstab
[tt]
A B C
5 6
[/tt]
Dummy records in bold. Item B has no value, but appears in the crosstab.

A B C, .... are your dates, what, one for each month?

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[JonFer]

One option is to create a table with one integer field in it called "SeqNum". The table can be called "Sequence". Add rows with the numbers 0 to 30. Then add the table twice to a query with your other table. Alias one instance of the Sequence table as s1 and the other instance as s2.

Your SQL would be like this:

Select s1.SeqNum*(Select Count(*) from Sequence)+s2.SeqNum+StartDate as NewDate,
       StartDate,
       EndDate-StartDate as Days,
       DollarAmt/(EndDate-StartDate+1) as AllocatedAmt
From YourTable, Sequence as s1, Sequence as s2
Where  (s1.SeqNum*(Select Count(*) from Sequence)+s2.SeqNum) <= EndDate-StartDate

This will calculate up to 960 (30*31+30) days difference and can be expanded by adding rows to Sequence.

 

[liltechy]

Jonfer
Where do I put the SQL?

liltechy

 

[liltechy]

Jonfer,
I figured it out and it works wonderfully. Thanks a bunch.
liltechy

 

[liltechy]

JonFer,
I have one more question, when it gives me the newdate it starts with 30 days from the AuthStDate and then it give the individual dates. How can I get it to give me all the individual dates starting from the date after the AuthStDate?


liltechy

 

[liltechy]

JonFer,
I forgot to put 0 in my Sequence table. Please disregard the previous question.

liltechy