How to use PIVOT in SQL SERVER for more than one columns?

Hi, folks! As a developer, I have to query SQL Server on regular basis but we hardly use PIVOT in our day to day lives. Even if we use PIVOT; we generally use it for a single column.

Today, I had to generate a report for one of our clients; according to their business requirement, I had to show them aggregated data which was dynamically calculated by a group by query.

Sample data after the above step looked something like this,

First Click2/22/2017bing2388103.292649
First Click2/22/2017facebook289642387
First Click2/22/2017google8199735.056566
Last Click2/22/2017bing2534385.592903
Last Click2/22/2017facebook304360380
Last Click2/22/2017google7884433.756148


I wanted this to be in the format below,



If you look carefully, all these are dynamically calculated columns. So, I needed to PIVOT on ‘Conversion’ and ‘Revenue’ columns simultaneously. I tried to surf around the internet hoping to find a quick workaround. I went through a couple of accepted solutions on stack overflow, but they were pretty complex to understand and implement.

I thought of a simple JUGAAD to implement this; I stored the results of pivot1 and pivot2 into tempTable1 and tempTable2 respectively then I used an inner join to get the aggregated transposed result which I required!

SELECT * INTO #tempTable1
 WHEN modelid = 1 THEN 'FirstClickRevenue'
 WHEN modelid = 2 THEN 'LastClickRevenue'
 WHEN modelid = 3 THEN 'WeightedRevenue'
 END AS ModelType,
 convert(date,conversiondate) AS Date,
 SUM(revenue) AS Revenue
 WHERE conversiondate >= @FromDate
 AND conversiondate <= @ToDate
 GROUP BY modelid,
 campaign) AS s PIVOT (sum(revenue)
 FOR [ModelType] IN (FirstClickRevenue,LastClickRevenue,WeightedRevenue))AS pvt1

SELECT * INTO #tempTable2
 WHEN modelid = 1 THEN 'FirstClickConversion'
 WHEN modelid = 2 THEN 'LastClickConversion'
 WHEN modelid = 3 THEN 'WeightedConversion'
 END AS ModelType,
 convert(date,conversiondate) AS Date,
 SUM(weight) AS Conversions
 WHERE conversiondate >= @FromDate
 AND conversiondate <= @ToDate
 GROUP BY modelid,
 campaign) AS s PIVOT (sum(conversions)
 FOR [ModelType] IN (FirstClickConversion,LastClickConversion,WeightedConversion))AS pvt2
ORDER BY date,campaign

SELECT p1.*,
FROM #tempTable2 p1
INNER JOIN #tempTable1 p2 ON
AND p1.campaign=p2.campaign

It’s a simple solution; a workaround for using PIVOT in special conditions like these! Nothing fancy but it efficiently serves the purpose; I hope it helps someone in need! 🙂

Here is the link to Microsoft documentation on using PIVOT.

Cheers! Happy coding and happy querying!

Share this blog

Criticizing SCRUM Constructively!

Scrum Process Image

Constructive criticism by definition means the process in which valid and well-reasoned opinions are offered which are backed by rational thinking, usually involving both positive and negative comments, in a decision oriented manner to improve the process or outcome as a whole without any bias.

We all know the power of scrum and agile development; if it is implemented and executed with certain tweaks then it can do wonders for the product and team working on it. As far as I understand scrum; it can be divided into 6 steps explained below.

1 – Creation of Product Backlog by Product Owner

Product Backlog is created by product owner to list out all the new tasks/ stories and divide them into groups according to their priority level.

2 – Couple of Grooming Sessions 

In grooming, task/ story details are discussed with the entire team and divided into sub-tasks. This step is very important because in this step entire team becomes familiar with the story requirement and expectation. Ideally story should be broken down into sub-tasks as small as possible. Product owner should try to be as specific as possible about the requirements.

3 – Assigning story points and locking sprint stories

This is the step where most of the errors occur. In this step entire team sits together and decides the story points based on it’s complexity and effort required to finish that story. Team has to decide and lock the stories which they will pick up in current sprint. Some teams overestimate (this is acceptable to some extent); while most of the teams underestimate (this is not acceptable at all)!

4 – Implementation planning of sprint

After selecting all the stories which have to be picked up in current sprint; it is important to plan and jot down the story deadline on a sprint calendar. It is very important to plan a sprint in such a way that everybody gets ample amount of time and a bit of buffer (for unexplainable set of time consuming events which may occur) for each and every story assigned to them.

5 – Daily scrum calls

After the sprint starts; it is very important to keep track of all the stories. Individual assignees should report entire team about daily targets and status of current story. Scrum master should act as a facilitator (rather than a dictator) making sure that sprint progresses smoothly.

6 – Retrospective meeting

After the end of the sprint; team again sits together and jots down all the positives and negatives of the concluded sprint. All the stories should be carefully reviewed in this meeting. There will be some areas where the team can improve upon; all these areas should be identified and an actionable plan should be carried out to avoid repeating same mistakes in next sprint.

On paper, above points gives a very solid and neat impression. But to be honest, implementing scrum can be quiet challenging sometimes.

