Angular Directives with D3

It's been exactly 2 years since i started to learn Angular and it's sad that i dint write even a single blog on the same. Finally decided to start a series on the same topic. AngularJS is a JavaScript MVC Framework that integrates two-way data binding, web services, and build web components. There are enough number of blogs and tutorials to follow on the same.

The current product which i am working is a data visualization tool which is built on AngularJS  and has many visualization  been integrated with D3.js.

In this blog, will be describing how to build a directive using d3.js and angular.

Directive is very powerful feature of AngularJS. It easily wired up with controller, html and do the DOM manipulations.

Building a Decomposition Force directed d3 directive:

 App.directive('forceGraph', function() {  
   return {  
     restrict: 'EA',  
     transclude: true,  
     scope: {  
       chartData: '='  
     },  
     controller: 'hierarchySummaryCtrl',  
     link: function(scope, elem, attrs) {  
       var svg;  
       elem.bind("onmouseover", function(event) {  
         scope.svg = svg;  
         console.log("hierarchy svg", scope.svg);  
         scope.$apply();  
       });  
       scope.$watch('chartData', function(newValue, oldValue) {  
         if (newValue) {  
           scope.draw(newValue.data,newValue.id);  
         }  
       });  
       scope.draw = function(rootData,divID) {  
         var width = 400,  
           height = 320,  
           root;  
         var force = d3.layout.force()  
           .linkDistance(80)  
           .charge(-120)  
           .gravity(.05)  
           .size([width, height])  
           .on("tick", tick);  
         var divid = "#" + divID;  
         d3.select(divid).selectAll("*").remove();  
         svg = d3.select(divid)  
           .append("svg").attr("viewBox", "0 0 400 400")  
           .attr("width", '100%')  
           .attr("height", '100%');  
         var link = svg.selectAll(".link"),  
           node = svg.selectAll(".node");  
         root = rootData;  
         update();  
         console.log(svg);  
         scope.setSvg(svg[0][0].innerHTML);        
         function update() {  
           console.log(nodes)  
           var nodes = flatten(root),           
           links = d3.layout.tree().links(nodes);          
           var nodes = flatten(rootData),           
           links = d3.layout.tree().links(nodes);             
           force.nodes(nodes)  
             .links(links)  
             .start();  
           // Update links.  
           link = link.data(links, function(d) {  
             return d.target.id;  
           });  
           link.exit().remove();  
           link.enter().insert("line", ".node")  
             .attr("class", "link");  
           // Update nodes.  
           node = node.data(nodes, function(d) {  
             return d.id;  
           });  
           node.exit().remove();  
           var nodeEnter = node.enter().append("g")  
             .attr("class", "node")  
             .on("click", click)  
             .call(force.drag);  
           nodeEnter.append("circle")  
             .attr("r", function(d) {  
               return Math.sqrt(d.size) / 5 || 4.5;  
             });  
           nodeEnter.append("text")  
             .attr("dy", ".25em")  
             .text(function(d) {  
               return d.name + ", Count: " + d.size;  
             });  
           node.select("circle")  
             .style("fill", color);  
         }  
         function tick() {  
           link.attr("x1", function(d) {  
               return d.source.x;  
             })  
             .attr("y1", function(d) {  
               return d.source.y;  
             })  
             .attr("x2", function(d) {  
               return d.target.x;  
             })  
             .attr("y2", function(d) {  
               return d.target.y;  
             });  
           node.attr("transform", function(d) {  
             return "translate(" + d.x + "," + d.y + ")";  
           });  
         }  
         function color(d) {  
           return d._children ? "#FFEB3B" // collapsed package  
             :  
             d.children ? "#F44336" // expanded package  
             :  
             "#D32F2F"; // leaf node  
         }  
         // Toggle children on click.  
         function click(d) {  
           if (d3.event.defaultPrevented) return; // ignore drag  
           if (d.children) {  
             d._children = d.children;  
             d.children = null;  
           } else {  
             d.children = d._children;  
             d._children = null;  
           }  
           update();  
         }  
         // Returns a list of all nodes under the root.  
         function flatten(root) {  
           var nodes = [],  
             i = 0;  
           function recurse(node) {  
             if (node.children) node.children.forEach(recurse);  
             if (!node.id) node.id = ++i;  
             nodes.push(node);  
           }  
           recurse(root);  
           return nodes;  
         }  
       };  
     }  
   };  
 });  

