Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

How to update a rows total based on related transaction file in Ms SQL

We need to update a series of purchased applying all their respective transactions.  For example, a purchase invoice of $21.25, applying all credit memos (total of 9.95) and debit memo (of $20.00) updating the purchase invoice to $31.30.  The credit memo reduces and the debit memos increases the value of the purchase invoice.  Purchase table has a column that its total products purchase is saved and a column for the updated value.

The Purchase Tables looks as follow:
PurchNo PurchDate Vendor TotalPurch	TotalPurchUpdt
Purch1  1/1/2013  IBM    21.25          0.00
Purch2  2/28/2013 TWA    29.55          0.00

When running the script, it should display:
PurchNo PurchDate Vendor TotalPurch	TotalPurchUpdt
Purch1  1/1/2013  IBM    21.25          31.30
Purch2  2/28/2013 TWA    29.55          16.50

Open in new window

0
rayluvs
Asked:
rayluvs
  • 5
1 Solution
 
rayluvsAuthor Commented:
so far the script in work:

SELECT p.PurchNo, p.TotalPurch, p.TotalPurchUpdt,
 (SELECT * FROM @TransDetail t WHERE t.Prod=pd.prod)
 from @PurchHeader p 
 INNER JOIN @PurchDetail pd ON p.PurchNo=pd.PurchNo

Open in new window


The following are virtual tables provided for more better helping us:

/**************************************************
* Script to get the total of items based on
* the type of documento.  That is if is a Credit
* memo, Debit memos, Invoice, etc.
**************************************************/

-- Create Virtual table
   DECLARE @PurchHeader TABLE(
           PurchNo VARCHAR(20),
           PurchDate DATE,
           Vendor VARCHAR(20),
           TotalPurch FLOAT,
           TotalPurchUpdt FLOAT)
   DECLARE @PurchDetail TABLE(
           PurchNo VARCHAR(20),
           Prod VARCHAR(20),
           Qty INT,
           Cost FLOAT)
   DECLARE @TransHeader TABLE(
           TransNo VARCHAR(20),
           TransType VARCHAR(20),
           TransDate DATE,
           PurchHeaderNo VARCHAR(20))
   DECLARE @TransDetail TABLE(
           TransNo VARCHAR(20),
           Prod VARCHAR(20),
           Qty INT,
           Cost FLOAT)

-- Insert Test Data
   INSERT INTO @PurchHeader SELECT 'Purch1','1/1/2013','IBM',21.25,0
   INSERT INTO @PurchHeader SELECT 'Purch2','2/28/2013','TWA',29.55,0
   INSERT INTO @PurchDetail SELECT 'Purch1','Prod1',5,4.25 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod2',2,10.15 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',4,1.25 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',1,4.25 
   
   INSERT INTO @TransHeader SELECT 'Crm01','CreditMemo','2/20/2013','Purch1'
   INSERT INTO @TransHeader SELECT 'Drm01','DebitMemo','3/28/2013','Purch1'
   INSERT INTO @TransHeader SELECT 'Crm02','CreditMemo','2/20/2013','Purch2'
   
   INSERT INTO @TransDetail SELECT 'Crm01','Prod1',1,9.95
   INSERT INTO @TransDetail SELECT 'Crm02','Prod1',1,3.1
   INSERT INTO @TransDetail SELECT 'Crm02','Prod2',1,9.95
   INSERT INTO @TransDetail SELECT 'Drm01','Prod1',1,20

-- Dispolay all Tables
   select * from @PurchHeader
   select * from @PurchDetail
   select * from @TransHeader   
   select * from @TransDetail

Open in new window

0
 
SharathData EngineerCommented:
try this.

/**************************************************
* Script to get the total of items based on
* the type of documento.  That is if is a Credit
* memo, Debit memos, Invoice, etc.
**************************************************/

