question

FazluAM-9817 avatar image
0 Votes"
FazluAM-9817 asked MelissaMa-msft edited

SQL Query to find ratio after finding numbers in table

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 @Office Table (Office Varchar(20), Male Int, Female Int, GCD Int, Ratio Varchar(20))
Insert Into @Office Values ('CTS', 40, 30, NULL, NULL)
Insert Into @Office Values ('IBM', 12, 14, NULL, NULL)
Insert Into @Office 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 @Office 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.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @FazluAM-9817,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.