locked
Inserting Values into a table RRS feed

  • Question

  • Hello All,

    I have an issue when inserting the data into a table. I have 2 fields(Invoice generated, Payments) in my table. I have to calculate values for those 2 fields and insert into a table.

    The calculation for invoice generated is something like this

    select  SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment'

             where TYPE_DESC != 'Level Pay Contract'

             and type_desc != 'Transfer to Payment Contract'

             and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',

                                               'Returned Cash - Revenue - Non Regulated')

     

     and calculation for payments will be 

    select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments'

    from billing_payment

    where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null

    and CURRENT_STMT_DATE is null))

     

    Since both have different filter conditions how can I insert these 2 values in a table.I can use insert into select but I am not getting how to give the where conditions for both of the values. Please assist.

    Thanks

     

    Thursday, December 8, 2011 6:21 PM

Answers

  • You can store result in a variable:

    DECLARE @invoice INT
    DECLARE @Payment INT

    select  @invoice=SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment'

             where TYPE_DESC != 'Level Pay Contract'

             and type_desc != 'Transfer to Payment Contract'

             and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',

                                               'Returned Cash - Revenue - Non Regulated')

    select @Payment=SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments'

    from billing_payment

    where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null

    and CURRENT_STMT_DATE is null))

     

    INSERT INTO TableName
    SELECT @invoice,@Payment


    Shatrughna.
    • Marked as answer by Kalman Toth Wednesday, December 14, 2011 9:41 PM
    Thursday, December 8, 2011 6:28 PM
  • INSERT INTO TABLE (

    TOTALAMOUNT (Datatype))

    select  SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment'

             where TYPE_DESC != 'Level Pay Contract'

             and type_desc != 'Transfer to Payment Contract'

             and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',

                                               'Returned Cash - Revenue - Non Regulated')

    UNION 

    select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments'

    from billing_payment

    where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null

    and CURRENT_STMT_DATE is null))

    • Proposed as answer by Red8Rain Thursday, December 8, 2011 7:16 PM
    • Marked as answer by Kalman Toth Wednesday, December 14, 2011 9:40 PM
    Thursday, December 8, 2011 6:28 PM
  • Hi,

    Declare @Result1  Int

    Declare @Result2  int

    --//storing 1st value in a varaible

    SELECT @Result1= SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment' 

    where TYPE_DESC != 'Level Pay Contract'

    and type_desc != 'Transfer to Payment Contract' and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated', 'Returned Cash - Revenue - Non Regulated')

     

    --//storing 2nd value in a varaible

    SELECT @Result2=SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments' from billing_payment where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null and CURRENT_STMT_DATE is null))

     

    --Now inserting in a table

    INSERT INTO tableName (nvoicegenerated, Payments) VALUES(@Result1,@Result2)

     


    PS.Shakeer Hussain
    Thursday, December 8, 2011 6:43 PM

All replies

  • You can store result in a variable:

    DECLARE @invoice INT
    DECLARE @Payment INT

    select  @invoice=SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment'

             where TYPE_DESC != 'Level Pay Contract'

             and type_desc != 'Transfer to Payment Contract'

             and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',

                                               'Returned Cash - Revenue - Non Regulated')

    select @Payment=SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments'

    from billing_payment

    where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null

    and CURRENT_STMT_DATE is null))

     

    INSERT INTO TableName
    SELECT @invoice,@Payment


    Shatrughna.
    • Marked as answer by Kalman Toth Wednesday, December 14, 2011 9:41 PM
    Thursday, December 8, 2011 6:28 PM
  • INSERT INTO TABLE (

    TOTALAMOUNT (Datatype))

    select  SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment'

             where TYPE_DESC != 'Level Pay Contract'

             and type_desc != 'Transfer to Payment Contract'

             and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated',

                                               'Returned Cash - Revenue - Non Regulated')

    UNION 

    select SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments'

    from billing_payment

    where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null

    and CURRENT_STMT_DATE is null))

    • Proposed as answer by Red8Rain Thursday, December 8, 2011 7:16 PM
    • Marked as answer by Kalman Toth Wednesday, December 14, 2011 9:40 PM
    Thursday, December 8, 2011 6:28 PM
  • Hi,

    Declare @Result1  Int

    Declare @Result2  int

    --//storing 1st value in a varaible

    SELECT @Result1= SUM(CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT) 

             from billing_payment

           --  where TYPE_DESC != 'Payment' 

    where TYPE_DESC != 'Level Pay Contract'

    and type_desc != 'Transfer to Payment Contract' and TRANSACTION_TYPE_DESC not in ('Reapplied Cash - Revenue - Non Regulated', 'Returned Cash - Revenue - Non Regulated')

     

    --//storing 2nd value in a varaible

    SELECT @Result2=SUM( CALC_PRICE_AMT + price_tax_amt + ADJUSTMENT_AMT)  'unclaimed Payments' from billing_payment where (customer_tkn = 42372942

    and CUST_STATEMENT_TKN is null and CURRENT_STMT_DATE is null))

     

    --Now inserting in a table

    INSERT INTO tableName (nvoicegenerated, Payments) VALUES(@Result1,@Result2)

     


    PS.Shakeer Hussain
    Thursday, December 8, 2011 6:43 PM
  •  

    Thanks so much shatrugna and Tarakq and Shakeer.

    I tried variables approach and it worked.  What if I have a query which defines multiple fields?. Let me explain more clearly 

     

    I need to insert totally 15 columns.Out of those 7 columns are inserted and they are direct select queries and I have defined parameters and used those parameters in insert into select. Issue with the rest of 8 columns is all those 8 columns are defined in one single sql query like this

     

    select 

    -- UNSTATMENTED

    SUM(fff.act_uns) + SUM(fff.inact_uns) as tot_unstat,

    SUM(fff.act_uns) as active_unstat,

    SUM(fff.inact_uns) as inactive_unstat,

    -- NOT DUE

    SUM(fff.act_notdue) + SUM(fff.inact_notdue) as tot_notdue,

    SUM(fff.act_notdue) as active_notdue,

    SUM(fff.inact_notdue) as inactive_notdue,

    -- 1 to 10

    SUM(fff.act_1_10) + SUM(fff.inact_1_10) as tot_1_10, 

    from (

    select 

    case when agr.status = 'ACTIVE' then agr.Unstatemented -- unstatement

      else 0

        end act_uns,

      case when agr.status = 'INACTIVE' then agr.Unstatemented

      else 0

        end inact_uns,

      case when agr.status = 'ACTIVE' then  agr.current_acct -- not due

      else 0

        end act_notdue,

      case when agr.status = 'INACTIVE' then agr.current_acct

      else 0

        end inact_notdue,

      case when agr.status = 'ACTIVE' then agr.aged_1_10

      else 0

        end act_1_10,

      case when agr.status = 'INACTIVE' then agr.aged_1_10

      else 0

        end inact_1_10,

    case when agr.status = 'ACTIVE' then agr.aged_11_30

    from (

    SELECT x.account_number,

           x.status,

                    SUM(CASE WHEN x.Arrears_Date IS NULL THEN x.Charge_Amt ELSE 0 END) Unstatemented,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date < 17 THEN x.Charge_Amt ELSE 0 END) current_acct,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date BETWEEN 17 AND 26 THEN x.Charge_Amt ELSE 0 END) AS aged_1_10,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date BETWEEN 27 AND 46 THEN x.Charge_Amt ELSE 0 END) AS aged_11_30,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date BETWEEN 47 AND 76 THEN x.Charge_Amt ELSE 0 END) AS aged_31_60,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date BETWEEN 77 AND 106 THEN x.Charge_Amt ELSE 0 END) AS aged_61_90,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date BETWEEN 107 AND 136 THEN x.Charge_Amt ELSE 0 END) AS aged_91_120,

                    SUM(CASE WHEN CAST('04-DEC-2011' AS DATE) - x.Arrears_Date > 137 THEN x.Charge_Amt ELSE 0 END) AS aged_121plus,

                    SUM(x.charge_amt) as tot

    FROM ( 

    select ea.ACCOUNT_NUMBER,

             ea.current_stmt_date as Arrears_Date,

             ea.line_total as charge_amt,

             ea.flow_start_date as CONFIRMED_START_DATE,

             ea.flow_end_date as CONFIRMED_DROP_DATE,

            case

             when ea.flow_START_DATE is not null and ea.flow_end_DATE IS null then 'ACTIVE'

             else 'INACTIVE'

            end status 

    from rlk_test.dbo.esg_ecp_data ea

    join (select --balance bue by account

                 x.account_number,

                             x.Charge_Amt + x.payment_amt balance

                        from (

                          select bc.account_number

                                 ,SUM(case when bc.type_Desc != 'Payment' then bc.Calc_Price_Amt + bc.Price_Tax_Amt + bc.Adjustment_Amt

                                           else 0

                                      end ) Charge_Amt 

                                     ,sum(case when bc.type_Desc = 'Payment'   then bc.adjustment_amt

                                               else 0

                                           end) payment_amt

                               from dbo.esg_billing_charge  bc 

                               group by bc.account_number ) x

                        where x.Charge_Amt + x.payment_amt > 0.01) deadbeat

                          on deadbeat.account_number = ea.account_number)x

    GROUP BY x.ACCOUNT_NUMBER,x.status )agr) fff

     

    How to seperate these values and insert into a table?...Please assist.

     

    Thursday, December 8, 2011 10:37 PM