My Repository With the sample

Digging into BigData with Google's BigQuery

Well, i was one of the speaker at Colombo Big Data Meetup which was held yesterday and i spoke about Google's bigquery. Hence i have decided to write a blog on that so that you could get benefited if you are a BigData Fan.

What is Big Data?

There are so many definitions for Big Data , let me explain what does it really mean? In the near feature, every object on this earth will be generating data including our body.We have been exposed to so much information everyday.In vast ocean of data, complete picture of where  we live where we go and what we say, its all been recorded and stored forever.More data allows us to see new , better different things.Data in the recent times have changed from stationary and static to fluid and dynamic.we rely a lot on data and thatch is  major part of any business.we live in a very exciting world  today, a world where technology is advancing at a staggering pace, a world data is exploding, tons of data being generated. 10 years before we were measuring data in mega bytes, today we are talking about data which is in petabyte size, may be in few years we are going to reach zetabyte era, that means the end of English alphabets.Does it means the end of Big Data? .No . If you have shared a photo or post or a tweet on any social media,You are one of them who is generating data, and you are doing it very rapidly.

More than 100 thousand tweets in 60 seconds are generated , more than 7 million posts have been posted on Facebook, before you read this sentence.So the data is generated faster  than you could ever think before.Big data and analysis has exploded recently but there is a barrier. That barrier is indeed it needs lot of money resources and time to setup the infrastructure.Also it needs skillful people to make it all happen. so google solves all these big query.Big query,One of the products of google cloud platform that allows us to easily work with big data.It is google's fully managed data analysis service offering in the cloud. It enables super fast analysis.Easily store and analyse big data in google infrastructure.

Lets get familiar with the components.
 Projects are going to be the top level item inside the google cloud platform. Project contains, users authentication billing information and that is where data sets are going to live.
 Data set is really a container for tables.Access controllers cannot be done on tables so that they are don  through projects and data sets.Project contains data sets, data sets contains tables.
 Tables where the data lives
 Jobs are going to be asynchronous process that run on the background to load, export and to execute large queries.

Lets see How to use google cloud platform for big data solution.Architecture is divided in to two workflows named data workflow and visualization workflow.We need to get our source data into big query using any ETL tool and pipe into google cloud storage. Extract it from source and de normalize it,Biquery likes less joins the better .we can use hadoop clusters running on computers to do many pre processing and transforming data.once its in bigquery its all about visualization. Most of the use cases are log analysis which is used to analyse application behavior and user behavior in order to improve the system.Retail forecast - the more data, business has the more accurately they can predict product sales for the next month,that allows them to plan better. Lets see how we can use big query to analyse lots of data in very short time they handle the infrastructure and we can just simply focus on getting our data and analyse it.

Google handles Big Data every second of every day to provide services like Search, YouTube, Gmail and Google Docs.Can you imagine how Google handles this kind of Big Data during daily operations? How they are doing it?

As an example, let’s consider the following SQL query, which requests the Wikipedia® content titles that includes numeric characters in it:

select count(*) from publicdata:samples.wikipedia where REGEXP_MATCH
(title, ‘[0-9]*’) AND wp_namespace = 0;

Notice the following:
• This “wikipedia” table holds all the change history records on Wikipedia’s article content and consists of 314 millions of rows – that’s 35.7GB.

• The expression REGEXP_MATCH(title, ‘[0-9]+’) means it executes a regular expression matching on title of each change history record to extract rows that includes numeric characters in its title (e.g. “United States presidential election, 2015”).
• Most importantly, note that there was no index or any pre-aggregated values
for this table prepared in advance.

Dremel can even execute a complex regular expression text matching on ahuge logging table that consists of about 35 billion rows and 20 TB, in merely tens of seconds. This is the power of Dremel; it has super high scalability and most of the time it returns results within seconds or tens of seconds no matter how big the queried data set is.

Two core technologies which gives Dremel this performance:

1. Columnar Storage. Data is stored in a columnar storage fashion which
makes possible to achieve very high compression ratio and scan throughput.
2. Tree Architecture is used for dispatching queries and aggregating results
across thousands of machines in a few seconds.

Columnar Storage
Dremel stores data in its columnar storage, which means it separates a record into column values and stores each value on different storage volume, whereas
traditional databases normally store the whole record on one volume.

• Traffic minimization. Only required column values on each query are scanned and transferred on query execution. For example, a query “SELECT top(title) FROM foo” would access the title column values only. In case of the Wikipedia table example, the query would scan only 9.13GB out of 35.7GB.
• Higher compression ratio. One study  reports that columnar storage can  achieve a compression ratio of 1:10, whereas ordinary row-based storage can compress at roughly 1:3. Because each column would have similar values, especially if the cardinality of the column (variation of possible column values) is low, it’s easier to gain higher compression ratios than row-based storage. Columnar storage has the disadvantage of not working efficiently when updating existing records. In the case of Dremel, it simply doesn’t support any update operations.

Tree Architecture
One of the challenges Google had in designing Dremel was how to dispatch queries and collect results across tens of thousands of machines in a matter of seconds. The challenge was resolved by using the Tree architecture. The architecture forms a massively parallel distributed tree for pushing down a query to the tree and then aggregating the results from the leaves at a blazingly fast speed.

The tree architecture also enables multiple queries to run at once within the tree, which lets
different users share the same hardware.You might have heard of hadoop map reduce mechanism,
so what is the difference between mad reduce and bigquery

Bigquery can be integrated in to applications in so many ways, Following are the integrations supported by bigquery,

Rest API (SDK) 

1. Google Spreadsheet
2. Web application

Interfaces for query:

1. Command Line Tool
2. Bigquery UI

Connectors for excel

Tools for Big Data Solution:

As mentioned in the above architecture following tools can be used for managing the data ingest and visualization.

Tableau,BIME and DigIn for analysing and creating visualizations for various insights. Talend and SQLStream for the ingestion of data into bigquery from various data sources.

Nothing comes free, since google handles the infrastructure there is bit of a cost involved and the pricing goes as below.

Once you have decided to use bigquery there are certain things you need to know before using for optimizations and less cost.

Do not use queries that contains Select * , which is going to execute entire dataset and hence it will result in a high cost.

Since bigquery stores values in nested fields it is always better to use repeated fields.

Store in multiple tables as possible since it is recommended not to have JOINS

Bigquery also supports extensions such as ebq and dry run to encrypt the data and for executing the query to actually check how much resources that actual query is going to consume, which makes lot of developers and data analysts job easy.

I will be writing two separate blogs in the coming days on how to integrate with Bigquery and How to ingest the data into bigquery.

You can find the slides of the presentation from here

Business Intelligence Solution for an Insurance Industry

Business Intelligence and It's impacts:

Hello! I am back, after what I realized was my first extended blog break in four years. This time something technical, which is very much relevant to the product which i am currently working on. First of all, what is Business Intelligence?  It is the process, tools and infrastructure for generating insights from raw data is collectively called Business Intelligence.

Every organization generates data as part of its operations. Every organization also has access to some form of external data. But in order to analyze and take informed decisions, the data has to be processed and turned into information, and the information has to be presented in a meaningful way to be able to identify patterns or to see key performance indicators (KPIs), and thereby generate insights out of the information.          

This blog mainly describes the importance of BI in an insurance industry since i have to do my final project  where a BI solution can be used and come up with a solution with a collection of tools.

BI Solution for an Insurance Industry:

