CODEWITHSUNDEEP
sqlsql-server
Nits Patel
Nits Patel

Reputation: 390

Calculate Record Row by Row and some calculation SQL Server

I'm using SQL Server 15.0.2070.41, and I have a table like:

Name | Price | No_Of_Person | Name_Of_Person 
-----+-------+--------------+------------------
Nits |  100  |   2          | Risa, Bit            
Nits |  200  |   3          | Nits, Dish, Bit        
Nits |   80  |   4          | Bit, Risa, Nits, Dish 
Nits |   20  |   3          | Bit, Nits, Nash

There is a total of 5 members are there whose names are Nits, Dish, Risa, Bit, Nash in this table name column shows the name of the person who had spend his own money.

How can I calculate which person will pay how much to Nits?

I tried something like:

Select 
    name, Item, Price, No_Of_Person, Name_Of_Persian, Note, Date,  
    Price / No_Of_Person as 'result' 
From 
    tb_Expense_Details 

SELECT 
    price, Name_Of_Person 
FROM
    tb_Expense_Details  
WHERE
    name = 'Risa' 
GROUP BY
    price, Name_Of_Person

Sample data:

ID  Name    Item    Price   No_Of_Persion   Name_Of_Persion Note    Date
1   nitis   ANY     50         2             Rishi,Nitish         2020-03-09
2   nishan  ANY     300        3             Rishi,Nitish,Nishant 2020-03-09

I am Getting output:

Name    Person  Price
nishant Nishant 100.000000000000
nishant Nitish  100.000000000000
nitish  Nitish  25.000000000000
nishant Rishi   100.000000000000
nitish  Rishi   25.000000000000

But I Want output Like:

Nishant  100
Nitish   125
Rishi    125

It Means Person column should not contain duplicate value it should be addition of respected price

Upvotes: 0

Views: 100

Answers (1)

Zhorov
Zhorov

Reputation: 29943

One possible approach to get the expected result is the following statement. You need to split the values in the Name_Of_Person column and make aggregation:

Table:

CREATE TABLE tbl_Expence_Details ( 
   [ID] [int] IDENTITY(1,1) NOT NULL, 
   [Name] [nvarchar](max) NULL, 
   [Item] [nvarchar](max) NULL, 
   [Price] [int] NULL, 
   [No_Of_Persion] [int] NULL, 
   [Name_Of_Persion] [nvarchar](max) NULL, 
   [Note] [nvarchar](max) NULL, 
   [Date] [date] NULL 
)
INSERT INTO tbl_Expence_Details
   (Name, Item, Price, No_Of_Persion, Name_Of_Persion)
VALUES   
   ('nitis',  'ANY', 50,  2, 'Rishi,Nitish'),
   ('nishan', 'ANY', 300, 3, 'Rishi,Nitish,Nishant')

Statement:

SELECT 
   RTRIM(LTRIM(s.[value])) AS Person, 
   SUM(1.0 * t.Price / t.No_Of_Persion) AS Price
FROM tbl_Expence_Details t
CROSS APPLY STRING_SPLIT(t.Name_Of_Persion, ',') s
GROUP BY RTRIM(LTRIM(s.[value]))

Result (without rounding and formatting):

Person  Price
Nishant 100.000000000000
Nitish  125.000000000000
Rishi   125.000000000000

Upvotes: 2

Related Questions