Could you provide a format?
My solution involves changing the format, using “CAST”.
Field is a t-sql date .
Something like
rank() over (order by business_date)
Ok, how about an example of the date?
08-16-2022
If that can be defined as a datetime field, then a function can be used to create the field “date minus one day.”
Try DATEADD if the SQL Is MS-SQL. Not sure Oracle allows it, as I had to convert my DATEADD functions, oh, a decade ago.
That’s not going to work if you’re using a Monday and trying to find the previous Friday.
Oh, is THAT what he means?
Subtract three if a Monday (yes, SQL should know what day of the week each date is), else subtract one.
DAYOFWEEK function. Check to see what number corresponds to Monday on your SQL. Could be 0, could be 1, could be 2!
I’d use “CASE WHEN” for that/
I assume that he also wants to skip holidays.
Like for 9/6/2022 (Tuesday) your formula would return 9/5/2022. But that’s not a business day; it’s Labor Day. He’d need it to know that 9/5/2022 is not the right answer and return 9/2/2022 instead.
Weekdays are easy. Business days are harder.
I’m thinking you won’t want a table with every single business day & prior day listed as that would get very large very fast.
Maybe just make a table that lists the weekday holidays and what their prior business day is.
Run through DTNF’s logic first, then check to see which results are on the list of weekday holidays. If they are, return the second column of the list. If they aren’t, then keep the answer from the DTNF code.
Update the list once a year with the next batch of holidays. Again… weekday holidays on the list. If July 4 is a Saturday then Friday July 3 is the holiday and you need the output for Monday, July 6 to be Thursday, July 2.
So DTNF’s logic will return Friday, July 3 then July 3 is on your list to be replaced with July 2.
But the next year most likely July 4 will be on a Sunday. So that year you want Monday July 5 to be the weekday holiday on the list, with Friday, July 2 as the replacement.
Maintaining the list properly will be key.
You might have to hardcode the work holidays. It should be easy enough to find a calendar table online with an extra column for days of the week. You could then write a select statement from that table that brings back a column for the date and a rank for the date rank, with conditions where weekday not in (‘Saturday’,‘Sunday’) and date not in your hardcoded dates for holidays.
On behalf on whomever inherits this code, pretty please don’t do this… ever.
You don’t need to tell SQL days of the week. It knows which are the correct days of the week. Use the built-in functionality that does this for you. (See DTNF’s post on this.)
LOL, this is MS SQL Server, not Access. 251 business days a year (give or take) and even a hundred years isn’t “large”. Source data is about 40 million records for comparison. Given that my source contains all the dates I care about, that is my source. RANK() might work. I’ll give it a try tomorrow and let y’all know. Thanks for the suggestions.
It’ll run faster with a smaller table. Plus it’ll be a lot easier to maintain.
You still have to write the SQL to tell the computer what to do. If you have a function that does what I suggested (which saves at best, a few lines of code), great. You still have to enter the function into your code. I suggested several steps to do it, you came up with a shortcut for one of the steps - good for you.
A multiyear date table takes up a miniscule amount of space, whether it’s one column or several columns, so space is not an issue. You may want to reuse the table for all days, so having columns for date, dayofweek (using your function to build it) and even holiday indicator may come in handy - I’m not sure where your “don’t do this ever” overreaction is coming from. Building a rank on the fly (after you’ve selected the necessary dates from the table) is also very quick.
We did this for years prior to the function being introduced into T-SQL and it worked well. We used that table for all sorts of queries. Forgive me for not being up to date with all the latest functions - haven’t needed to write a business day query in years. Heaven forbid if you ever suggest a technique (one of many in this case, I might add) that doesn’t use all the latest functions.
I wonder if you wrote the code I was hired to fix a few years ago. I got the runtime on that code from about 400 minutes down to about 65 minutes and cleaned up some errors besides.
Please use the DAYOFWEEK function if you need to know whether a date is Monday, Tuesday, etc.
Or, you know, don’t. I’m not looking for new clients at the moment, but maybe in the future I will be!
I’m not sure about DAYOFWEEK, but the slightly clunkier DATENAME function has been around at least 20 years, likely longer, and still beats the pants off maintaining a table and doing an unnecessary lookup.
My preference would be to use simpler logic (single join vs formula + join to smaller table), but runtime will out. If it were a process that was used frequently, runtime would be more of a consideration, but this is normally a semi-annual process, or quarterly at most, so runtime isn’t the highest priority.
This is job 2 today, but is deferable, as it is improvement vs a production need. Hopefully I will be able to share results. FWIW, the 40 million records will undergo some consolidation (somewhere between 5x1 and 20x1, exact value depends on filter) and filtering before needing to be matched across dates.
The other thing I just thought of is I can add a field to the record and put prior date on the record, essentially doing the match to curr/prior table one time instead of on the fly in the future (at the cost of storage). That’s not normalized, but it is a choice. I also have control of the data that will be added in the future (and can readd existing data), so it could be part of the load process.
I think you’re missing a word in the last part.
If you’re working with a small amount of data that you’re confident will stay small and you don’t care about the runtime then fine.
I wouldn’t put a lot of stock in “only run twice a year”. Twice a year processes have a funny way of becoming quarterly processes, which in turn have a funny way of becoming monthly processes, which in turn have a funny way of becoming “run this process 4 times with 4 slightly different data sets” or “run this process with incomplete data 1 week and then again 1 business day before month end (before you run it for real after month end) so that we can get an idea of what the numbers will look like”.
I have observed that current month-end processes are now more cumbersome than quarter-end processes when I began my career.