DCrake
Remembered
- Local time
- Today, 08:08
- Joined
- Jun 8, 2005
- Messages
- 8,632
Having had a conversation with a highly regarded contact we came up with a concept that would/should cut out all queries surrounding dates in tables.
Months missing from crosstab queries
Conversion between DMY and MDY
Week ends or week days only
Bank/public holidays
etc
What we came up with was a table (which I have had to chop the bottom half off, so it would be small enough to post.) This table consists of a wide variety of dimensions sourrounding a given date,
Its...
Julien number
date in DMY format
date in MDY format
Day of the week
is it a weekday/weekend
is it a known bank holiday
week day
short/long day
short/long month
short/long year
and many more.
The original table runs from 13/12/1899 to 31/12/2020
the pk is an autonumber which coincidently is the julien day number.
So how does it work?
Lets say you want to run a complicated crosstab report for any given year but only for weekdays and you want to exclude any holiday's that appear in that period.
First you introduce the table to your query and depending on whether your dates are formated dmy or mdy link the actual date in the table to the matching date in the table to be interrogated. If you put a right join on it so all dates from the master dates table appear even if they don't appear in the source table. You then drag down the field FKWeekday and set the where condition to 1 (Yes) drag down the FKBankHoliday Field and set the where condtion to 0 (No).
Finally you apply the date range to the FKDMYDate, FKMDYDate or PKDate field. I suggested the PKDate field because if you pass the parameters as numbers as opposed to actual dates it does not matter what format the date is in.
Make up you row and column headings to suit you needs but if you are using weeks or months for example you need to use the date in the master table as every date will be there. So no missing columns are encountered.
So now we run the query.
buy using this table and applying the relevant condtions there is no need to test for weekends/weekdays/holidays/etc because all that information is already known in the master dates table.
I have not put this to the test yet as I have only created it today. There are fields for financial years elements but have left these blank as you may have a different day one than me. As a developer you could populate them yourselves with the corrsponding data entities.
I will leave this thread open for any comments you may have.
David
Months missing from crosstab queries
Conversion between DMY and MDY
Week ends or week days only
Bank/public holidays
etc
What we came up with was a table (which I have had to chop the bottom half off, so it would be small enough to post.) This table consists of a wide variety of dimensions sourrounding a given date,
Its...
Julien number
date in DMY format
date in MDY format
Day of the week
is it a weekday/weekend
is it a known bank holiday
week day
short/long day
short/long month
short/long year
and many more.
The original table runs from 13/12/1899 to 31/12/2020
the pk is an autonumber which coincidently is the julien day number.
So how does it work?
Lets say you want to run a complicated crosstab report for any given year but only for weekdays and you want to exclude any holiday's that appear in that period.
First you introduce the table to your query and depending on whether your dates are formated dmy or mdy link the actual date in the table to the matching date in the table to be interrogated. If you put a right join on it so all dates from the master dates table appear even if they don't appear in the source table. You then drag down the field FKWeekday and set the where condition to 1 (Yes) drag down the FKBankHoliday Field and set the where condtion to 0 (No).
Finally you apply the date range to the FKDMYDate, FKMDYDate or PKDate field. I suggested the PKDate field because if you pass the parameters as numbers as opposed to actual dates it does not matter what format the date is in.
Make up you row and column headings to suit you needs but if you are using weeks or months for example you need to use the date in the master table as every date will be there. So no missing columns are encountered.
So now we run the query.
buy using this table and applying the relevant condtions there is no need to test for weekends/weekdays/holidays/etc because all that information is already known in the master dates table.
I have not put this to the test yet as I have only created it today. There are fields for financial years elements but have left these blank as you may have a different day one than me. As a developer you could populate them yourselves with the corrsponding data entities.
I will leave this thread open for any comments you may have.
David
Last edited:
