Sql Help Kavali

[sri_sql]

Original Output :

 

      Payment Table    

 

  SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3  

    4   30 1/1/2014      30 1/1/2014 30 1/1/2014  

    5 10 1/2/2014 20 1/3/2014 30 1/4/2014  

    6 30 1/1/2014 40 1/2/2014 50 1/6/2014  

 

 

Expected Output:

 

     Payment Table

   

SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3

     4 90 1/1/2014 null 1/1/2014 null 1/1/2014

     5 10 1/2/2014 20 1/3/2014 30 1/4/2014

     6 30 1/1/2014 40 1/2/2014 50 1/6/2014

 

Oka Query rasanu kaani run avatledu:

 

Case When

PayDt1 = PayDt2 and

PayDt1 = PayDt2

Then PayAmt1 = PayAmt2 + PayAmt3

Else PayAmt1

 

Please Help....

[Pawan_Kalyan_7]

???  case statement work avtleda 

[Pawan_Kalyan_7]

put replace amount nulls with zero 0 

[sri_sql]

???  case statement work avtleda 

Problem endi antey Case statement run chestey Cannot insert null values into PayAmt1 and PayDt1 ani vastondi ...

 

Table Structure lo PayAmt1 and PayDt1 not null ani specify chesadu, So adi skip chesi rayali anukunta query 

[Pawan_Kalyan_7]

use isnull function in the query 

[sri_sql]

put replace amount nulls with zero 0 

Idhi oka verey parent table ninchi vastondi bhayya daanlto i cannot insert zeros 

[sri_sql]

use isnull function in the query 

Ok try chestha 

[Pawan_Kalyan_7]

Idhi oka verey parent table ninchi vastondi bhayya daanlto i cannot insert z

vere table nunchi tedaniki source query untadi kada ...

 

a source query lo use ISNULL(pmtamt1,0) ,ISNULL(pymtdt1,'default date')

[sri_sql]

vere table nunchi tedaniki source query untadi kada ...

 

a source query lo use ISNULL(pmtamt1,0) ,ISNULL(pymtdt1,'default date')

oh ok 

[sri_sql]

CREATE TABLE #Payments 

( SettlementID INT NOT NULL

, PaymentAmount1 MONEY NOT NULL 

, PaymentDate1 DATETIME NOT NULL

, PaymentAmount2 MONEY NULL

, PaymentDate2 DATETIME NULL

, PaymentAmount3 MONEY NULL

, PaymentDate3 DATETIME NULL

, NumberofPayments INT NOT NULL

)

 

INSERT INTO #Payments( SettlementID, PaymentAmount1, PaymentDate1, NumberofPayments)

SELECT SettlementID, PaymentAmount, PaymentIMDate, NumberofPayments

FROM SettlementAcctPayments SAP

WHERE PaymentNumber = 1

UPDATE #Payments

SET PaymentAmount2 = PaymentAmount

, PaymentDate2 = PaymentIMDate

FROM #Payments P

INNER JOIN AcctPayments SAP 

ON P.SettlementID = SAP.SettlementID

AND P.NumberofPayments = SAP.NumberofPayments

WHERE PaymentNumber = 2

UPDATE #Payments

SET PaymentAmount3 = PaymentAmount

, PaymentDate3 = PaymentIMDate

FROM #Payments P

INNER JOIN AcctPayments SAP 

ON P.SettlementID = SAP.SettlementID

AND P.NumberofPayments = SAP.NumberofPayments

WHERE PaymentNumber = 3

 

 

Original Output :

 

      Payment Table    

 

  SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3  

    4   30 1/1/2014      30 1/1/2014 30 1/1/2014  

    5 10 1/2/2014 20 1/3/2014 30 1/4/2014  

    6 30 1/1/2014 40 1/2/2014 50 1/6/2014  

 

 

Expected Output:

 

     Payment Table

   

SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3

     4 90 1/1/2014 null 1/1/2014 null 1/1/2014

     5 10 1/2/2014 20 1/3/2014 30 1/4/2014

     6 30 1/1/2014 40 1/2/2014 50 1/6/2014

 

[sri_sql]

Help...

[MegaPowerRockstar]

Is null vadethe work Ava ledha??

[sri_sql]

Is null vadethe work Ava ledha??

 

Actual Database ledu  bhayya na dagara..so try cheyaledu..just dummy data create chesi run chesanu daaniki run ayindi...but in realtime lo work avatledu

[ChampakDas]

edi 60 istundi

Original Output :

 

      Payment Table    

 

  SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3  

    4   30 1/1/2014      30 1/1/2014 30 1/1/2014  

    5 10 1/2/2014 20 1/3/2014 30 1/4/2014  

    6 30 1/1/2014 40 1/2/2014 50 1/6/2014  

 

 

Expected Output:

 

     Payment Table

   

SID PayAmt1 PayDt1 PayAmt2 PayDt2 PayAmt3 PayDt3

     4 90 1/1/2014 null 1/1/2014 null 1/1/2014

     5 10 1/2/2014 20 1/3/2014 30 1/4/2014

     6 30 1/1/2014 40 1/2/2014 50 1/6/2014

 

Oka Query rasanu kaani run avatledu:

 

Case When

PayDt1 = PayDt2 and

PayDt1 = PayDt2

Then PayAmt1 = PayAmt2 + PayAmt3

Else PayAmt1

 

Please Help....

 

 

[sri_sql]

edi 60 istundi

 

 

 

Sorry

The Whole Query is :

Case When

PayDt1 = PayDt2 and

PayDt1 = PayDt2

Then PayAmt1 + PayAmt2 + PayAmt3

When

PayDt1 = PayDt2 and

PayDt1 != PayDt3

Then

PayAmt1 + PayAmt2

When

PayDt1 != PayDt2 And

PayDt1 != PayDt1

Then

PayAmt1

End As PayAmt1