GL Query Is Showing Null values

[former_member188586]

hi experts , i made Query like This

one Query Gives Null value , Another Query Give Not Null

SELECT T1.[CardCode],T1.[CardName],sum(T0.[Debit]) "Debit",sum(T0.[Credit]) "Credit",(sum(T0.[Debit])-sum(T0.[Credit])) "Total FSD"

FROM JDT1 T0 Full  JOIN OCRD T1 ON T1.[CardCode] = T0.[ContraAct]

WHERE  T0.[RefDate] >='20120401'  AND T0.[RefDate] <='20120415' AND T0.[Account] ='205002'

GROUP BY T1.[CardCode],T1.[CardName]

The Above Query is Correct But Give Null Value on Bp Code ANd Bp Name , Please If Any one Know The Solution Please replay 

SELECT T1.[CardCode],T1.[CardName],sum(T0.[Debit]) "Debit",sum(T0.[Credit]) "Credit",(sum(T0.[Debit])-sum(T0.[Credit])) "Total FSD"

FROM JDT1 T0  Join  OCRD T1 ON  T0.[ContraAct]=T1.[CardCode]

WHERE  T0.[RefDate] >='20120401'  AND T0.[RefDate] <='20120415' AND (T0.[Account] ='205002' )

GROUP BY T1.[CardCode],T1.[CardName]

---Thanks for Advance Solution

--Ramudu

[former_member188586]

Thanks you very much for  Help sir  , But Not Understand why it's Not Fetching data.........

[KennedyT21]

The Logic you are looking for is not posssible

[KennedyT21]

Try This one

SELECT

  T0.RefDate AS 'Posting Date',

  T0.DueDate,

  T0.TaxDate AS 'Document Date',

  T0.BaseRef AS 'Doc.No.',

  T0.TransId AS 'Trans.No.',

  T0.Memo AS 'Remarks',

  T1.[ContraAct] 'OffsetAcct',

  Account = (CASE

    WHEN T2.AcctName IS NULL THEN t3.cardname

    ELSE T2.AcctName

  END),

  T1.Debit,

  T1.Credit

FROM OJDT T0

INNER JOIN JDT1 T1

  ON T0.TransId = T1.TransId

LEFT OUTER JOIN OACT T2

  ON T2.AcctCode = T1.ContraAct

LEFT OUTER JOIN OCRD t3

  ON t3.CardCode = T1.ContraAct

--Where T0.RefDate>=@d1 and T0.RefDate<=@d2 

WHERE T0.TaxDate >= '20120401'

AND T0.taxdate <= '20120415'

AND (T1.[ContraAct] = '205002'

OR t1.ShortName = '205002')

ORDER BY T0.TransId, t1.Line_ID

Regards

Kennedy

[KennedyT21]

Try this Genral report format...

declare @sAccFrom as nvarchar(10)

declare @sAccTo as nvarchar(10)

declare @sPCFrom as nvarchar(10)

declare @sPCTo as nvarchar(10)

declare @dPostFromMax as datetime

declare @dPostToMax as datetime

declare @dPostFrom as datetime

declare @dPostTo as datetime

declare @dDocFrom as datetime

declare @dDocTo as datetime

declare @dTaxFrom as datetime

declare @dTaxTo as datetime

declare @dPeriodFrom as datetime

declare @dPeriodTo as datetime

declare @dPeriod as datetime

declare @sTmp as nchar(1)

declare @AcctCode as nvarchar(20)

declare @AcctCode_Tmp as nvarchar(20)

declare @AcctName as nvarchar(100)

declare @RefDate as datetime

declare @TransType as nvarchar(10)

declare @TransTypeD as nvarchar(4)

declare @TransId as int

declare @Desc2 as nvarchar(254)

declare @LineMemo as nvarchar(254)

declare @Debit as numeric(19,6)

declare @Credit as numeric(19,6)

declare @ValueTmp as numeric(19,6)

declare @DiscPrcnt as numeric(19,6)

declare @PrevBalance as numeric(19,6)

declare @PrevBalanceTmp as numeric(19,6)

declare @Balance as numeric(19,6)

declare @OpenBalance as numeric(19,6)

declare @GrandBalance as numeric(19,6)

declare @Index as int

declare @CreditAcc as nvarchar(1)

declare @PYear as int

declare @CurrPTmp as int

declare @CurrP as int

declare @PFrom as int

declare @PTo as int

/* SELECT FROM [dbo].[JDT1] T0 */

declare  @FromAcct as nvarchar(20)

