Using SQL Server PIVOT feature to transform rows into columns

PIVOT queries in SQL Server are useful when we need to transform data from row-level to column-level.

Example: Imagine having the table named Invoice (payments per month) – for simplicity let’s say that we have PaidDate and Amount.

Id	PaidDate                    Amount
1	2014-01-05 12:34:22.000	    318.22
2	2014-01-06 11:44:00.000	    128.33
3	2014-04-07 16:44:00.000	     55.50
4	2014-02-08 15:12:00.000	     88.55
5	2014-02-16 18:39:00.000     115.45
6	2014-03-01 16:27:00.000	      9.99
7	2014-03-08 13:13:00.000	     16.99
8	2014-04-27 16:44:00.000	    568.69
9	2014-05-10 19:40:00.000	     43.65

What we wanted now is to get total money collected from the beginning of the year 2014, and in order to do this we can use this PIVOT query:

SELECT *
FROM (
  SELECT  YEAR(PaidDate) AS [Year],
          LEFT(DATENAME(month, PaidDate), 3) AS [Month],
          Amount FROM Invoice
) as PaymentsByYear
PIVOT
(
  SUM(Amount)
  FOR [Month] IN (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC)
) AS PaymentsByYear
ORDER BY [Year] DESC

And we finally get

Year    JAN     FEB     MAR     APR     MAY     JUN     JUL     AUG     SEP     OCT     NOV     DEC
2014	446.55  204.00  26.98   624.19  43.65   NULL    NULL    NULL    NULL    NULL    NULL    NULL

How to get TimeZone by RemoteIP or for specific city

The goal: to find out the TimeZone of a visitor.

There are few free services that can give you this information.

First step is to get the City name by RemoteIP address – there are free services/databases that helps you to achieve this, one of them is MaxMind.

When you have a city name you can get its latitude/longitude from the database of the same service or you can use Google API like this:

For Berlin, Germany:
http://maps.googleapis.com/maps/api/geocode/json?address=Berlin&sensor=false

You will get JSON result like this:

{
  "results" : [
  {
    "address_components" : [
    {
      "long_name" : "Berlin",
      "short_name" : "Berlin",
      "types" : [ "locality", "political" ]
    },
    {
      "long_name" : "Berlin",
      "short_name" : "Berlin",
      "types" : [ "administrative_area_level_1", "political" ]
    },
    {
      "long_name" : "Germany",
      "short_name" : "DE",
      "types" : [ "country", "political" ]
    }
    ],
    "formatted_address" : "Berlin, Germany",
      "geometry" :
      {
        "bounds" :
        {
          "northeast" :
          {
            "lat" : 52.6754542,
            "lng" : 13.7611176
          },
          "southwest" :
          {
            "lat" : 52.33962959999999,
            "lng" : 13.0911663
          }
        },
        "location" :
        {
          "lat" : 52.52000659999999,
          "lng" : 13.404954
        },
        "location_type" : "APPROXIMATE",
        "viewport" :
        {
           "northeast" :
           {
             "lat" : 52.6754542,
             "lng" : 13.7611176
           },
           "southwest" :
           {
             "lat" : 52.33962959999999,
             "lng" : 13.0911663
           }
         }
       },
       "types" : [ "locality", "political" ]
     }
   ],
   "status" : "OK"
}

After getting latitude and longitude of the city you can now ask Google for the TimeZone like this
https://maps.googleapis.com/maps/api/timezone/json?location=52.6754542,13.7611176&timestamp=1331161200&sensor=false