Inspite of all the timely meetings and thorough plannings there are some concerns, inherent flaws and mistakes often committed which I would like to point out.

1 – Pressure to over-commit in sprint planning

An individual team member should not be pressurized directly or indirectly to over-commit. Everyone has his/her own limit. Scrum masters and product owners should adopt a balanced approach in which proper freedom should be given to team members to pick stories of their choice. Only that Developer should assess what he/she can accomplish in the upcoming Sprint. Undoubtedly, there should always be some kind of regulation or minimum points which he/she has to pick but no one should be forced to stretch. Whenever this thing happens; it will directly impact the mindset of team members. It will indirectly attractresentment, inefficiency and low quality work in long run.

2 – Working on interdependent stories

Sometimes different teams work on same set of modules. This results in utter chaos in most cases if there is an absence of proper documentation and planning. It has been observed frequently that in such cases defects/bugs are dodged from one team to another team and it creates ownership issues. Every team member will say that it’s not his/her bug and nobody will fix it. Ultimately the one who worked most recently will be unanimously chosen as scapegoat and he/she will have to fix that bug. Well, fixing bug is not a problem here and it will result in a better product eventually, but this unplanned work will affect the sprint performance of the scapegoat and his/her stories will spill or will eventually take a hit on quality aspects also. So, this situation should be avoided. In some extreme cases if it’s unavoidable then it’s better to spill a story then to push half-baked code to production.

3 – Unavoidable interruptions

Some teams work very closely with customers and production support teams, in such scenarios there can be unplanned interruptions in terms of client requests and production support issues. These issues are of high priority and can’t be dodged on to next sprint. Such teams should plan sprints defensively keeping in mind the average number of support requests they receive per day/ per week/ or per sprint (choose whatever metric you are comfortable with). All the issues/ requests should be properly examined and it’s priority should be cross checked with product owner. A proper demarcation should be made between issues and feature requests and should be dealt with accordingly.

4 – Degradation of quality

In scrum environment, there is high pressure of release cycles on each and every team. Everyone counts points  (if not everyone; most of them ); this sometimes pushes teams to deploy unoptimized and potentially breakable code to production. This will result in production defects and bugs; which will again have to be tracked by some defect logging system and will have to be fixed on high priority. Nobody will realize that it will affect current sprint and this is a never ending cycle. Deadlines are very important; that’s why sprint planning should be precise and concise without compromising on quality and coding standards.
5 – Check-points over shadows innovative solution

The strongest concern I’d pose here on Scrum is that we sometimes become excessively focused on checking the boxes to say that we are done with something, rather than being focused on finding innovative solutions to the problems which we were handed.
PS : I am not an expert on SCRUM and AGILE development; I am just another guy who can code a bit and slowly  evolving to be better at it. These are just my personal observations; most of them which I have faced over the course of my short professional career. But frankly, yes, I am quite critical of SCRUM and above are my justifications for the same.

Happy Coding ( and happy sprinting!)

Share this blog

How to choose a right startup to work for?

imageHi friends,

Last week I wrote a blog stating the positives of working at a startup. This time around I want to write about ‘the other side of this coin‘; I am hardly two years into tech & startup industry; I am still learning and evolving as a developer and as an individual. I will try to present my views with whatever limited knowledge I have.

Indian startup ecosystem is on adrenaline. It is booming day by day; funds are coming in from venture capitalists, angel investors and adventure seeking biggies.

The list of prestigious unicorn 1 billion club is increasing at a very fast pace.
Lots of Indian companies are making their debut in this list. From an overall perspective we can say that; this is a very good time to work for startups.

This morning as I was going through my Facebook news-feed, I came across this article; I was astonished to see that a promising startup with good funding had to lay-off hundreds of employees! Everyone may have their own perspective but, if we believe this article we can say that they made this move trying to make what they call a big structural and organisational change (basically trying to save few more bucks). May be the management was forced to do this thing; but I feel it is very bad and atrocious for those employees which are getting fired for no fault of theirs. I understand that in startups margin of mistake is very minimal; you are expected to perform continuously; sometimes employees get sacked due to under performance but laying them off without a solid reason cannot be accepted.

This raises a few questions…

– As a fresher or an aspiring developer should I join startups?
– What should be the minimum criteria of a startup?
– What things should I look forward to after I join a startup?

1) As a fresher or an aspiring developer should I join startups?

Hell yes, you should always look for such opportunities. A startup not only plays a crucial role in developing your technical abilities but also sculpts your attitude & approach towards a problem. It teaches you to handle pressure and perform under stressful environment. In bigger companies you won’t get to build something from scratch; but in startups you will probably get this chance. Building something from scratch gives you an insight in entire development cycle of that product; right from step 1 (step 0 in some cases!). You are the owner of that product and that product is your responsibility. It gives you immense technical prowess, exposure and satisfaction. You learn and adapt things very quickly. (You have to; you don’t have any other choice!)