/*  WHERE  */

set @FromAcct =   /*  T0.Account  */  '[%0]'

/* SELECT FROM [dbo].[JDT1] T1 */

declare  @ToAcct as nvarchar(20)

/*  WHERE   */

set @ToAcct =  /*  T0.Account  */  '[%1]'

CREATE TABLE [#GeneralLedger] (IdInt int PRIMARY KEY IDENTITY,

             LineType int,

   AcctCode  nvarchar(20),

   AcctName  nvarchar(100),

   RefDate  datetime,

   TransType  nvarchar(4),

            TransTypeCode  nvarchar(10),

   TransId  int, 

   Desc2  nvarchar(254),

   LineMemo  nvarchar(254),

   Debit  numeric(19,6),

   Credit  numeric(19,6),

   Balance  numeric(19,6)

)

  /* SELECT FROM [dbo].[JDT1]  T0 */

declare  @FromDate as datetime

/*  WHERE  */

set @FromDate = /*  T0.RefDate */  '[%2]'

/* SELECT FROM [dbo].[JDT1] T1 */

declare  @ToDate as datetime

/* WHERE  */

set @ToDate = /*  T0.RefDate  */  '[%3]'

SET @dPostFromMax =  GETDATE()

SET @dPostFromMax =  @FromDate

SET @dPostToMax =  GETDATE()

SET @dPostToMax =  @ToDate

SET @Index=0

SET @PFrom = 0

SET @PYear = DATEPART(YEAR, @dPostFromMax)

SET @PFrom = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.F_RefDate)= @PYear AND DATEPART(MONTH,T1.F_RefDate)=DATEPART(MONTH, @dPostFromMax))

SET @PTo = (select top 1 T1.AbsEntry from OFPR T1 where DATEPART(YEAR,T1.T_RefDate)= @PYear AND DATEPART(MONTH,T1.T_RefDate)=DATEPART(MONTH, @dPostToMax))

DECLARE First1 CURSOR FOR

select T0.AcctCode, T0.AcctName,

T1.RefDate,T1.TransType,

case when T1.TransType=13 then 'IN'

    when T1.TransType=14 then 'CN'

    when T1.TransType=15 then 'DN'

    when T1.TransType=16 then 'RE'

    when T1.TransType=162 then 'MR'

    when T1.TransType=18 then 'PU'

when T1.TransType=19 then 'PC'

when T1.TransType=20 then 'PD'

when T1.TransType=202 then 'PW'

when T1.TransType=21 then 'PR'

when T1.TransType=24 then 'RC'

when T1.TransType=30 then 'JE'

when T1.TransType=46 then 'PS'

when T1.TransType=58 then 'ST'

when T1.TransType=59 then 'SI'

when T1.TransType=60 then 'SO'

when T1.TransType=67 then 'IM'

else T1.TransType end,

T1.TransId,

'',

T1.LineMemo,

case when T1.Debit=0.0 then NULL else T1.Debit end,

case when T1.Credit=0.0 then NULL else T1.Credit end,

T0.LocManTran

from oact T0, jdt1 T1

where T1.TransType <> -3

    AND (T1.Debit <> 0.0 or T1.Credit <> 0.0)

    and T0.AcctCode = T1.Account

    AND T1.Account >= @FromAcct

AND T1.Account <= @ToAcct

AND T1.RefDate >= @FromDate

AND T1.RefDate <= @ToDate

ORDER BY T0.AcctCode, T1.RefDate, T1.TransId

SET @AcctCode_Tmp=''

SET @PrevBalance=0.0

SET @Balance=0.0

SET @OpenBalance=0.0

SET @CurrP = @PFrom

OPEN First1

FETCH NEXT FROM First1

INTO @AcctCode, @AcctName,

@RefDate, @TransType, @TransTypeD,

@TransId, @Desc2, @LineMemo, @Debit, @Credit , @CreditAcc

WHILE @@Fetch_Status = 0

BEGIN

SET @CurrPTmp = (select top 1 AbsEntry from OFPR WHERE T_RefDate>=@RefDate and F_RefDate<=@RefDate  )

IF ( @CurrP <> @CurrPTmp and @AcctCode = @AcctCode_Tmp)

