Excel formula to SQL

Question

I would like to add a column with computed values to my MSSQL database, but I don't know how to create the SQL code

My data contains the columns PricePerUnit and Instance_Type

I would like the new computed value column to show what percentage cheaper each Instance_Type is versus the most expensive of that same Instance_Type. For example the most expensive c5.12xlarge is on the first row (London) and therefore is 0% cheaper, but the same c5.12xlarge is Ireland is cheaper by 4.95%, and in Oregon that identical Instance_Type of c5.12xlarge is 15.84% cheaper. I would like the computed value column in SQL to show 0% and 4.95%. 15.84% and so on.

In Excel I would use the following

formula =(MAXIFS(A:A,B:B,B2)-A2)/MAXIFS(A:A,B:B,B2)

The database table is called AmazonEC2

Here is an image of it working in Excel. The first blue table is identical to the data in the SQL database, the black table represents what I want to achieve in SQL.

I'm not understanding how the numbers are being calculated. For example, Ireland being 4.95% cheaper than...what? — jw11432, Mar 04 '20 at 23:14
"but the same c5.12xlarge is Ireland is cheaper by 4.95%" There is no c5.12xlarge associated to an Ireland location that I can see in the image. — jw11432, Mar 04 '20 at 23:21
Sorry, I hadn't realised the image had blacked out every other line when I uploaded it, not sure hat happened there. I must have has some transparency. The c5.12xlarge for Ireland was in one of the blacked out rows. — user2971938, Mar 05 '20 at 00:25
here is a larger version of the data set in excel https://mydevnet-my.sharepoint.com/:x:/g/personal/leon_mydevnet_net/Ec0-iXCrHHlDtfEmoNq1dJEBTVJebfPHOH4UIFJxAsWKcg — user2971938, Mar 05 '20 at 00:41

Isaac · Accepted Answer · 2020-03-05T03:03:49.243

I don't think it would be good (it may not even be possible) to do this as a computed column.

From the comments it seems the data type of PricePerUnit is nvarchar(). If that is the case I must point out that is poor database design. I understand that may be beyond your control to change.

Anyway, I created an AmazonEC2 table as I think you may have it. Using your spreadsheet of data I created insert statements to populate that table with your data using the following formula.

=CONCATENATE("insert into AmazonEC2 (PricePerUnit, Instance_Type, Instance_Family, Location) values ('", A2, "', ", "'", B2, "', '", C2, "', '", D2, "')")

I built all of that into a dbfiddle so you can see it in action and so that other people here can manipulate the data and try different approaches.

Here is final SQL statement to retrieve your data and calculate the PercentCheaper at the time of retrieval. You could also create a view based on this SQL statement.

SELECT
     x.PricePerUnit
   , x.Instance_Type
   , x.Instance_Family
   , x.Location
   , FORMAT((x.MaxPricePerUnit - convert(decimal(10, 2), PricePerUnit)) / x.MaxPricePerUnit, 'P') AS PercentCheaper
FROM (
         SELECT
              PricePerUnit
            , Instance_Type
            , Instance_Family
            , Location
            , MAX(convert(decimal(10, 2), PricePerUnit)) OVER (PARTITION BY Instance_Type) AS MaxPricePerUnit
         FROM AmazonEC2
     ) x;

What we are doing here is getting the maximum PricePerUnit for each Instance_Type in the subquery which I have aliased as "x". Then I select from that and perform the calculation to find the PercentCheaper for each row.

Since it seems PricePerUnit is an nvarchar() column you need to convert it to a number in order to do the calculations. Note that you do not need to convert MaxPricePerUnit because the conversion happened before it was used as an input to the MAX() function resulting in an output with a decimal(10,2) data type.

Thanks for the reponse. What does the "x." signify that prefixes most of the command? — user2971938, Mar 05 '20 at 00:56
The "x" is the alias (name) of the sub query at the end of the SQL statement. You can make it whatever you want (within reason). — Isaac, Mar 05 '20 at 01:02
When I run the code above I get the error....."Msg 402, Level 16, State 1, Line 6 The data types nvarchar and nvarchar are incompatible in the subtract operator." — user2971938, Mar 05 '20 at 01:08
Is your PricePerUnit column and nvarchar data type? It would be helpful if you posted your table definition and even some sample data for us to work with. — Isaac, Mar 05 '20 at 01:21
OMG that's amazing!!!. Just woke up and dropped that into my SSMS and it works a perfectly. I have learned so much off the back of your help. I cant tell you how much I appreciate you support. I just need to go away and learn things, such as what (convert(decimal(10, 2) does, but that the point of this exercise, educating myself with the help of this community. Thanks so much — user2971938, Mar 05 '20 at 11:36
convert(decimal(10, 2), PricePerUnit) is the full expression. What this is doing is taking the PricePerUnit value and converting it to a decimal with an appropriate precision and scale. — jw11432, Mar 05 '20 at 16:01

Excel formula to SQL

1 Answers1