query for adding the word match to the status table based on matching set of criteria

• raven4life

  Grasshopper

  Points: 12

  More actions

  January 28, 2008 at 7:35 am

  #181267

  I have a table ca with the following fields

  seq,cardno,debitamt ,postdate , creditamt, status

  So the table contains data like the following

  seq------cardno--------debitamt-----postdate-------creditamt------status

  1--------101----------0-------------1/1/08--------10

  2---------102----------10 ----------2/1/08---------0

  3---------103----------50----------2/1/08---------0

  4----------104----------6-----------2/1/08---------0

  5---------102----------0-----------3/1/08---------10

  6---------103-----------0-----------3/1/08---------50

  THe status will be updates to matched or unmatched

  This what my table looks like

  so what i want to do was to find all the debit that matched with all the credit for a cardno , and add the text message matched for all the same , as well as unmatched for tht ones that werent matched

  I spliited up the table two new allcredit and alldebit

  using

  The following code for

  SELECT Cardno, POST_DATE, DebitAmt, INTO AllDebit

  FROM ca

  WHERE (((ca.DebitAmt)<>0))

  ORDER BYCardno, POST_DATE, DebitAmt;

  then I used the following inner join to find the one that are matched

  SELECT AllCredit.Cardno, AllDebit.debitamt, AllDebit.POST_DATE AS DR_POST_DATE, AllCredit.POST_DATE AS CR_POST_DATE, AllCredit.creditamt, INTO matched

  FROM AllCredit INNER JOIN AllDebit ON (AllCredit.creditamt=AllDebit.debitamt) AND (AllCredit.Cardno=AllDebit.Cardno);

  NOW my question really revloves on the fact how to get a new table where

  that when there is a matching record for the coresponding debit and credit the , the rows must be added with a field matched

  the result will look like this

  cardno--------debitamt-----postdate-------creditamt-----status

  101----------0-------------1/1/08--------10-------------unmatched

  102----------10 ----------2/1/08---------0---------------matched

  103----------50----------2/1/08---------0---------------matched

  104----------6-----------2/1/08---------0-------------unmatched

  102----------0-----------3/1/08---------10---------------matched

  103-----------0-----------3/1/08---------50---------------matched

  Please do help me out , i some how cant getit

• Susantha Bathige

  Ten Centuries

  Points: 1040

  More actions

  February 1, 2008 at 3:49 am

  #774116

  You can use the following query to update status field with 'matched'.

  Table tab1 contains the sample data you have given.

  update dbo.tab1 set [status]='matched'

  from

  (select cardno,sum(debitamt) as dra,sum(creditamt) cra, case when sum(debitamt)= sum(creditamt) then 1 else 0 end as ismatch from tab1

  group by cardno) a

  where a.ismatch=1 and a.cardno=tab1.cardno

  Susantha