Sorry for asking a noob question but why should I change the code if it is working for me? Is Jeff's more resource-efficient?
Also maybe my Microsoft Report Builder is buggy but it can't do assigning with =. You have to use AS instead. So in the instance you mentioned above, I will need to rewrite it as.
, ((iif(month(pr_time_attendance.h_date) < 10, 1, 0) * 12) + month(pr_time_attendance.h_date) - 10 + 1) as Fiscal Month
I think it also did not recognize datefromparts when i tried it :(.
Just to be sure...
Jeffrey Williams is correct... my code would suck for performance in a WHERE clause because it would not allow for a SARGable query. If you want to use it for criteria, it would best be used as I previously indicated... as a formula to power a persisted, indexed, computed column (takes the place of a Calendar table JOIN). His code is MUCH better if you want to use formulas in a WHERE clause.
The reason for change should not be singularly based on whether or not something actually works. Of course, that's the minimum requirement but it's also important that you use a method that's scalable so the neither current nor future performance will suffer.
The advantage of doing it as a persisted computed column is that you'd only need to change things in the table if the fiscal year changed. There are other considerations, as well... for example... is everyone else using a Calendar table? It's probably best if you followed suit if they are or have a really good reason not to.
So, the bottom line is "Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty". 😉
is pronounced "ree-bar
" and is a "Modenism
" for R
ow.First step towards the paradigm shift of writing Set Based code:________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
Helpful Links:How to post code problemsHow to Post Performance ProblemsCreate a Tally Function (fnTally)