Recently I was involved in a project which required rewriting few logic from T-SQL using DAX functions for migrating values to a Power pivot based model. The DAX function set being not as comprehensive as T-SQL required me to simulate…
T-SQL to DAX: Simulating RANK function
RANK() function helps us to find out rank of a row based on particular sequence within a sql table or data group within it. DAX doesn’t have a corresponding function available within it to get the RANK. This blog post…
T-SQL Tips: Beginning of Month Calculation
One of the most frequently used date manipulations in SQL Server is for calculating the beginning and end dates of a month. This is particularly useful in date range filters and monthly aggregation calculations. SQL 2012 has brought in a new…
Get fiscal year dates corresponding to a date value
Quite often we need aggregations to be done based on fiscal years rather than on financial years. My preferred method always has been on storing fiscal year values also along with other fields in my calendar table which can be…
Get the latest weekday of month in SQL 2012
Recently there was a requirement to get latest weekday of a month.I made use of a solution based on the EOMONTH function available in SQL 2012 for that, which I’m sharing through this blog.The UDF will look like below CREATE…
Enforcing effective data validation checks in T-SQL
I’ve seen on quite a few occasions of people asking ways to validate the source data especially in integration and ETL type of projects. The usual suggestion in those cases is to leverage upon the set of standard validation functions…
Get the Nth positioned string from a delimited list
Today had a colleague asking me on a method to return particular positioned string from a delimited list. The scenario was like belowConsider a string like abcd-efgh-jkl-mnop-qrst-uvwx. The requirement was to get 3rd string from it ie ijkl. The usual…
Checking database access rights for a user in a sqlserver instance
There was a recent requirement in one of my projects to check the access for particular user in all the databases in a server. Thought of sharing the script used through the blog for quick reference. The solution makes use of…
Programaticaly executing packages in SSIS 2012 project deployment model
The usual way to execute SSIS packages programmatically was by invoking Dtexec utility until SQL 2012. With the arrival of project deployment model in SSIS 2012 we can now rely upon catalog view and system stored procedures for executing SSIS…
Demystifying LAST_VALUE function in SQL 2012
The impetus for writing this blog is the ambiguity existing regarding the documentation of LAST_VALUE() function in books online/ MSDN.Recently I was playing around with new analytic functions of SQL 2012. Was trying to implement the LAST_VALUE functionality when I noticed the…