BEGIN

    SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))

  SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where  T1.AbsEntry=LTRIM(RTRIM( @CurrPTmp)))

  IF (SELECT COUNT(*) FROM [#GeneralLedger]) > 0

  BEGIN

   SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)

           SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1  where T1.AbsEntry=LTRIM(RTRIM( @CurrP))) 

   INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' +  DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance - @OpenBalance

   INSERT INTO [#GeneralLedger] select 1,'',  CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2))+' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom) +' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2))+' ' + DATENAME(MONTH, @dPeriodTo)+',' + DATENAME(YEAR, @dPeriodTo),NULL,'','',NULL,'','',NULL,NULL,NULL

   SET @OpenBalance=  IsNULL( @PrevBalance,0.0)

  END

  SET @CurrP = @CurrPTmp

END

IF @AcctCode <> @AcctCode_Tmp

BEGIN

  IF (SELECT COUNT(*) FROM [#GeneralLedger])>0

  BEGIN

   SET @Balance= @Balance + IsNULL( @PrevBalance,0.0)

      SET @dPeriod = (select top 1 T1.F_RefDate from  OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP))) 

   INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL, @PrevBalance- @OpenBalance

   INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' + @AcctCode_Tmp+ ' :',NULL,NULL, @PrevBalance

  END

  SET @CurrP = @CurrPTmp

  SET @PrevBalance=(SELECT IsNULL(SUM(IsNULL(T1.Debit,0.0)),0.0) - IsNULL(SUM(IsNULL(T1.Credit,0.0)),0.0) FROM oact T0, jdt1 T1 where T1.TransType <> -3 and T1.Account = T0.AcctCode AND T1.Account >= @FromAcct AND T1.Account <= @ToAcct  AND T1.RefDate < @FromDate)

  SET @AcctCode_Tmp = @AcctCode

  SET @Index=0

  SET @OpenBalance = @PrevBalance

END

    SET @dPeriodFrom = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))

  SET @dPeriodTo = (select top 1 T1.T_RefDate from OFPR T1 where  T1.AbsEntry=LTRIM(RTRIM( @CurrP)))

IF @Index=0

