Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate balance

Author  Topic 

dinhson_vn
Starting Member

15 Posts

Posted - 2013-08-27 : 09:24:09
I currently have 1 procedure:

ALTER PROCEDURE [dbo].[sp_Support_Out_In_Detail]
@No AS NVARCHAR(4000),
@FromDate DATETIME,
@ToDate DATETIME,
@TransInvoiceID INT

AS
SELECT x.No,x.Name,x.ZIPCODE,x.FromDate,x.ToDate,x.Total450c, n.Total450k, x.Total24c,

ISNULL(x.Total450c,0)-(ISNULL(n.Total450k,0)) TotalCheck450, /*-Save TotalCheck450 to calculate and compare--*/
((x.Total24c)*1000) ATMTotalSupport24,

CASE
WHEN ISNULL(x.Total450c,0)-(ISNULL(n.Total450k,0)) <0 THEN x.Total450c*800
WHEN ISNULL(x.Total450c,0)-(ISNULL(n.Total450k,0)) >0 THEN n.Total450k*800
WHEN ISNULL(x.Total450c,0)-(ISNULL(n.Total450k,0)) =0 THEN x.Total450c*800
END ATMToatlSupport45

INTO #tmpSupportCost0003
FROM Tbl_AmountTranpost_Detail_Out x LEFT JOIN Tbl_AmountTranpost_Detail_In n ON
n.No=x.No
AND x.No = n.No
WHERE x.No=@No
AND CONVERT(NVARCHAR(10),x.FromDate,112) = CONVERT(NVARCHAR(10),@FromDate,112)
AND CONVERT(NVARCHAR(10),x.ToDate,112) = CONVERT(NVARCHAR(10),@ToDate,112)
--AND x.No=b.No

/*--Update Data --*/

INSERT INTO Tbl_SupportCost
(No
,Name
,ZIPCODE
,FromDate
,ToDate
,Total450c
,Total450k
,Total24c
,TotalCheck450
,ATMTotalSupport24
,ATMToatlSupport45
,IsDelete
,CreateDate
,TransInvoiceID)
SELECT
@No
,Name
,ZIPCODE
,@FromDate
,@ToDate
,Total450c
,Total450k
,Total24c
,TotalCheck450
,ATMTotalSupport24
,ATMToatlSupport45
,0
,GETDATE()
,@TransInvoiceID
FROM #tmpSupportCost0003
DROP TABLE #tmpSupportCost0003

Table save Totalcheck450 is structured as follows:


Now when the next month, I want to n.Total450k + Totalcheck

-01/2013: ISNULL (x.Total450c, 0) - (ISNULL (n.Total450k, 0)) TotalCheck450 = A (A number will be saved, TotalCheck450)
-02/2013: ISNULL (x.Total450c, 0) - (ISNULL (n.Total450k, 0)) TotalCheck450

here A number would be : A + (ISNULL (n.Total450k, 0)

- What can I do to get results ?

Looking forward to the help from everyone
I thank you very much.

dinhson_vn
Starting Member

15 Posts

Posted - 2013-08-27 : 10:19:09
You do not understand, I will answer. and send the illustrations.



Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 02:36:29
looks like you need a running count.
see scenario 1 below

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dinhson_vn
Starting Member

15 Posts

Posted - 2013-08-28 : 03:31:16
quote:
Originally posted by visakh16

looks like you need a running count.
see scenario 1 below

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Not you
The number of shipments is AB (out put)
The number of returns is AC (input)

Calculate the number of AC-AB

I will have 1 ABC figures:

If the ABC <= 0 the AC * 800
If the ABC > 0 then ABC * 800

Here is what the procedure was done.

Now if the AC-AB> 0 then saved the ABC

ABC will be added to the AC of the following month

ABC + AC will be added next month
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 12:19:11
quote:
Originally posted by dinhson_vn

quote:
Originally posted by visakh16

looks like you need a running count.
see scenario 1 below

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Not you
The number of shipments is AB (out put)
The number of returns is AC (input)

Calculate the number of AC-AB

I will have 1 ABC figures:

If the ABC <= 0 the AC * 800
If the ABC > 0 then ABC * 800

Here is what the procedure was done.

Now if the AC-AB> 0 then saved the ABC

ABC will be added to the AC of the following month

ABC + AC will be added next month


I cant correlate your explanation with earlier posted output
Can you explain with relation to that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dinhson_vn
Starting Member

15 Posts

Posted - 2013-08-28 : 22:43:27
quote:

I cant correlate your explanation with earlier posted output
Can you explain with relation to that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs






For example




Hope you can help

Thanks
Go to Top of Page

dinhson_vn
Starting Member

15 Posts

Posted - 2013-08-29 : 09:02:46
Help Me !!!

Thanks.
Go to Top of Page
   

- Advertisement -