?
Solved

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

Posted on 2013-10-25
6
Medium Priority
?
335 Views
Last Modified: 2013-10-25
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
Comment
Question by:rayluvs
  • 5
6 Comments
 

Author Comment

by:rayluvs
ID: 39600659
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39600966
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
 

Author Comment

by:rayluvs
ID: 39601004
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
Get your problem seen by more experts

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

 

Author Comment

by:rayluvs
ID: 39601041
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
 

Author Comment

by:rayluvs
ID: 39601762
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
 

Author Closing Comment

by:rayluvs
ID: 39601763
Thanx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

568 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question