Click here to Skip to main content
15,916,951 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Here is my query with left join and I am automatically developing credit debit and balance.

Perhaps I am lacking upon column. I used gl_drcr as main column and split it by query into credit and debit but I am unable to get value of balance. I have column of amount also, where I can store amount values.

Can you help me to sort out where my fault is in this query?

What I have tried:

SQL
ALTER procedure [dbo].[sp_getDataML]
    (@gl_acid int, @userid int)
AS
BEGIN
    SELECT 
        ROW_NUMBER() OVER (ORDER BY gLedeger.PK_id) AS sno,
        CASE 
           WHEN master.ma_accname IS NULL THEN ''
           ELSE ma_accname 
        END AS AccName, 
        gLedeger.gl_date AS date, gLedeger.gl_narration AS Narration, 
        gLedeger.gl_drcr as DRCR,
        CASE
           WHEN gLedeger.gl_drcr > 0 THEN ABS(gLedeger.gl_drcr) 
           ELSE 0.00 
        END AS debit,
        CASE 
           WHEN gLedeger.gl_drcr < 0 THEN ABS(gLedeger.gl_drcr) 
           ELSE 0.00 
        END AS credit,
        SUM(COALESCE(gLedeger.gl_drcr.credit, 0) 
         - COALESCE(gLedeger.gl_drcr.debit, 0)) AS Balance  
    FROM 
        gLedeger   
    LEFT JOIN 
        master ON gLedeger.GL_ACID = master.PK_ID 
    LEFT JOIN
        registration r ON master.userId = @userid
    WHERE 
        gLedeger.GL_ACID = @gl_acid
        
    ORDER BY 
        gLedeger.gl_date
END
Posted
Updated 24-Oct-16 4:07am
v4

Not sure if I understand the problem correctly but one thing is that you seem to be missing join condition between registration and some other table. Also you could move the condition
SQL
master.userId = @userid

to WHERE clause
 
Share this answer
 
i got the solution
as follows:-

SQL
ALTER procedure [dbo].[fulltest]
(
  @gl_acid int,
  @userid int
  )
  as 
  begin


SELECT 	ROW_NUMBER() OVER (Order by gLedeger.PK_id) as sno,case when master.ma_accname is null then '' else ma_accname end as AccName,gLedeger.gl_date as date,gLedeger.gl_narration  as Narration,gLedeger.gl_drcr as DRCR,
	case when gLedeger.gl_drcr > 0 then abs(gLedeger.gl_drcr) else 0.00 end as debit,
	case when gLedeger.GL_DRCR	 < 0 then abs(gLedeger.gl_drcr) else 0.00 end as credit,
SUM(gLedeger.gl_drcr) OVER(ORDER BY gledeger.pk_ID ROWS UNBOUNDED PRECEDING) AS    Balance  
	FROM gLedeger   LEFT JOIN master ON 
    gLedeger.GL_ACID = master.PK_ID left join registration r on master.userId=@userid
    WHERE gLedeger.GL_ACID = @gl_acid
	/*and LEFT(gLedeger.gl_voucher,2)=@voucher*/
	
	ORDER BY gLedeger.gl_date

	end
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900