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
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