With rising globalization and growth, Business intelligence has become important for many firms. Business Intelligence solutions help these firms to transform into a dynamic enterprise through actionable intelligence. One of the important sector in this modern world is the insurance firms. In terms of technology, insurance companies are generally not at the forefront of technology and their systems are way behind. I have taken this domain as my project with some of the open source and commercial business intelligence tools that could help this sector.

The Organization i picked was HNB assurance PLC, it is a leading Sri Lankan Insurance corporation which provides Life Insurance solutions for Sri Lankan citizens.

The following are the Analysis that i have performed on the sample data that i have collected.

Classification of their Customer profiles using Decision tree and plotted in GeoMaps
Sentimental analysis of the company's Facebook Page
Various insights for their Insurance Claims
Sales forecasting – Dashboard

BI Tool that i have used : 

DigIn 

DigIn is the only true end-to-end analytics platform that allows you to easily visualize your structured and unstructured data in one place.Also it has On-demand data ingest capabilities and in-memory caching allows anyone to access data from anywhere at anytime from any device.

Conclusion: 

Use of business intelligence and analytic tool/solution is very vital for any insurance company wanting to succeed in an increasingly competitive industry. The ability to turn large volumes of raw data into actionable insights represents a significant value proposition for these businesses. These insights can be priceless in terms of the limitless opportunities they can unearth across the business with the help of social media analytics. Hope, This BI tool that i have suggested for this organization is a wonder not a
blunder.

You could find the slides that i have used for my presentation Here

DuoSoftware, Not Just another Software Company

Time has gone extremely fast, and I can still recall the day that I joined Duo. I can safely say that Duo is an awesome place to work at since I have had the opportunity to work with awesome, highly motivated people who loves the company. I never had much trouble with work life balance as I only worked more when I felt like it, rather than someone telling me to do. It truly is a different experience working for a Product Company which gives you an environment (Innovation engine) and Core focus on developing cutting-edge components.

Unlike most of the top IT firms in Sri Lanka, Duo gives  much importance to innovation and out of the box thinking. If you have something new and substantial to offer, trust me you are going to go a long way. It is a great place to start your career. Work culture is very positive and is full of energy. Talents are recognized and appreciated. Most importantly, one of the places which gives importance to work life balance!

Why I think it’s a great place to work?

Flat Hierarchy and Freedom of Ideas:

One of the things I love about Duo Software is that there's no sense that management is hiding somewhere making decisions and then condescendingly telling you what to do without listening to input. From CEO to the person who provides tea is constantly asking for input from everyone in the company and is available for discussing where we should go. It is a company, still at a size where you can get to know everybody and things still feel like a family. And it's exciting to watch the company itself evolve and grow.

Great Learning:

Duo Software has a lot of smart, hard-working people. The best engineers I have ever had the privilege of working with. For developers out there it’s a company that uses “GoLang”, a fun new language, as well as Couch Base, Redis, AngularJS and a range of other cool new technologies that will be good to know for the rest of your career.

Here at Duosoftware,

“Developers don't need dress code,

                                          They do write best code!”

Awesome office:

Some heavy metal music to keep you awake, a pool table which is free to be used by the team whenever they like, a reception hall with a pretty receptionist, a group of crazy people from different places belonging to one family. What more  could you ask for?

More Opportunity and Responsibility:

As of my knowledge, DuoSoftware is producing an exciting product that is constantly evolving and exploring the cutting edge, thereby a developer can become a full professional stack developer.

The Best “CEO”:

Last but not the least, one of my inspirations Mr.Muhunthan Canagey. As an aspiring tech entrepreneur, I think his best asset and what led him to success was being optimistic. He sees the problem in many perspectives and he thinks of solving the problem in many dimensions. An outstanding human being, that any employee  would be proud to to have as CEO.

Best memories you would have when you are at Duosoftware:


*Yes at Duo Software we don’t miss an opportunity to celebrate whether it's a birthday or it’s someone's first salary in office.Duo Software boasts of an overloaded fridge to provide drinks (only cool) at work, and a pool table to use and to a take your mind off work and colleagues do much more than computer games to relax and bond.Late night working  with colleagues during critical times is always a fun experience and takes the stress away from pressure situations.