Note: timestamp is number of seconds since Jan 1st, 1970 which you can calculate in csharp (C#) using these function:

public static double ConvertToUnixTimestamp(DateTime date)
{
  DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
  TimeSpan diff = date.ToUniversalTime() - origin;
  return Math.Floor(diff.TotalSeconds);
}

public static DateTime ConvertFromUnixTimestamp(double timestamp)
{
 DateTime origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
 return origin.AddSeconds(timestamp);
}

And it will return this JSON result with TimeZone offset and name

{
  "dstOffset": 0,
  "rawOffset": 3600,
  "status": "OK",
  "timeZoneId": "Europe/Berlin",
  "timeZoneName": "Central European Standard Time"
}

Whom to blame for the poor sale?

There aren’t many doubts. It is always the Salesman and there are three reasons for the poor sale:

  1. Salesman didn’t choose the right customer or the right decision maker in the company

    This means that your product is maybe ideal fit for certain customers but the customer your salesman is talking to is not in this group. Also, if the customer says “we will think about it” it means that he already know from the beginning of the year where he will spend his budget and your product in not on his core needs list. If you get an answer “we don’t have a budget” this means that you are talking to the “tech guy” in the company and he is never the decision maker. Financial guy, the decision maker, makes the budget and if he decide that he needs your product he will find the budget (because he created it and other things that he planned to buy will suffer). Never try to sell your product to the tech guy (they understand completely your product, they will love it and they are polite) but they are not the decision makers (they think they are but they are not). You will just lost your time talking to them and avoid beeing sent to tech guy when you talk to financial guy (if you talk in tech terms and he does not understand you then you will be sent to the tech guy – means no sale).

  2. Salesman and Customer did not understand each other

    This is problem when salesman is not able to understand the urgent need that this customer has. Or the customer is not able to present his problem correctly (salesman should be able to overcome this situation). Maybe this customer’s urgent need is not covered by the product that salesman is selling, but it should not be a stopper to make a deal. Salesman will face later engineers in his company with obvious question: “How did you sell something that we don’t have?” but that is the life. It is important that the deal is closed, it will be already built somehow.

  3. Overall value of the selling product is lower than the similar product from the competition

    This is also the salesman error because he is trying to sell some uncompetitive product and he is persistent to do this indefinitely. It is time for him to change the company.

stress-anger-frustration
(source: agbeat.com)

If you are a tech guy (like me) then never talk about your product technical specifications, because you are wasting the customer’s time. Try to understand the real urgent need that this customer has, don’t asked him questions where he can answer simple with “Yes” or “No”. You need to be clever to make him give you the complete statement of the urgent problem that he has and he must do this personally (not by throwing your words into his mouth). When you come to the sale’s meeting, forget the passion about your product and talk at least as possible. Make him talk. Listen carefully.

Even if you like technology the most of everything and like spending most of the time working on your product, you must learn how to sell – no will done this better then you and you can not outsource the sales. In a good company around 50% of all budget is spent on sales activities.

Who will win: Shitty product with great marketing or excellent product with shitty marketing? Always the product with a great marketing.

This is what I remembered from the lecture of Matt Mayfield – “What Engineers Need to Unlearn to Sell”?

Tips and Tricks: Export GoogleMaps as image

You don’t really need to bother with exporting GoogleMaps position into the image because Google created StaticAPI that renders the whole map as image, instead of using JavaScript to make dynamic map.

Example:

<img alt="" src="//maps.googleapis.com/maps/api/staticmap?center=46.056988,14.515536&amp;zoom=15&amp;size=455x350&amp;maptype=roadmap&amp;markers=color:red%7Clabel:S%7C46.056988,14.515536&amp;sensor=false" border="0" />

This will results with this:

MS SQL Server 2014 “Hybrid” to attack MongoDB

On April 1st 2014 MS SQL Server 2014 is internally released for small group of big customers such as bwin and others. The biggest improvement is in-memory OLTP engine to deliver breakthrough performance to their mission critical applications. Company bwin scale its applications to 250K requests a second, a 16x increase from before, and provide an overall faster and smoother customer playing experience.

The new In-Memory OLTP engine (formerly code named Hekaton), provides significantly improved OLTP performance by moving selected tables and stored procedures into memory. Hekaton enables you to use both disk based tables and Memory-Optimized Tables together in the same queries and stored procedures.

The In-memory OTLP engine works with standard x64 hardware. The new In-Memory OLTP engine uses a completely new optimistic locking design that’s optimized for in-memory data operations. In addition, stored procedures are compiled into native Win64 code. The end result is far faster application performance.

Microsoft recommends that you provide an amount of memory that’s two times the on-disk size of the memory-optimized tables and indexes.

Why similarity to MongoDB? Because we were using it also and if you have enough RAM memory it keeps all indexes in RAM and is very quick plus it uses the advantages of SSD disks. Where SQL Server shines comparing to Mongo is when you need to group large amount of data and analyze them. Until this version of MS SQL 2014 (we didn’t test it) MongoDB was superior when it comes to large number of insert request into database. For analyzing the large amount of data MS SQL was better. Now, with new SQL Server 2014 it comes closer to MongoDB main advantage (process data in memory and use SSD advantages – if specified).

in-memory-oltp

(Source: sqlmag.com)

The following data types aren’t supported by memory-optimized tables:

  • datetimeoffset
  • geography
  • hierarchyid
  • image
  • ntext
  • sql_variant
  • text
  • varchar(max)
  • User data types (UDTs)

In addition, there are a number of database features that aren’t supported. Here are some of the most important database and table limitations:

  • Database mirroring isn’t supported.
  • The AUTO_CLOSE database option isn’t supported.
  • Database snapshots aren’t supported.
  • DBCC CHECKDB and DBCC CHECKTABLE don’t work.
  • Computed columns aren’t supported.
  • Triggers aren’t supported.
  • FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.
  • IDENTITY columns aren’t supported.
  • FILESTREAM storage isn’t supported.
  • ROWGUIDCOL isn’t supported.
  • Clustered indexes aren’t supported.
  • Memory-optimized tables support a maximum of eight indexes.
  • COLUMNSTORE indexes aren’t supported.
  • ALTER TABLE isn’t supported. In-Memory OLTP tables must be dropped and re-created.
  • Data compression isn’t supported.
  • Multiple Active Result Sets (MARS) aren’t supported.
  • Change Data Capture (CDC) isn’t supported.

Other SQL Server 2014 improvements

  • Using advantages of SSD disks technology (new buffer pool enhancements increase performance by extending SQL Server’s in-memory buffer pool to SSDs for faster paging)
  • AlwaysOn Availability Groups now support up to eight secondary replicas
  • Business Intelligence Enhancements (Power View can work against multidimensional cube data, new data visualization tool named Power Query, new visual data mapping feature named Power Maps)
  • SQL Server 2014 also supports encrypted backups
  • SQL Server 2014 will have the ability to scale up to 640 logical processors and 4 TB of memory in a physical environment. Plus, it has the ability to use up to 64 virtual processors and 1 TB of memory when running in a VM

More reading about this:

How to escape parenthesis/brackets ‘{‘, ‘}’ in string.Format?

Usually you have something like this:

string.Format("Output: {0}", "A");

This will result as:

Output: A

What you should do if you want to make an output like this?

Output: {A}

You need to escape parenthesis/brackets like this:

  • You use {{ to output {
  • You use }} to output }

So, you final C# command should look like this:

string.Format("Output: {{{0}}}", "A");

Follow

Get every new post delivered to your Inbox

Join other followers