BEGIN

  INSERT INTO [#GeneralLedger] select 0, @AcctCode, @AcctName,NULL,'','',NULL,'','',NULL,NULL,NULL

  INSERT INTO [#GeneralLedger] select 1,'', CAST(DATEPART(DAY, @dPeriodFrom) as NVARCHAR(2)) + ' ' + DATENAME(MONTH, @dPeriodFrom) + ','+ DATENAME(YEAR, @dPeriodFrom)+' - ' + CAST(DATEPART(DAY, @dPeriodTo) as NVARCHAR(2)) + ' '+ DATENAME(MONTH, @dPeriodTo) + ','+ DATENAME(YEAR, @dPeriodTo) ,NULL,'','',NULL,'','Period Open Balance ' +  @AcctCode + ' :',NULL,NULL, @PrevBalance

  SET @Index =1

END

IF  @Index = 1

BEGIN

  BEGIN

   SET @PrevBalance =  @PrevBalance + IsNULL( @Debit,0.0) - IsNULL( @Credit,0.0)

   IF @Debit < 0

    BEGIN

     SET @ValueTmp = ABS( @Debit)

     SET @Debit = NULL

     SET @Credit = IsNULL( @Credit,0) + @ValueTmp

    END

   IF @Credit < 0

    BEGIN

     SET @ValueTmp = ABS( @Credit)

     SET @Credit = NULL

     SET @Debit = IsNULL( @Debit,0) + @ValueTmp

    END

   IF @CreditAcc <> 'Y'  

    INSERT INTO [#GeneralLedger] select 3,'','', @RefDate , @TransTypeD , @TransType , @TransID , @Desc2 , @LineMemo , @Debit , @Credit , NULL

  END

END

FETCH NEXT FROM First1

INTO @AcctCode , @AcctName ,

  @RefDate , @TransType , @TransTypeD ,

  @TransId , @Desc2 , @LineMemo , @Debit , @Credit , @CreditAcc

END

IF (SELECT COUNT(*) FROM [#GeneralLedger])>0

BEGIN

SET @Balance = @Balance + @PrevBalance

SET @dPeriod = (select top 1 T1.F_RefDate from OFPR T1 where T1.AbsEntry=LTRIM(RTRIM( @CurrP)))

INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total Per Period ' + DATENAME(MONTH, @dPeriod) + ' :',NULL,NULL , @PrevBalance - @OpenBalance

INSERT INTO [#GeneralLedger] select 2,'','',NULL,'','',NULL,'','Total For Account ' +  @AcctCode + ' :',NULL,NULL, @PrevBalance

END

CLOSE First1

DEALLOCATE First1

SELECT case when IsNULL(T0.AcctCode,'')='' then '' else T0.AcctCode + ' - ' end  +  T0.AcctName as [Acc Name/Period], T0.RefDate as [Trx Date], T0.TransType as [Trx Code], T0.TransTypeCode, T0.TransId as [SAP Jrn No]  , T0.LineMemo as [Details], T0.Debit, T0.Credit, T0.Balance FROM [#GeneralLedger] T0

ORDER BY T0.IdInt

DROP TABLE [#GeneralLedger]

Regards

Kennedy

[Former Member]

Hi Ramu ,

Pls find the below query
SELECT

max(T0.ShortName) 'Customer',

Max(T2.CardName) 'Customer Name',

ISNULL (sum(T0.[Debit]),0) "Debit",

ISNULL (sum(T0.[Credit]),0) "Credit",

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) as "Amount(LC)"

FROM dbo.JDT1 T0 INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID

INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName

WHERE t1.RefDate >= '20150401' and t1.RefDate<=  '20150731'

and t0.account = '205002'

GROUP BY T0.ShortName

ORDER BY SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) DESC

Regards,

Ramasamy

[Former Member]

when using sum function for debit & credit amount u should include max condition for the Cardcode & Cardname...Tats y u r getting null characters in customer name & code Regards, K.Ramasamy

[former_member188586]

hi Ramaswamy

based Above Query , i did't get Any Value it showing Blank out put

[kothandaraman_nagarajan]

Hi,

If you use FULL outer join, then query takes both values from journal entry tables and also from particular BP code.

Thanks.

[former_member188586]

Hi sir

I tried

with Full outer Join , then also occurred

[kothandaraman_nagarajan]

Remove Full outer join and use just join.

[former_member188586]

I TRIED THAT ALSO SIR , SAME PROBLEM

[kothandaraman_nagarajan]

From your screen shot, i can see the difference.

What is your expected result?

[former_member188586]

I WANT THE cARD CODE AND CARD NAME SIR

WHICH SHOWS ON NULL

[kothandaraman_nagarajan]

But your second query result is correct right?

[former_member188586]

No

Sir 1st Query is Right but Showing Null Values

Today i'm Checking Another ledgers also facing Same problem ...

[Former Member]

ramu pls use isnull function for sum ... ur report will work fine

[Former Member]

Ramu ,

      If there is no records found for the GL account u will get the query output as empty

Regards,

K.Ramasamy

[former_member188586]

Hi Ramaswamy

please check once screen shot above replay...

Thanks for replay

[former_member188586]

please send condition or else add and send the Query..

[former_member188586]

Hi Minish

Please look once Bellow Image

The 1st output Showing Null values , if i open Gl Or JE the Card code and Card name is There but here Not Showing , what was the problem ..

[former_member184146]

Okay,

try this

SELECT T1.[CardCode],T1.[CardName],sum(T0.[Debit]) "Debit",sum(T0.[Credit]) "Credit",(sum(T0.[Debit])-sum(T0.[Credit])) "Total FSD"

FROM JDT1 T0 Full  JOIN OCRD T1 ON T1.[CardCode] = T0.[ContraAct]

WHERE  T0.[RefDate] >='20120401'  AND T0.[RefDate] <='20120415' AND T0.[Account] ='205002' and t0.ContraAct is not null

GROUP BY T1.[CardCode],T1.[CardName]

--Manish

[former_member188586]

Hi Manish ji

which you Given Query Give Same out , Like With Null on Card Code and Code Name

[KennedyT21]

Try This

SELECT

  T1.[CardCode],

  T1.[CardName],

  SUM(T0.[Debit]) "Debit",

  SUM(T0.[Credit]) "Credit",

  (SUM(T0.[Debit]) - SUM(T0.[Credit])) "Total FSD"

FROM JDT1 T0

INNER JOIN OCRD T1

  ON T0.[ContraAct] = T1.[CardCode]

WHERE T0.[RefDate] >= '20120401'

AND T0.[RefDate] <= '20120415'

AND (T0.[Account] = '205002')

GROUP BY T1.[CardCode],

         T1.[CardName]

[former_member188586]

hi sir

Thanks for your Replay ..

please check bellow Image sir then you understand what i'm facing sir

[former_member188586]

Any Update Please, it's Very Urgent

[former_member184146]

Heloo Ramudu,

Whats wrong with query both the querys are running fine. it did not give any error like you said null not null.

--Manish

[former_member188586]

ok wait i'll send you screen shot