There is a saying “Love your job but don't love your company, because you may not know when your company stops loving you” which exactly tells the current corporate world. But if you are at Duo Software, you will never stop loving the Company as it is a company that gives you so much more than mere work experience in return.

Finally, my journey with Duo Software is remarkable, and I hope the upcoming days will be equally fulfilling and challenging, towards my next goal. I hope this blog will also serve some help to new members of team Duo  or laterals willing to join Duo Software.   

          

Tips to become a good Coder/Programmer

I am not a great programmer, but i love coding and trying my best to become a good coder, there are few things i got to know when i made a research on how to become an efficient programmer.

1. Search for a problem that's really annoying you that's tractable to being solved by some way of automation.

 2. Made a Google search for programmatic solutions to these kind of problems. Make a research on which languages are most commonly used and scalable to satisfy your wider needs. In all ways.

  3. Start trying to solve the problem by breaking down the problem down into smaller and smaller chunks and trying to find ways of implementing a solution in the language which you have chosen from the above step

4 .Code all the time, continuous practice whenever you have time. Keep trying to solve your problem- where you get stuck, follow people who seem to know what they're doing on github,stackoverflow and post their solutions. Ask whatever questions may be even if its not practical on stack overflow(If you don’t care about vote downs). Buy and read lot of books. Get over the "monkey-see-monkey-do" approach, especially when getting issues. Learn from your issues  by going fixing the immediate issue  to learning what class of conditions the error deals with.

5. Subscribe to online education sites like Codecademy, Peepcode, Udemy, CodeSchool. Copy, imitate, fail, bang and scream the desk. Smash your keyboard if code you wrote does not work, try again. Feel the elation of getting a small chunk right.  

6. Every problem has (n+1) solutions,where n is the number of solutions that we try and 1 is that we have not tried. Every developer love that feeling when they’ve been coding something for a while, and it finally works. Yes, it is daunting. Yes, the learning curve is ridiculously steep. But you will only learn by doing and not being afraid to get it wrong.Whenever you write code always try to comment, so that other users who use your code will have an idea on what have already written. When you find a technique that works  or solve your problem generalize it and keep your own utility library.This makes yourlife and others easy.

 7. Learn to refactor your code.Whenever you get time try to improve the code.Whenever you Get confused with all your changes,  pick up git,tfs,svn or another version control system. Collaborate on others projects. Refactor again with what you've learned.  

I’ll finish with two quotes:

“Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.”- Brian Kernighan

“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”- Rick Osborne

SQL 2012 Built In Functions

Well, i have decided to write on technologies finally, it was one of the good sessions from SQL meetup  which was held at Microsoft srilanka on this month. I learnt some useful and simple sql functions which is present in sql server 2012.

SQL Server 2012 introduces many new rich built-in functions that offer more solutions to common T-SQL challenges.Some of the main functions which am going to write about are,

Conversion functions

• PARSE (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL)

Date and time functions

• DATEFROMPARTS (Transact-SQL)
• DATETIMEFROMPARTS (Transact-SQL)
• DATETIME2FROMPARTS (Transact-SQL)
• SMALLDATETIMEFROMPARTS (Transact-SQL)
• DATETIMEOFFSETFROMPARTS (Transact-SQL)
• TIMEFROMPARTS (Transact-SQL)
• EOMONTH (Transact-SQL)

Logical functions

• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)

String functions

• CONCAT (Transact-SQL)
• FORMAT (Transact-SQL)

In sql 2008 , in order to find a trend we were using CTE functions,  For example to find the trend in 2008,

WITH CTE AS (
SELECT rownum = ROW_NUMBER() OVER (order by WorkOrderID),OrderQtyFROM
[AdventureWorks].[Production].[WorkOrder])
SELECT CASEWHEN CTE.OrderQty-PREVCTE.OrderQty >0 THEN 'UP'
 WHEN CTE.OrderQty-PREVCTE.OrderQty <0 p="" then="">  ELSE 'NA'
  END AS Trand
