Recently we had the requirement of importing data from multiple dbf files to SQL Server database. This post explains the step by step procedure of how this can be achieved using SSIS and also discusses the resolution to an issue…
T-SQL to DAX: Simulating PERCENTILE_CONT analytic function
PERCENTILE_CONT is used for calculating a percentile based on a continuous distribution of the column value. From ORACLE SQL documentation:The result of PERCENTILE_CONT is computed by linear interpolation between values after ordering them. Using the percentilevalue (P) and the number of rows…
T-SQL to DAX: Simulating CUME_DIST analytic function
CUME_DIST() analytical function was introduced in SQL 2012 for calculating the cumulative distribution of a value in a group of values.Its calculated as number of rows with value equal to or less than current value divided by total rows for…
T-SQL to DAX : Simulating PERCENT_RANK() analytic function
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…