如何在商店程序中显示盈亏金额 [英] How to show profit/loss amount in storeprocedure

问题描述

ALTER PROCEDURE FETCH_INCOME_EXPENSES_DETAILS
(
@FROM_DATE DATETIME,
@TO_DATE DATETIME
)
AS
BEGIN

DECLARE @TEMPTABLE TABLE
(
	INCOME_TYPE NVARCHAR(4000),
	INCOME_AMT	BIGINT,
	
	EXPENSES_TYPE NVARCHAR(4000),
	EXPENSES_AMT BIGINT
)


INSERT INTO @TEMPTABLE (INCOME_TYPE,INCOME_AMT)
SELECT 'ADVANCED FROM ROOM',SUM(ADVANCE_AMT) FROM ROOMBOOKED WHERE ARRDATE BETWEEN @FROM_DATE AND @TO_DATE

INSERT INTO @TEMPTABLE (INCOME_TYPE,INCOME_AMT)
SELECT 'ADVANCED FROM HALL',SUM(ADVANCE_PAID) FROM HALL_BOOKED_DETAILS WHERE BOOKED_DATE BETWEEN @FROM_DATE AND @TO_DATE

INSERT INTO @TEMPTABLE (INCOME_TYPE,INCOME_AMT)
SELECT 'ROOM CHECKEDOUT',SUM(ADDOUTSTANDING) FROM CHECKEDOUT_DETAILS WHERE BILLEDDATE BETWEEN @FROM_DATE AND @TO_DATE

我正在使用下面的代码来获取特定日期之间的金额明细.，我正在使用此存储过程来获取报告(使用Crystal报告).正确提取金额，但如何显示其利润金额或亏损金额.
在这里，我使用"PL_amount"来显示金额.当我得到减号以及金额时，它表示损失了其他利润.如何执行此请求可以帮助我.，thanx提前

i am using below code to fetch amount details between particular date.,i am using this stored procedure to get reports (using crystal reports). Amount fetching correctly but how to show whether its profit amount or loss amount.
here i am using "PL_amount " to show amount., when i am getting minus symbol along with amount it means loss else profit., how to do this pleas help me out., thanx in advance

INSERT INTO @TEMPTABLE (EXPENSES_TYPE,EXPENSES_AMT)
SELECT EXPENSES_TYPE,SUM(RATE) FROM OTHEREXPENSES WHERE EXPENSES_DATE BETWEEN @FROM_DATE AND @TO_DATE  GROUP BY EXPENSES_TYPE

INSERT INTO @TEMPTABLE (EXPENSES_TYPE,EXPENSES_AMT)
SELECT EXPENSES_TYPE,SUM(EXPENSES_AMT) FROM HOTEL_EXPENSES WHERE EXPENSES_DATE BETWEEN @FROM_DATE AND @TO_DATE GROUP BY EXPENSES_TYPE

DECLARE @TOTAL_INCOME_AMT BIGINT
DECLARE @TOTAL_EXPENSES BIGINT
SELECT @TOTAL_INCOME_AMT=SUM(INCOME_AMT) FROM @TEMPTABLE
SELECT @TOTAL_EXPENSES=SUM(EXPENSES_AMT) FROM @TEMPTABLE

DECLARE @PLAMOUNT NUMERIC(13,2)
SELECT @PLAMOUNT=@TOTAL_INCOME_AMT - @TOTAL_EXPENSES

SELECT CONVERT(VARCHAR,@FROM_DATE,103) AS FROM_DATE,CONVERT(VARCHAR,@TO_DATE,103) AS TO_DATE,@TOTAL_INCOME_AMT AS TOTALINCOME,@TOTAL_EXPENSES AS EXPENSESAMT,@PLAMOUNT AS PL_AMOUNT, * FROM @TEMPTABLE
END

推荐答案

使用case语句进行检查，如下所示

check with case statement as below

select @PLAMOUNT as PLAMOUNT,case when @PLAMOUNT < 0 then 'loss' else 'profit' end as [profit_loss]

我认为您应该尝试<显示字段PL_AMOUNT时，水晶报表中的比较为0.

I think you should try < 0 comparison in crystal report when you are displaying field PL_AMOUNT.

这篇关于如何在商店程序中显示盈亏金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！