SQL Query to find ratio after finding numbers in table

Question

Hi,

I have a table and update two fields,. one is GCD (Greatest common divisor) and another column is Ratio of that numbers. For example

Declare @tong Table (Office Varchar(20), Male Int, Female Int, GCD Int, Ratio Varchar(20))
Insert Into @tong Values ('CTS', 40, 30, NULL, NULL)
Insert Into @tong Values ('IBM', 12, 14, NULL, NULL)
Insert Into @tong Values ('TCS', 28, 32, NULL, NULL)

In the above table in first record, GCD of 40 and 30 is 10 and ratio of that two numbers with 10 is 4:3
hence, i need to update the table like this

Update @tong set GCD = 10 , set Ratio = '4:3'

Likewise i need to update all the records in that table

Could you please help me to write a Sql query for this example.

Answer 1

Hi @Fazlu AM ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working.

Step1: create one function, refer to the stored procedure mentioned in this article.

CREATE FUNCTION dbo.UDF_MultiGCD (@str VARCHAR (500))  
RETURNS INT  
AS  
BEGIN  
   DECLARE @tb TABLE (i INT identity, spdata NVARCHAR (max))  
   DECLARE @strt INT  
   DECLARE @end INT  
   DECLARE @a INT  
   DECLARE @b INT  
   DECLARE @t INT  
   DECLARE @cnt INT  
   DECLARE @ind INT  
   
   SELECT @strt = 1, @end = CHARINDEX (',', @str)  
   WHILE @strt < LEN (@str) + 1  
   BEGIN  
      IF @end = 0 SET @end = LEN (@str) + 1  
      INSERT INTO @tb (spdata) VALUES (SUBSTRING (@str, @strt, @end - @strt))  
      SET @strt = @end + 1  
      SET @end = CHARINDEX (',', @str, @strt)  
   END  
   
   SELECT @cnt = max (i) FROM @tb  
   SELECT @a = convert (INT, spdata) FROM @tb WHERE i = 1  
   SET @ind = 2  
   WHILE @ind <= @cnt  
   BEGIN  
      SELECT @b = convert (INT, spdata) FROM @tb WHERE i = @ind  
      WHILE (@b % @a) != 0  
      BEGIN  
         SET @t = @b % @a  
         SET @b = @a  
         SET @a = @t  
      END  
      SET @ind = @ind + 1  
   END  
   RETURN  @a  
END  
GO  

Step2:call this funtion.

;WITH CTE AS (  
SELECT *,dbo.UDF_MultiGCD(CAST(Male AS CHAR(2))+','+CAST(Female AS CHAR(2)))  GCDNEW FROM @Office)  
UPDATE CTE  
SET GCD=GCDNEW,Ratio=CAST(Male/GCDNEW AS CHAR(1)) + ':'+  CAST(Female/GCDNEW AS CHAR(1))  
  
SELECT * FROM @Office  

Output:

Office Male Female GCD Ratio  
CTS 40 30 10 4:3  
IBM 12 14 2 6:7  
TCS 28 32 4 7:8  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.