FROM CTELEFT OUTER JOIN CTE AS PREVCTE ON PREVCTE.rownum = CTE.rownum – 1

but SQL 2012 made it more easier, I have used AdventureWorkds2012 database,

LAG and LEAD

No longer need to use a self-join or CTE.

LAG: Access data from previous rows in the result set.

LEAD: Access data from future rows in the result set

Advantage:

Increase in performance

Paging Data:

Again in order to do paging with sql 2008 we have to write a cte function, for example to get the first 20 rows from a table, the function will be as follows,

Start with 0, Page Size:20

WITH Paging_CTE AS ( SELECT TransactionID , ProductID , TransactionDate , Quantity , ActualCost , ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNumber FROM Production.TransactionHistory ) SELECT TransactionID , ProductID , TransactionDate , Quantity , ActualCost FROM Paging_CTE WHERE RowNumber > 0 AND RowNumber <= 20

SQL 2012

OFFSET & FETCH

OFFSET provides a starting row from which to display the result set.

FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point

FIRST_VALUE and LAST_VALUE

FIRST_VALUE: Retrieves the first value in a partition.

LAST_VALUE: Retrieves the last value in a partition.

PARSE:
Parse a value and returns the result of an expression, translated to specified data type. It will raise error if translation isn’t possible. Since this  function relies on CLR. It has better performance.

Syntax:

PARSE ( string_value AS data_type [ USING culture ] )

PARSE(), expects three parameters,

string_value – String value to parse into the specified data type.

data_type – Return data type, numeric or datetime type

culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

example 

SELECT PARSE('18/02/2014' AS datetime2 USING 'en-GB') AS [Using PARSE Function]

GO

OUTPUT

2012-02-18 00:00:00.000

TRY_PARSE   – TRY_PARSE() function is similar to PARSE() with only difference is that it returns NULL if the conversion is not possible.

Syntax:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

TRY_PARSE(), expects three parameters,

string_value – String value to parse into the specified data type.

data_type – Return data type, numeric or datetime type

culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

SELECT TRY_PARSE(’10.30′ AS INT) AS [Using TRY_PARSE Function]

GO

OUPUT:
NULL
Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.

TRY_CONVERT – Checks whether conversion from one type to other is possible or not. It transforms the source data into the target type if the conversion is possible. Returns NULL if the conversion is not possible.

Syntax:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

TRY_CONVERT(), expects three parameters,

data_type – The data type into which to cast expression.

expression – The value to be cast.

style – Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

Example,

SELECT TRY_CONVERT(datetime2, ’02/18/2012′, 103) AS [Using TRY_CONVERT Function];

GO

OUTPUT:

NULL

As you can see the both queries fail conversion but only first query that is using COVERT function throws error whereas query that is using TRY_CONVERT returns NULL indicating that conversion fails.

Date and time functions

These functions are mainly to manipulate data related to dates.

DATEFROMPARTS – The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default.

DATEFROMPARTS ( year, month, day )

DATEFROMPARTS(), expects three parameters,

Year – Integer expression specifying a year.

Month – Integer expression specifying a month, from 1 to 12.

Day – Integer expression specifying a day.

DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.

Syntax:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.

Syntax:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIMEFROMPARTS(), expects seven parameters,

Year – Integer expression specifying a year.

Month – Integer expression specifying a month, from 1 to 12.

Day – Integer expression specifying a day.

Hour – Integer expression specifying hours.

Minute – Integer expression specifying minutes.

Seconds – Integer expression specifying seconds.

Milliseconds – Integer expression specifying milliseconds.

DATETIME2FROMPARTS – The DATETIME2FROMPARTS function, returns full datetime2 value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified precision.

Syntax:

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

DATETIME2FROMPARTS(), expects eight parameters,

Year – Integer expression specifying a year.

Month – Integer expression specifying a month, from 1 to 12.

Day – Integer expression specifying a day.

Hour – Integer expression specifying hours.

Minute – Integer expression specifying minutes.

Seconds – Integer expression specifying seconds.

Fractions – Integer expression specifying fractions.

