# Need to derive a Calculated column in SQL server

• ### Question

• Hi,

Can anyone pls help me on the below task.

I have four different tables. I am joining the those tables.

ex: select T1.col1,T2.col2,   ________

from table1 T1 left join Table T2 on T1.Id =T2.Id

left join Table T3 on T2.accnum =T3.accnum

left join Table T4 on T3.code =T4.code

Now I need one calculated column. Below is the requirement:

(T1.savingdeposit * sum(T2.amount) where T2.accnum ='50001') /100 + T3.currentdeposit * sum(T2.amount) where T2.accnum ='50002')/100

sum(T2.amount) where T2.accnum ='50003' and (T4.maturitydate - T3.businessdate) / 365 <= 1

+

(T4.duetoLLc *  sum(GLAmount) where T2.accnum in  ('50004','50005','50006') / 100

All this above calculation gives a single value.

Please suggest a SQL query that makes all the above calculations in best way.

Thanks

Kritika

• Moved by Wednesday, August 5, 2020 7:16 PM Query question
Wednesday, August 5, 2020 5:54 PM

### All replies

• Hi,

For this type of question it may help if you provide input data and desired output.

So please post some representative data for all your tables and the desired output based on the presented input if you want some help.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Wednesday, August 5, 2020 7:20 PM
• For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Furthermore, it helps to have a short description of the business rules whih explains why you want that result.

Finally, don't forget to include which version of SQL Server you are working on.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Wednesday, August 5, 2020 9:16 PM
• T2.Accnum column is varchar(10) and all other columns used in calculations are of numeric type.

My expected output is of numeric type. For example 675000000.00

Thursday, August 6, 2020 4:22 AM
• Hi Kritika,

Otherwise please provide more details as mentioned by above experts. For example, create table DDL of four different tables, some sample data, actual query and expected output.

This permits us to copy and paste into a query window to develop a tested query.

Best Wishes

Melissa

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Thursday, August 6, 2020 6:22 AM
• This didn't help me to see what exactly do you need. As I asked, please post some input data in your tables and desired output based on the input along with the business rules.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, August 6, 2020 2:19 PM
• sure Naomi. I will brief my task from beginning.

I have two tables. As per my Business I need to  calculate ratio from these tables.

 Table1(All are decimal  datatype): Current_Deposit Saving_Deposit Foreign_Due LCCF 25000.00 40000.00 30000.00 40000.00

Table 2 alias as T2 : (Datatypes:- Amount-Decimal,AccountNumber-Varchar(10),Startdate-Date,MaturityDate-Date,BusinessDate-Date,categorycode -Varchar(10))

 Amount Account_number start_date Maturity_date Business_date Category_code 1000.00 IN10030 07-08-2000 07-08-2001 16-02-2001 A5001 2000.00 IN10031 08-08-2000 08-08-2001 17-02-2001 A5002 3000.00 IN10032 09-08-2000 09-08-2001 18-02-2001 A5003 4000.00 IN10033 10-08-2000 10-08-2001 19-02-2001 A5004 5000.00 IN10034 11-08-2000 11-08-2001 20-02-2001 A5005 6000.00 IN10035 12-08-2000 12-08-2001 21-02-2001 A5006 7000.00 IN10036 13-08-2000 13-08-2001 22-02-2001 A5007 8000.00 IN10037 14-08-2000 14-08-2001 23-02-2001 A5008 9000.00 IN10038 15-08-2000 15-08-2001 24-02-2001 A5009 10000.00 IN10039 16-08-2000 16-08-2001 25-02-2001 A5001 11000.00 IN10040 17-08-2000 17-08-2001 26-02-2001 A5001 12000.00 IN10041 18-08-2000 18-08-2001 27-02-2001 A5001 13000.00 IN10042 19-08-2000 19-08-2001 28-02-2001 A5001

Expected output for example : 650000.00

Now I need follow the series of below logic to calculate the expected output. Please help me with the SQL code to implement the below logic.

(T1.current_Deposit * sum(T2.Amount) where T2.Account_Number = 'IN10030') / 100 + (T1.Saving_Deposit * sum(T2.Amount) where T2.Account_Number = 'IN10031') / 100

sum(T2.Amount) where T2.Account_Number = 'IN10032' and (T2.Maturity_Date - T2.Business_Date) / 365 <= 1

+

(T1.saving_Deposit * sum(T2.Amount) where T2.Account_Number in ( 'IN10033','IN10034') or ( T2.Account_Number = 'IN10035' or T2.Category_code = 'A5001')) / 100

+

(T1.LCCF * sum(T2.Amount) where T2.Account_Number = 'IN10036') / 100

+

sum(T2.Amount) where T2.Account_Number = 'IN10037' and T2.Amount > 0 and (T2.Maturity_Date  - T2.Business_Date) /365 < = 1 and T2.Start_Date  <= T2.Business_Date

Please feel free to let me know if you need any inputs.

Thanks

kritika

• Edited by Thursday, August 6, 2020 7:55 PM
Thursday, August 6, 2020 7:46 PM
• So it is a bit tricky and most likely we may try to do something like this:

;with cteTotals as (select sum(case when AccountNumber = ''IN10030' else 0 end) as FirstAmount, sum(case when ...) as SecondAmount, etc. from Table2)

select ...

from Table1 T1, cteTotals

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

In other words, in the top cteTotals get the sums using the conditions for each of the sum as you need and shown and then do your main select from the first table and this cteTotals and apply your formula using these new calculated columns which you may want to name the way you can understand them (you can add account name as the end of Amount so it will be easy to understand what is it).

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Thursday, August 6, 2020 8:13 PM
• Thanks a lot Naomi. I think this would work.

Thursday, August 6, 2020 8:57 PM
• Hi Kritika,

```drop table if exists #table1
drop table if exists #table2

create table #table1
(
Current_Deposit decimal(10,2),
Saving_Deposit decimal(10,2),
Foreign_Due decimal(10,2),
LCCF decimal(10,2),
)

insert into  #table1 values
(25000.00,40000.00,30000.00,40000.00)

select * from #table1

create table #table2
(
Amount decimal(10,2),
AccountNumber Varchar(10),
Startdate Date,
MaturityDate Date,
categorycode Varchar(10)
)

insert into #table2 values
(	1000.00	,	'IN10030',	convert(date,	'07-08-2000',	105),	convert(date,	'07-08-2001',	105),	convert(date,	'16-02-2001',	105),	'A5001'),
(	2000.00	,	'IN10031',	convert(date,	'08-08-2000',	105),	convert(date,	'08-08-2001',	105),	convert(date,	'17-02-2001',	105),	'A5002'),
(	3000.00	,	'IN10032',	convert(date,	'09-08-2000',	105),	convert(date,	'09-08-2001',	105),	convert(date,	'18-02-2001',	105),	'A5003'),
(	4000.00	,	'IN10033',	convert(date,	'10-08-2000',	105),	convert(date,	'10-08-2001',	105),	convert(date,	'19-02-2001',	105),	'A5004'),
(	5000.00	,	'IN10034',	convert(date,	'11-08-2000',	105),	convert(date,	'11-08-2001',	105),	convert(date,	'20-02-2001',	105),	'A5005'),
(	6000.00	,	'IN10035',	convert(date,	'12-08-2000',	105),	convert(date,	'12-08-2001',	105),	convert(date,	'21-02-2001',	105),	'A5006'),
(	7000.00	,	'IN10036',	convert(date,	'13-08-2000',	105),	convert(date,	'13-08-2001',	105),	convert(date,	'22-02-2001',	105),	'A5007'),
(	8000.00	,	'IN10037',	convert(date,	'14-08-2000',	105),	convert(date,	'14-08-2001',	105),	convert(date,	'23-02-2001',	105),	'A5008'),
(	9000.00	,	'IN10038',	convert(date,	'15-08-2000',	105),	convert(date,	'15-08-2001',	105),	convert(date,	'24-02-2001',	105),	'A5009'),
(	10000.00,	'IN10039',	convert(date,	'16-08-2000',	105),	convert(date,	'16-08-2001',	105),	convert(date,	'25-02-2001',	105),	'A5001'),
(	11000.00,	'IN10040',	convert(date,	'17-08-2000',	105),	convert(date,	'17-08-2001',	105),	convert(date,	'26-02-2001',	105),	'A5001'),
(	12000.00,	'IN10041',	convert(date,	'18-08-2000',	105),	convert(date,	'18-08-2001',	105),	convert(date,	'27-02-2001',	105),	'A5001'),
(	13000.00,	'IN10042',	convert(date,	'19-08-2000',	105),	convert(date,	'19-08-2001',	105),	convert(date,	'28-02-2001',	105),	'A5001')

;with cteTotals as (
select sum(case when AccountNumber = 'IN10030' then Amount else 0 end) as A1
,sum(case when AccountNumber = 'IN10031' then Amount else 0 end) as A2
,sum(case when AccountNumber = 'IN10032' and DATEDIFF(day, MaturityDate,BusinessDate) <=365 then Amount else 0 end) as A3
,sum(case when AccountNumber in ('IN10033','IN10034') or ( AccountNumber = 'IN10035' or categorycode = 'A5001') then Amount else 0 end) as A4
,sum(case when AccountNumber = 'IN10036' then Amount else 0 end)  as A5
,sum(case when AccountNumber = 'IN10037' and Amount > 0 and DATEDIFF(day, MaturityDate,BusinessDate) <=365 and Startdate<=BusinessDate then Amount else 0 end)  as A6
from #Table2
)

select Current_Deposit*A1/100
+Saving_Deposit*A2/100
+A3
+Saving_Deposit*A4/100
+LCCF*A5/100
+A6
from cteTotals a,#table1 b```

Wishes

Melissa

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !