Calculate Record Row by Row and some calculation SQL Server

Question

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

@Zhorov 200 will divided into 3 part but in last calculation Nits will get 66.66 * 2 = 133 from Dish and Bit — Nits Patel
– Nits Patel, Commented Mar 9, 2020 at 9:35

Zhorov · Accepted Answer · 2020-03-09 11:09:09Z

2

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

edited Mar 9, 2020 at 11:09

answered Mar 9, 2020 at 9:34

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Nits Patel Over a year ago

its showing 'Error converting data type nvarchar to numeric' i thing i need to convert data type?

Nits Patel Over a year ago

CREATE TABLE [dbo].[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 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Nits Patel Over a year ago

thanks a lot its working as i want.. thank u so much

Nits Patel Over a year ago

if i am inserting two times same name its showing two records but it should be one record with total price why?

Nits Patel Over a year ago

i am asking another question as i want its going too long so but as of now i get my output

|

Collectives™ on Stack Overflow

Calculate Record Row by Row and some calculation SQL Server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related