?
Solved

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

Posted on 2013-10-25
6
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 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