EMI Calculation in SQL Server

Hi, Here is an example of "EMI Calculation" of Your Product
Just put the value of @Amount, @Month, @Interest and @Downpayment (If Applicable).

Now you run this query in SQL Server.
The TOTAL Details will come...

DECLARE @Amount      AS FLOAT = 13999 --Put The TOTAL Price Amount.

DECLARE @Month       AS FLOAT = 24    --Put EMI Month (Year*12).

DECLARE @Interest    AS FLOAT = 14.5  --Put Interest Rate in Percentage.

DECLARE @Downpayment AS FLOAT = 2000  --Put the DownPayment Amount if Applicable.

---------------------------------------------------------------------------------

DECLARE @Rest     AS FLOAT = (@Amount - @Downpayment)

DECLARE @Rate     AS FLOAT = (@Interest/12)/100

DECLARE @STEP1    AS FLOAT = POWER((1+@Rate),@Month)

DECLARE @STEP2    AS FLOAT = (@STEP1 - 1)

DECLARE @STEP3    AS FLOAT = (@STEP1/@STEP2)

DECLARE @EMI      AS FLOAT = @Rest * @Rate * @STEP3

SELECT 'Price Amount Rs.' AS 'EMI Details',@Amount AS 'Value'

UNION ALL

SELECT 'DownPayment Amount Rs.',@Downpayment

UNION ALL

SELECT 'EMI Months',@Month

UNION ALL

SELECT 'EMI Years',(@Month/12)

UNION ALL

SELECT 'EMI Interest Rate (%)',@Interest

UNION ALL

SELECT 'EMI Amount Rs.',ROUND(@EMI,2)

UNION ALL

SELECT 'TOTAL EMI Amount Rs.',ROUND(@EMI,2)*@Month

UNION ALL

SELECT 'TOTAL Payable Amount Rs.',(ROUND(@EMI,2)*@Month) + @Downpayment

Import Data from Excel to SQL Server using Distributed Queries

SELECT *

FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;HDR=YES;Database=C:\Test1.xls',

       'SELECT * FROM [Sheet1$]')

For Better Help see this site

http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

Insert, Update or Delete Data in SQL Server from Excel Table

OPENROWSET Examples
Below are four examples to show some of the flexibility with the OPENROWSET command:

SELECT with a JOIN and ORDER BY Clause

Code Explanation - With the query below, 5 records should be returned to show a simple INNER JOIN statement can return a single result set from both data in the table ([Sales].[SalesPerson]) and Excel spreadsheet.

SELECT SP.[SalesPersonID] ,SP.[TerritoryID] ,SP.[SalesQuota] ,SP.[Bonus] ,SP.[CommissionPct],SP.[SalesYTD] ,SP.[SalesLastYear] ,SP.[rowguid] ,SP.[ModifiedDate] ,T.[SalesPersonID] ,T.[TerritoryID] FROM [AdventureWorks].[Sales].[SalesPerson] SP INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$]') TON SP.[SalesPersonID] = T.[SalesPersonID] AND SP.[TerritoryID] = T.[TerritoryID] ORDER BY SP.[SalesPersonID], SP.[TerritoryID] GO

INSERT with a SELECT Statement

Code Explanation - With the first block of code, five records are inserted into the  [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the INSERT_Example worksheet of the Excel spreadsheet.  In the second query, the data inserted is verified.

INSERT INTO [AdventureWorks].[Sales].[SalesPerson](SalesPersonID, TerritoryID, SalesQuota,Bonus, CommissionPct, SalesYTD, SalesLastYear, rowguid, ModifiedDate) SELECT SalesPersonID,TerritoryID ,SalesQuota ,Bonus ,CommissionPct ,SalesYTD ,SalesLastYear, NEWID(),GETDATE()FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus, CommissionPct, SalesYTD, SalesLastYear FROM [INSERT_Example$]') GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO

UPDATE with a JOIN Statement

Code Explanation - With the first block of code, five records are updated in the  [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the UPDATE_Example worksheet  of the Excel spreadsheet.  In the second query, the data updated is verified.

UPDATE SPSET SP.Bonus = T.BonusFROM [AdventureWorks].[Sales].[SalesPerson] SP INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;',  'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus FROM [UPDATE_Example$]') T ON SP.SalesPersonID = T.SalesPersonID AND SP.TerritoryID = T.TerritoryID  AND SP.SalesQuota = T.SalesQuota GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO

DELETE with a JOIN Statement

Code Explanation - With the first block of code, five records are deleted in the [AdventureWorks].[Sales].[SalesPerson] table by reading the data from the DELETE_Example worksheet of the Excel spreadsheet.  In the second query, the data deleted is verified.

DELETE SPFROM [AdventureWorks].[Sales].[SalesPerson] SPINNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus FROM [UPDATE_Example$]') TON SP.SalesPersonID = T.SalesPersonIDAND SP.TerritoryID = T.TerritoryID GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson] WHERE SalesPersonID IN (1, 2, 3, 4, 5)GO