locked
Need to derive a Calculated column in SQL server RRS feed

  • 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 Naomi N 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,

    Please try to use CAST(T2.Accnum AS DECIMAL(10, 2)) instead of T2.Accnum in your calculations.

    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 !
    For more information, please refer to the sticky post.


    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 Kritika256 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.

    I will mark as answer once I complete the task. 

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

    Please refer below example and modify your query accordingly.

    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,
    BusinessDate 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 !
    For more information, please refer to the sticky post.

    Friday, August 7, 2020 5:38 AM