Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: Urgent Help required in procedure

    Goodday .

    i require your help forming this procedure.

    basically transections are of credit card for deals. The structure is as follows

    PreviousStatus NewStatus
    D =Declined
    A =Authorize Void
    A C =capture
    C R Refund void

    authorize can be captured( paid) or can be void
    capture later on can be refund (void)

    Code:
    select id,refid, orderid,previousstatus,newstatus from creditcardtransaction where id=114
    select id,refid, orderid,previousstatus,newstatus from creditcardtransaction where id=151
    select id,refid, orderid,previousstatus,newstatus from creditcardtransaction where id=221
    id refid orderid previousstatus newstatus
    ----------- ----------- ----------- -------------- ---------
    114 NULL 97717 NULL A

    (1 row(s) affected)

    id refid orderid previousstatus newstatus
    ----------- ----------- ----------- -------------- ---------
    151 114 97717 A C

    (1 row(s) affected)

    id refid orderid previousstatus newstatus
    ----------- ----------- ----------- -------------- ---------
    221 151 97717 C R


    i need the output in this form :

    ID AuthDate CaptureDate RefndDate Amount
    114 2010-12-17 21:25:00 2010-12-18 04:49:00 2010-12-27 04:40:00 10.00


    i can add dates if i can know how to do it . what i am stuck at is to go through the tree of finding related record on 3 lines ..

    id 144 become ref id on 2nd line .. and id of 2nd line become ref id on 3rd line .


    please help urgently caz my db is offline due to this small procedure

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    Help any one ??

    so far what i am trying is to collect all information in a temp table.
    and than select it .

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    Code:
    SELECT CCT.Id,
           CCT.RefId,
    CASE
    WHEN CCT.NewStatus = 'A' AND CCT.isvoid IS NULL THEN TransDate
    
    WHEN CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C
    	WHERE  CCT.refid = C.id AND C.NewStatus = 'A' AND isvoid IS NULL)
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    	WHERE  C.NewStatus = 'A' AND isvoid IS NULL)
             ELSE isnull(NULL,transdate)
           END AS authdate,
    CASE
    WHEN CCT.NewStatus = 'C' AND CCT.isvoid IS NULL THEN TransDate
    --when cct.previousstatus ='A' and newstatus='C' than transdate
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    	WHERE  CCT.refid = C.id AND C.NewStatus = 'C' AND isvoid IS NULL)
             ELSE isnull(NULL,transdate)
           END AS Capturedate,
    CASE
    WHEN CCT.NewStatus = 'R' AND CCT.isvoid IS NULL THEN TransDate
             ELSE isnull(NULL,transdate)
           END AS refunddate,
    CCT.amount,
    CASE isvoid
    WHEN NULL THEN NULL
    ELSE voiddate
    END AS voiddate
    FROM   CreditCardtransaCtion CCT
    WHERE CCT.ORDERID=97717

    Id RefId authdate Capturedate refunddate amount voiddate
    ----------- ----------- ----------------------- ----------------------- ----------------------- --------------------------------------- -----------------------
    114 NULL 2010-12-17 21:25:00 2010-12-17 21:25:00 2010-12-17 21:25:00 2.00 NULL
    151 114 2010-12-17 21:25:00 2010-12-18 04:49:00 2010-12-18 04:49:00 2.00 NULL
    221 151 2010-11-22 07:54:00 2010-12-18 04:49:00 2010-12-27 04:40:00 2.00 NULL



    but how can i modify it so refund date can appear in id 141

    means .. if i query any id 141,151, or 221 i will be able to see auth , capture and refund dates .

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    Code:
    SELECT Id,
           RefId,
    CASE
    WHEN CCT.NewStatus = 'A' AND CCT.isvoid IS NULL THEN TransDate
    
    
    WHEN CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C
        WHERE  CCT.refid = C.id AND C.NewStatus = 'A' AND isvoid IS NULL)
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (SELECT TOP 1 c.TransDate FROM   CreditCardtransaCtion c
        WHERE  C.NewStatus = 'A'  And cct.refid=c.id  and cct.isvoid IS NULL)
             ELSE null
           END AS authdate,
    CASE
    
    When cct.previousstatus is null and newstatus='A' and cct.isvoid is null then
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
        WHERE  CCT.id =C.refid AND C.NewStatus = 'C' AND isvoid IS NULL) 
    
    
    
    WHEN CCT.NewStatus = 'C' AND CCT.isvoid IS NULL THEN TransDate
    when cct.previousstatus is null and newstatus='C' then transdate
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
        WHERE  CCT.refid = C.id AND C.NewStatus = 'C' AND isvoid IS NULL)
             ELSE null
           END AS Capturedate,
    CASE
    when cct.newstatus ='A' and previousstatus='C' and isvoid is null then
    (SELECT TOP 1 c.TransDate FROM   CreditCardtransaCtion C 
        WHERE  C.NewStatus = 'R' and cct.refid=c.id AND isvoid IS NULL)
    
    WHEN CCT.NewStatus = 'R' AND previousstatus='c' and CCT.isvoid IS NULL THEN  transdate
    
    
             ELSE null
           END AS refunddate,
    CCT.amount,previousstatus,newstatus,
    CASE isvoid
    WHEN NULL THEN NULL
    ELSE voiddate
    END AS voiddate
    FROM   CreditCardtransaCtion CCT
    WHERE CCT.ORDERID=97717

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    Code:
    SELECT Id,
           RefId,
    CASE
    WHEN CCT.NewStatus = 'A' AND CCT.isvoid IS NULL THEN TransDate
    
    
    WHEN CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C
    	WHERE  CCT.refid = C.id AND C.NewStatus = 'A' AND isvoid IS NULL)
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (select transdate from CreditCardtransaCtion  where id=(Select c.Refid from CreditCardtransaCtion c where c.id=(select c.id from CreditCardtransaCtion c where cct.refid=c.id)))
    
           END AS authdate,
    CASE
    
    When cct.previousstatus is null and newstatus='A' and cct.isvoid is null then
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    	WHERE  CCT.id =C.refid AND C.NewStatus = 'C' AND isvoid IS NULL) 
    
    
    
    WHEN CCT.NewStatus = 'C' AND CCT.isvoid IS NULL THEN TransDate
    when cct.previousstatus is null and newstatus='C' then transdate
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
    (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
    	WHERE  CCT.refid = C.id AND C.NewStatus = 'C' AND isvoid IS NULL)
             ELSE null
           END AS Capturedate,
    CASE
    when cct.newstatus ='A' and cct.previousstatus is null and cct.isvoid is null then
    (select transdate from CreditCardtransaCtion  where id=(Select c.Refid from CreditCardtransaCtion c where c.id=(select c.id from CreditCardtransaCtion c where cct.refid=c.id)))
    
    
    when cct.newstatus ='C' and cct.previousstatus ='A' and cct.isvoid is null then
    (SELECT top 1 c.TransDate FROM   CreditCardtransaCtion C 
    	WHERE  C.NewStatus = 'R' and c.refid=cct.id AND isvoid IS NULL)
    
    WHEN CCT.NewStatus = 'R' AND previousstatus='C' and CCT.isvoid IS NULL THEN  transdate
    
    
             ELSE null
           END AS refunddate,
    CCT.amount,previousstatus,newstatus,
    CASE isvoid
    WHEN NULL THEN NULL
    ELSE voiddate
    END AS voiddate
    FROM   CreditCardtransaCtion CCT
    WHERE CCT.ORDERID=97717
    Code:
    114	NULL	2010-12-17 21:25:00	2010-12-18 04:49:00	NULL	2.00	NULL	A	NULL
    151	114	2010-12-17 21:25:00	2010-12-18 04:49:00	2010-12-27 04:40:00	2.00	A	C	NULL
    221	151	2010-12-17 21:25:00	2010-12-18 04:49:00	2010-12-27 04:40:00	2.00	C	R	NULL
    Last edited by mishaalsy; 12-30-10 at 09:01.

  6. #6
    Join Date
    Aug 2009
    Posts
    262

    Thank you thank you every one

    i am so much great full to every one for their response .. without u all i couldnt have done it .. yea u did remind me that every arse is for himself only
    Code:
    select id,CASE WHEN CCT.NewStatus = 'A' AND CCT.isvoid IS NULL THEN TransDate
         WHEN CCT.PreviousStatus = 'A' AND CCT.NewStatus = 'C' THEN 
                (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C
                    WHERE  CCT.refid = C.id AND C.NewStatus = 'A' AND isvoid IS NULL)
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
                (select transdate from CreditCardtransaCtion  where id=
                    (Select c.Refid from CreditCardtransaCtion c where c.id=
                        (select c.id from CreditCardtransaCtion c where cct.refid=c.id)))
    END AS AuthDate,
    
    CASE When cct.previousstatus is null and newstatus='A' and cct.isvoid is null then
                (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
                    WHERE  CCT.id =C.refid AND C.NewStatus = 'C' AND isvoid IS NULL) 
    
    
    
    WHEN CCT.NewStatus = 'C' AND CCT.isvoid IS NULL THEN TransDate
    WHEN cct.previousstatus is null and newstatus='C' then transdate
    
    WHEN CCT.PreviousStatus = 'C' AND CCT.NewStatus = 'R' THEN 
                (SELECT TOP 1 C.TransDate FROM   CreditCardtransaCtion C 
                    WHERE  CCT.refid = C.id AND C.NewStatus = 'C' AND isvoid IS NULL)
                    ELSE null
    END AS CaptureDate,
    
    CASE WHEN cct.newstatus ='A' and cct.previousstatus is null and cct.isvoid is null then
                (select transdate from CreditCardtransaCtion where id=( 
                    select c.id from CreditCardtransaCtion c where c.refid=(
                        select refid from CreditCardtransaCtion c where refid=
                         (select c.id from CreditCardtransaCtion c
                            where c.refid = cct.id))))
    
    WHEN cct.newstatus ='C' and cct.previousstatus ='A' and cct.isvoid is null then
                (SELECT top 1 c.TransDate FROM   CreditCardtransaCtion C 
                    WHERE  C.NewStatus = 'R' and c.refid=cct.id AND isvoid IS NULL)
    
    WHEN CCT.NewStatus = 'R' AND previousstatus='C' and CCT.isvoid IS NULL THEN  transdate
             ELSE null
    END AS RefundDate,VoidDate
    
    FROM   CreditCardtransaCtion CCT
    --where  CCT.OrderID=O.OrderID 
    --And CCT.NewStatus=@TransType   
    
    --and cct.isvoid is  null
    
    order by cct.TransDate desc
    Last edited by mishaalsy; 12-30-10 at 09:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •