Solved

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

Posted on 2013-10-25
6
311 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 32
Move SQL 2005 Express to Server 2012R2 19 73
How to calculate iops? 12 27
sqlseverexpress 2008 agent xps question 1 11
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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

18 Experts available now in Live!

Get 1:1 Help Now