display every date in a date range

kobiashi · Oct 28, 2018

hi all

im trying to display all days in a range, i have a chart that shows a running 30 day window, what i want to do is show all days, in that 30 day window, whether an event occurs on a day or not, i have read that the only way to do this is have a table with all dates in the table, is there another way to do this?

pbaldy · Oct 29, 2018

Not that I know of. I append the required dates into a table used for that purpose before proceeding.

June7 · Oct 29, 2018

Consider following found code, no guarantee can make practical use of:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate]-1+100*Abs([Hundreds].[id] Mod 10)+10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1,"mm/dd/yyyy") AS Dt, 1 AS Data
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE ((([StartDate]-1+100*Abs([Hundreds].[id] Mod 10)+10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1) Between [StartDate]-1 And [EndDate]));

CJ_London · Oct 29, 2018

@June - that's neat, new one for me - by the looks of it, the limit is 999 days which should enough for most people, or just add a 'thousands' table to take it up to 9999. Risk is that one of the numbers is missing, but unlikely.

Subject to that risk you can make it faster by only including ID's<0

kobiashi · Oct 29, 2018

hey all thanks for the help, i decided to use a table in the end, as there was only a few thousand entries for 15 years.

MajP · Oct 29, 2018

In your case you could make a table with just 30 entries. When the user selects the start date before opening the chart, just run a loop updating the table with the needed 30 days. Since you are just updating and not continuously adding/deleting it should not bloat. If it is always the next 30 days out then you can update it on start up.

theDBguy · Oct 30, 2018

Hi,

If I may offer another potential approach using a Cartesian query. I have an example of this on my blog site, which basically takes a tally table that MajP mentioned above and then produce every dates within a date range using a cartesian query.

Just my 2 cents...

CJ_London · Oct 30, 2018

display every date in a date range

kobiashi

Registered User.

pbaldy

Wino Moderator

June7

AWF VIP

CJ_London

Super Moderator

kobiashi

Registered User.

MajP

You've got your good things, and you've got mine.

theDBguy

I’m here to help

CJ_London

Super Moderator

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread