Solved

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

Posted on 2013-10-25
6
319 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 40

Accepted Solution

by:
Sharath earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now