Precision – Integer literal specifying the precision of the datetime2 value to be returned.

SMALLDATETIMEFROMPARTS – The SMALLDATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour and the specified minute.

Syntax:

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

SMALLDATETIMEFROMPARTS(), expects five parameters,

Year – Integer expression specifying a year.

Month – Integer expression specifying a month, from 1 to 12.

Day – Integer expression specifying a day.

Hour – Integer expression specifying hours.

Minute – Integer expression specifying minutes.

DATETIMEOFFSETFROMPARTS – The DATETIMEOFFSETFROMPARTS function returns a full datetimeoffset. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

Syntax:

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

DATETIMEOFFSETFROMPARTS(), expects ten parameters,

Year – Integer expression specifying a year.

Month – Integer expression specifying a month, from 1 to 12.

Day – Integer expression specifying a day.

Hour – Integer expression specifying hours.

Minute – Integer expression specifying minutes.

Seconds – Integer expression specifying seconds.

Fractions – Integer expression specifying fractions.

Hour_offset – Integer expression specifying the hour portion of the time zone offset.

Minute_offset – Integer expression specifying the minute portion of the time zone offset.

Precision – Integer literal specifying the precision of the datetimeoffset value to be returned.

TIMEFROMPARTS – The TIMEFROMPARTS function, returns a full time value as shown in the below query result. It is important to note that the fractions argument actually depends on the precision argument.

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

Syntax:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

TIMEFROMPARTS(), expects five parameters,

Hour – Integer expression specifying hours.

Minute – Integer expression specifying minutes.

Seconds – Integer expression specifying seconds.

Fractions – Integer expression specifying fractions.

Precision – Integer literal specifying the precision of the time value to be returned.

EOMONTH –

This is very handy function with sql server 2012. This function takes two parameters first being start_date which is mandatory and the second one is Month_to_add which is optional. This function will return the last day of the month (also termed as EOM or end of the month) for the given date.

EOMONTH ( start_date [, month_to_add ] )

EOMONTH(), expects two parameters,

Start_date – Date expression specifying the date for which to return the last day of the month.

Month_to_add – Optional integer expression specifying the number of months to add to start_date.

Example:

Execute the following:

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE, 1) AS [Using EOMONTH Function]

GO

Here is the result set:

Using EOMONTH Function

2014-02-28

Logical Functions:

CHOOSE – This function can be used to return the value out of a list based on its index number (Note: Index no. here starts from 1).

Syntax:

CHOOSE ( index, val_1, val_2 [, val_n ] )

CHOOSE(), expects two parameters,

Index – Is an integer expression that represents a 1-based index into the list of the items following it.

Value – List of values of any data type.

IIF – Conditional function which will return the value based on the condition you specified as the first argument. This is similar to SSRS IIF expression.

Syntax:

IIF ( boolean_expression, true_value, false_value )

IIF(), expects three parameters,

Boolean_expression – A valid Boolean expression.

True_value – Value to return if boolean_expression evaluates to true.

False_value – Value to return if boolean_expression evaluates to false.

String Functions:

CONCAT – It’s the same concatenate function that we use in excel, it will concatenate two or more strings to make it single string. It implicitly converts all arguments to string types.

example,

select CONCAT(NULL,'Yahoo') AS NullYahoo

FORMAT – Function for locale-aware formatting of date/time and number values as strings.

Syntax:

FORMAT ( value, format [, culture ] )

FORMAT(), expects at three parameters,

Value – Expression of a supported data type to format.

Format – nvarchar format pattern.

Culture – Optional nvarchar argument specifying a culture.

THROW -

Raises an exception and transfers execution to a CATCH block of a try/catch statement.
Reduces the need to use RAISERROR in TRY/CATCH blocks.

Can provide custom error messages.

Example,

BEGIN TRY

   SELECT CONVERT(INT,'A1000')

END TRY

BEGIN CATCH

THROW 51000, 'Can not convert this string(Throw an error by Asanka)', 1;

END CATCH;

These are the various SQL built in functions that are quite useful for any SQL lovers!