SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Calculate and save with WHEN conditions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dinhson_vn
Starting Member

9 Posts

Posted - 08/31/2013 :  10:27:55  Show Profile  Reply with Quote
Help me complete this procedure. It fails in the WHEN condition


  ALTER PROCEDURE [dbo].[sp_TranpostSupport_Xuat_Nhap_ToTal]	

	@NO_ AS NVARCHAR(4000),
	@FromDate DATETIME,
	@ToDate DATETIME,
	@TransInvoiceID INT
	
AS	
SELECT x.NO_,x.NAMES,x.ZIPCODE,x.FromDate,x.ToDate,x.Total450c, n.Total450k,

	   (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) TotalCheck450,
		     
	CASE
		WHEN (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) >0 THEN TotalCheck450     /*The purpose is to store the condition value TotalCheck450 */
		WHEN (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) < 0 THEN TotalCheck450 =0 /*	(ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0))*/ 
	END TotalCheck450

INTO                       #tmpSupportCost0003  
FROM  Tbl_AmountTranpost_Detail_Out x LEFT JOIN Tbl_AmountTranpost_Detail_In n ON 
		n.NO_=x.NO_ 
		AND n.ZIPCODE = x.ZIPCODE
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)
		
/*--Update Data--*/	

INSERT INTO Tbl_BalanceTranpostCost
		(NO_
		,NAMES
		,ZIPCODE
		,FromDate
		,ToDate
		,TotalCheck450
		,IsDelete
		,CreateDate
		,TransInvoiceID)
		
SELECT  @NO_
		,NAMES
		,ZIPCODE
		,@FromDate
		,@ToDate      
		,TotalCheck450
		,0
		,GETDATE()
		,@TransInvoiceID		
FROM #tmpSupportCost0003     
 
DROP TABLE #tmpSupportCost0003 


Thank.

dinhson_vn
Starting Member

9 Posts

Posted - 08/31/2013 :  11:33:43  Show Profile  Reply with Quote
SELECT x.NO_,x.NAMES,x.ZIPCODE,x.FromDate,x.ToDate,x.Total450c, n.Total450k,

--(ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) TotalCheck450,

CASE
WHEN (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) >0 THEN (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) /*The purpose is to store the condition value TotalCheck450 */
WHEN (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0)) < 0 THEN 0 /* (ISNULL(n.Total450k,0)-ISNULL(x.Total450c,0))*/
END TotalCheck450

I had do it complete
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000