Hell no, if you want a structured job with fixed and defined requirements. Most of the startups don’t define things clearly; their structure can be ambiguous. Working at a startup will always require a dedicated and passionate effort from your side. You can’t just log hours and chill around. You are expected to deliver in short deadlines. Sometimes you don’t have a road-map for navigation; you have to start and finish journey on your own; making mistakes and evolving from them and eventually learning a new lesson daily! Most of the startups provide good (industry-standard) salaries but they don’t provide important perks like health insurance, travel allowance etc. These things can be very important sometimes!

2) What should be the minimum criteria of that startup?

The startup should have a decent technology stack; you should not be working on outdated things. Your profile should reflect the type of work you will do in future (atleast a close estimate!). The startup should be well funded; atleast to a level where your monthly salary is guaranteed. Transparency is the key; you should be blatant in asking right questions in a right manner at right times and you should expect (honest) answers from the management. It is about your career; you should have full knowledge of what you are getting into. Most of the startups have a cool culture; but you should find yourself culturally fit among other peers. You will spent most of the time working your ass off over there; so it should be enjoyable for you and the people around you. You can’t ask for fancy things; but they should provide atleast a bare minimum (e.g., a good system to work on, office-desk essentials, public holidays 😛 etc.).

You should get whatever is promised (if not more!). In other words you should make sure that the terms of your contract are followed.

3) What things should I look forward to after I join a startup?

Get ready for one heck of a ride!!! It will be a journey of great learning and adventure if you have chosen a right startup. There will be times when everything may (and will) go wrong; you will have to persevere through all the insecuritiesand nervousness. It won’t be easy to work among coding-rockstars; you will face a great amount of peer pressure. Every one in the team will have a story to tell; you will wonder that when you will reach at their level. In the start, you may feel down or low in confidence (if you are joining as a fresher). It will be a big shift for you in terms work-ethics, ideology and routine for you. Suddenly you will be held accountable for anything that is not working or anything that is not built properly! You will find yourself working in stress sometimes but most of the times you will work passionately to kind of prove yourself (and others) that you can do better! Meanwhile there will be a lot of boozing, terrace parties and eating hangouts. In no time you will find yourself being recognized (if you work hard enough!).

Happy startup-hunting!

Share this blog

What it is like to work at a startup?

Ahoy mates!

– Every day is a roller coaster ride.

– You are not a valued employee. You are a pirate or a jedi instead.

– You work with a great bunch of motivated, talented and coolest geeks who are not shy to use F*** word a bit too frequently.

– Action may speak louder than words but code definitely speaks louder than any action!

– You are always updated about the coolest technical stuff happening all around the globe; thanks to the crazy folks all around you!

– You are not just a developer. You are a scrum-master, a deploy-king, a tester, a code-reviewer, a recruiter, an interviewer and sometimes you even get to pitch for your company! (If you are lucky enough..)

– You may feel intimidated and nervous at times!

– You learn the meaning of ownership and accountability.

– You continuously learn a lot of things simultaneously. Sometimes in a hardest way possible!

– Your mind suddenly gets tweaked to engage itself in provoking new thoughtsand ideas.

– You are free to choose and explore new technologies. Start-ups don’t believe in legacy tech!

– Your ideas  and grievances are heard effectively and immediately.

– You are expected to work hard and trained to work smart.

– You might get addicted to caffeine and beer in long run. 😀

Share this blog

Who are Fullstack Developers?


Software/ Website development can be categorized into,

Front endHTML, Javascript, CSS
Back endJava, PHP, ASP.NET / C#.NET, Ruby
DatabaseMicrosoft SQL Server, MySQL, Oracle

So by definition a developer who works on front-end is a front-end developer and a developer who works on back-end is a back-end developer.

Front-end developers are responsible for a website’s user-interface and the user-experience architecture. They work closely with designers to construct and improve the ui/ ux of a website. A good front-end developer can be able to accurately identify specific issues in user-experience and provide recommendations and coding solutions to improve the design.

Back-end developers generally handle the server and the data. Their job is to build an application and also to design/ implement it’s interaction with the server and the database. They manipulate data and also work with public and private API’s. A good back-end developer should have a sound knowledge of Linux/ Windows as a development and deployment system; he/ she should also have insights on different version control systems such as GIT/ SVN.

These were the specialized position of developers. But as the requirements continued to become more complicated and ambitious, some kickass people started to build frameworks and helper libraries. JQuery is the most common example, it made JavaScript development in the browser significantly easier, other examples are AngularJs, Knockout, Backbone, EmberJs. There were similar shifts in back-end technologies, such as Zend, Symfony, CakePHP forPHP and CodeIgnitor for Ruby on Rails and PHP both. Thus, today browsers have became more capable, and the frameworks are becoming excessively powerful.

This ignited the emergence of fullstack developers which blurred the lines between front-end and back-end developers. Start-ups played an important part to popularize this role. These developers are jack-of-all-trades and master-of-some. They provide full package and can cross functionally work on entire technology stack for a company. This is a win-win situation for both an individual and a company. For an individual developer, it adds solid skills on his/her resume, a solid learning experience is in store, moreover they get to work on some kickass and challenging stuff but sometimes the job of a fullstack developer can be very complex and demanding.

Happy coding and developing!

Share this blog