How to use parameter in set function and calculated field in where
-
Posted to wrong thread - removed
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - fahad.kazi wrote:
Hi Jeffrey,
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". 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeffrey,
The data type is date time because when I use query wizard to put filter and choose a date - the SQL code says:
WHERE
pr_time_attendance.h_date >= N'2020-05-10T00:00:00' -
Thank you for the explanation. This makes sense in terms of resource-efficiency. I will convert :).
-
It appears you don't have access to the database system - only SSRS. Not sure the tool recognizes the difference between date and datetime so that isn't really a way to determine the data type. When you include that column in a report - does it have the time or is it just the date?
Is there any way you can identify the version of SQL you are running against? The datefromparts should work 2012 and greater - and if we really need a datetime we should be able to use datetimefromparts. But if the version of SQL is not 2012 or greater then a different method for calculating the fiscal start date would be needed.
In Report Builder we could probably create a parameter that is calculated from the fiscal year parameter. So you would have 3 parameters - the first one is the fiscal year as outlined previously. The second would be fiscal start - using the code =DateSerial(Reports!FiscalYear.Value - 1, 7, 1) and fiscal end using the code =DateSerial(Reports!FiscalYear.Value, 6, 30).
Then modify your where clause to:
Where [date field] >= Reports!FiscalStart.Value And [date field] < dateadd(day, 1, Reports!FiscalEnd.Value)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - fahad.kazi wrote:
Hi Jeffrey,
The data type is date time because when I use query wizard to put filter and choose a date - the SQL code says:
WHERE
pr_time_attendance.h_date >= N'2020-05-10T00:00:00'I'm thinking that's no assurance at all. For example, that would also work in SMALLDATETIME, DATE, DATETIME2 . The only way to know the datatype of a column for sure is to either have an error tell you what it is or look. I prefer the latter. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, it does show with time - a bunch of 00:00:00s in the unformatted field lol.
-
Thanks, Jeff. I will try that. As for the version, I was convinced it was SSRS 2016 or newer but I am not so sure now. This is the only thing I could find regarding its version.
-
That is the version of SSRS - which may or may not be the same as the source system where you are getting the data. The dataset is configured with a connection string to a database - and it is that system the determines what SQL can be used. If you have SSMS and can connect directly to the SQL Server instance - you could then run Select @@version.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply