Sql Help Kavali

[sri_sql]

 

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

 

 

 Idhi work chesindi dummy data tho,but Null values insertion realtime data lo error throw chesthundi anukunta:

 

SELECT
     SID
    ,CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN PayAmt1 + PayAmt2 + PayAmt3 ELSE PayAmt1 END AS PayAmt1
    , CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN NULL Else PayAmt2 END AS PayAmt2
    , CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN NULL Else PayAmt3 END AS PayAmt3
FROM #Payment;

[ChampakDas]

ado okati avutundi dude...rendu kavu

aa dummy data create table insert statements evu nenu try chesta

 Idhi work chesindi dummy data tho,but Null values insertion realtime data lo error throw chesthundi anukunta:

 

SELECT
     SID
    ,CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN PayAmt1 + PayAmt2 + PayAmt3 ELSE PayAmt1 END AS PayAmt1
    , CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN NULL Else PayAmt2 END AS PayAmt2
    , CASE
        WHEN PayDt1 = PayDt2 AND PayDt2 = PayDt3 THEN NULL Else PayAmt3 END AS PayAmt3
FROM #Payment;

 

[loveindia]

direct data load appudey conditions petti alaney insert cheyocchu ga if it is ur requirement. or you just want to see data like that in a select query??

 

[c_how_dare]

Ltt

[sri_sql]

ado okati avutundi dude...rendu kavu

aa dummy data create table insert statements evu nenu try chesta

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

)

 

Insert into #Payments 

SELECT 4 ,30, '1/1/2014',30, '1/1/2014', 30, '1/1/2014'  

UNION ALL

SELECT  5 ,30, '1/2/2014',30, '1/3/2014', 30, '1/4/2014'  

UNION ALL

SELECT 6 ,10, '1/1/2014',30, '1/2/2014', 30, '1/3/2014' 

[sri_sql]

direct data load appudey conditions petti alaney insert cheyocchu ga if it is ur requirement. or you just want to see data like that in a select query??

Data Load already chesadu bhayya..

 

Ipudu new condition pettadu marcahamani...Payments unna specific date adhey column lo ravali ani vachindi 

[loveindia]

select SettlementID
    , CASE WHEN (PaymentDate1 = PaymentDate2) AND (PaymentDate2 = PaymentDate3) THEN PaymentAmount1+PaymentAmount2+PaymentAmount3
           WHEN (PaymentDate1 = PaymentDate2) THEN PaymentAmount1+PaymentAmount2
           ELSE PaymentAmount1 END AS payAmt1
    , PaymentDate1
    , CASE WHEN (PaymentDate1 = PaymentDate2) AND (PaymentDate2 = PaymentDate3) THEN NULL
           ELSE PaymentAmount1 END AS payAmt2
    , PaymentDate2
    , CASE WHEN (PaymentDate1 = PaymentDate2) AND (PaymentDate2 = PaymentDate3) THEN NULL
           ELSE PaymentAmount1 END AS payAmt3           
    , PaymentDate3
from #payments

[loveindia]

If you have more conditons you can add to the query I gave you...

[ChampakDas]

looks good..only data problem kanipistundi any of the column value may be NULL...just for the heck of it to prove it is not real data issue run below query and tell me the result

 

SELECT
     SettlementID
    ,CASE
        WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN ISNULL(PaymentAmount1,0) + ISNULL(PaymentAmount2,0) + ISNULL(PaymentAmount3,0) ELSE PaymentAmount1 END AS PaymentAmount1
    , CASE
        WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN NULL Else PaymentAmount2 END AS PaymentAmount2
    , CASE
        WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN NULL Else PaymentAmount3 END AS PaymentAmount3
FROM Payments;

 

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

)

 

Insert into #Payments 

SELECT 4 ,30, '1/1/2014',30, '1/1/2014', 30, '1/1/2014'  

UNION ALL

SELECT  5 ,30, '1/2/2014',30, '1/3/2014', 30, '1/4/2014'  

UNION ALL

SELECT 6 ,10, '1/1/2014',30, '1/2/2014', 30, '1/3/2014' 

 

 

[sri_sql]

If you have more conditons you can add to the query I gave you...

I will try this query with real data bhayya

[sri_sql]

looks good..only data problem kanipistundi any of the column value may be NULL...just for the heck of it to prove it is not real data issue run below query and tell me the result

SELECT
SettlementID
,CASE
WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN ISNULL(PaymentAmount1,0) + ISNULL(PaymentAmount2,0) + ISNULL(PaymentAmount3,0) ELSE PaymentAmount1 END AS PaymentAmount1
, CASE
WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN NULL Else PaymentAmount2 END AS PaymentAmount2
, CASE
WHEN PaymentDate1 = PaymentDate2 AND PaymentDate2 = PaymentDate3 THEN NULL Else PaymentAmount3 END AS PaymentAmount3
FROM Payments;

I will try this query with real data and let u know bhayya

[loveindia]

inputs enti... outputs enti ala cheppu man... this way it is very difficult... sample data untey really easy....