-- Create Virtual table
   DECLARE @PurchHeader TABLE(
           PurchNo VARCHAR(20),
           PurchDate DATE,
           Vendor VARCHAR(20),
           TotalPurch FLOAT,
           TotalPurchUpdt FLOAT)
   DECLARE @PurchDetail TABLE(
           PurchNo VARCHAR(20),
           Prod VARCHAR(20),
           Qty INT,
           Cost FLOAT)
   DECLARE @TransHeader TABLE(
           TransNo VARCHAR(20),
           TransType VARCHAR(20),
           TransDate DATE,
           PurchHeaderNo VARCHAR(20))
   DECLARE @TransDetail TABLE(
           TransNo VARCHAR(20),
           Prod VARCHAR(20),
           Qty INT,
           Cost FLOAT)

-- Insert Test Data
   INSERT INTO @PurchHeader SELECT 'Purch1','1/1/2013','IBM',21.25,0
   INSERT INTO @PurchHeader SELECT 'Purch2','2/28/2013','TWA',29.55,0

   INSERT INTO @PurchDetail SELECT 'Purch1','Prod1',5,4.25 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod2',2,10.15 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',4,1.25 
   INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',1,4.25 
   
   INSERT INTO @TransHeader SELECT 'Crm01','CreditMemo','2/20/2013','Purch1'
   INSERT INTO @TransHeader SELECT 'Drm01','DebitMemo','3/28/2013','Purch1'
   INSERT INTO @TransHeader SELECT 'Crm02','CreditMemo','2/20/2013','Purch2'
   
   INSERT INTO @TransDetail SELECT 'Crm01','Prod1',1,9.95
   INSERT INTO @TransDetail SELECT 'Crm02','Prod1',1,3.1
   INSERT INTO @TransDetail SELECT 'Crm02','Prod2',1,9.95
   INSERT INTO @TransDetail SELECT 'Drm01','Prod1',1,20

 
  update PH
    set TotalPurchUpdt =  PH.TotalPurch + Cost
  from @PurchHeader PH
  join ( select TH.PurchHeaderNo,sum(case TH.TransType when 'CreditMemo' then -1
                              when 'DebitMemo' then 1 end * TD.Cost) Cost
     from @TransHeader TH
	 join @TransDetail TD on TH.TransNo = TD.TransNo
	group by TH.PurchHeaderNo) t1 on PH.PurchNo = t1.PurchHeaderNo
   select * from @PurchHeader

/*
PurchNo	PurchDate	Vendor	TotalPurch	TotalPurchUpdt
Purch1	2013-01-01	IBM	21.25	31.3
Purch2	2013-02-28	TWA	29.55	16.5
*/

Open in new window

0
 
rayluvsAuthor Commented:
I think I got it.  Please advice if (we have to run the script on a large database(:

SELECT p1.PurchNo,p1.Vendor,p1.TotalPurch,
      (SELECT SUM(t2.Qty*t2.Cost) FROM @TransDetail t2 
      INNER JOIN @TransHeader t1 ON t2.TransNo=t1.TransNo
      WHERE t1.PurchHeaderNo=p1.PurchNo)
      FROM @PurchHeader p1

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rayluvsAuthor Commented:
Ok saw an error.  Here is the closest we got (seems to work):

SELECT p1.PurchNo,p1.Vendor,p1.TotalPurch,p1.TotalPurch+
         (SELECT SUM(t2.Qty*
                     CASE WHEN t1.TransType='CreditMemo' THEN t2.Cost*(-1) WHEN t1.TransType='DebitMemo' THEN t2.Cost END 
                     ) FROM @TransDetail t2 
         INNER JOIN @TransHeader t1 ON t2.TransNo=t1.TransNo
         WHERE t1.PurchHeaderNo=p1.PurchNo)
      FROM @PurchHeader p1

Open in new window


Please advice.
0
 
rayluvsAuthor Commented:
Hi Sharath_123, didn't noticed your entry.  Just saw it and it works also.  As a matter of fact, I prefer your query instead is more cleaner.

Thanx
0
 
rayluvsAuthor Commented:
Thanx
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now