Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: Display all accounts

    I have 3 tables
    CREATE TABLE [dbo].[ACCT_MASTER](
    [POLICY_YEAR] [char](4) NULL,
    [GL_ACCOUNT] [nvarchar](8) NULL,
    [GL_ACCT_DESCRIPTION] [nvarchar](100) NULL,
    [GL_ACCT_LINE_NUM] [int] NULL,
    [GL_NUM_LINE_NUM] [int] NULL,
    [GENERAL] [int] NULL,
    [ACTIVE] [int] NULL
    )
    CREATE TABLE [dbo].[HISTORY](

    [ACCT_PERIOD] [nvarchar](4) NOT NULL,
    [GL_NUMBER] [nvarchar](13) NOT NULL,
    [TRAN_DATE] [smalldatetime] NOT NULL,
    [DESC_LINE] [nvarchar](2000) NULL,
    [TRAN_TYPE] [char](1) NULL,
    [AMOUNT] [money] NULL
    )
    CREATE TABLE [dbo].[DATES](
    [GL_CUR_EOM_DATE] [smalldatetime] NOT NULL,
    [GL_PRIOR_EOM_DATE] [smalldatetime] NOT NULL,
    [GL_CUR_ACCTG_PERIOD] [nvarchar](4) NOT NULL,
    [GL_PRIOR_ACCTG_PERIOD] [nvarchar](4) NOT NULL

    )


    ACCT_MASTER HISTORY Dates
    Gl_ACCOUNT year GL_NUMBER Perid
    12345-00 2013 12345-00-2013 1304
    67890-00 2010 67890-00-2010 1305
    54321-08 2013 54321-00-2013 1304
    .
    .
    Total of 3640 accounts
    I can’t figure out how to display all 3640 accounts. If there is no match in HISTORY table for this period display 0 for the calculations but display Gl_ACCOUNT + year.
    12345-00-2013
    67890-00-2010 0
    54321-00-2013
    All 3640 rows here

    My code shows only 3469 records.
    select M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')NewGL, isNull (SUM(PRIOR_VDIFFPRIOR), 0)as [PriorEndOfMont],
    ISNULL(sum(CURR_VDIFFPRIOR),0) as [CurrentEndOfmonth] ,
    isnull (SUM (PRIOR_VDIFFPRIOR),0) - isnull (sum(CURR_VDIFFPRIOR),0) as Diffrence
    from GL_ACCT_MASTER m
    left outer join SUMMARY s on M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000') = s.GL_NUMBER
    group by GL_NUMBER,M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')order by GL_NUMBER,M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, you appear to have 5 tables, and the two in your query (reformated below) are not shown:
    Code:
    select M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')NewGL, 
    	isNull (SUM(PRIOR_VDIFFPRIOR), 0)as [PriorEndOfMont],
    	ISNULL(sum(CURR_VDIFFPRIOR),0) as [CurrentEndOfmonth] ,
    	isnull (SUM (PRIOR_VDIFFPRIOR),0) - isnull (sum(CURR_VDIFFPRIOR),0) as Diffrence
    from GL_ACCT_MASTER m left outer join 
    	SUMMARY s on M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000') = s.GL_NUMBER
    group by GL_NUMBER, M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')
    order by GL_NUMBER, M.GL_ACCOUNT +'-'+ isnull(policy_year, '0000')

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    Sorry wrong Query

    select m.GL_ACCOUNT + '-'+ isnull (POLICY_YEAR, '0000') as GL_NUMBER,d.GL_PRIOR_ACCTG_PERIOD,
    SUM((Case When h.Tran_Type = 'D' Then h.AMOUNT Else 0 End)) AS PRIOR_DB_AMOUNT,
    SUM((Case When h.Tran_Type = 'C' Then h.AMOUNT Else 0 End)) AS PRIOR_CR_AMOUNT,
    SUM((Case When h.Tran_Type = 'D' Then h.AMOUNT Else 0 End)) -
    SUM((Case When h.Tran_Type = 'C' Then h.AMOUNT Else 0 End)) AS PRIOR_VDIFFPRIOR
    from ACCT_MASTER M
    LEFT OUTER JOIN HISTORY H ON m.GL_ACCOUNT + '-'+ isnull (POLICY_YEAR, '0000') = H.GL_NUMBER
    INNER JOIN DATES D
    ON H.ACCT_PERIOD = d.GL_PRIOR_ACCTG_PERIOD
    group by h.GL_NUMBER, m.GL_ACCOUNT + '-'+ isnull (POLICY_YEAR, '0000'),d.GL_PRIOR_ACCTG_PERIOD

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about something like this?
    Code:
    select m.GL_ACCOUNT + '-' + isnull(m.POLICY_YEAR, '0000') as GL_NUMBER
         , d.GL_PRIOR_ACCTG_PERIOD
         , SUM(Case h.Tran_Type
               When 'D' Then h.AMOUNT
               Else          0
               End) AS PRIOR_DB_AMOUNT
         , SUM(Case h.Tran_Type
               When 'C' Then h.AMOUNT
               Else          0
               End) AS PRIOR_CR_AMOUNT
         , SUM(Case h.Tran_Type
               When 'D' Then   h.AMOUNT
               When 'C' Then - h.AMOUNT
               Else          0
               End) AS PRIOR_VDIFFPRIOR
     from  ACCT_MASTER M
     LEFT  OUTER JOIN
           HISTORY     H
      ON   h.GL_NUMBER = m.GL_ACCOUNT + '-' + isnull(m.POLICY_YEAR, '0000')
     LEFT  OUTER JOIN
           DATES       D 
      ON   d.GL_PRIOR_ACCTG_PERIOD = H.ACCT_PERIOD
     group by
           m.GL_ACCOUNT + '-' + isnull(m.POLICY_YEAR, '0000')
         , d.GL_PRIOR_ACCTG_PERIOD 
    ;
    or this?

    Code:
    ...
    ...
     from  ACCT_MASTER M
     LEFT  OUTER JOIN
           HISTORY     H
     INNER JOIN
           DATES       D 
      ON   d.GL_PRIOR_ACCTG_PERIOD = H.ACCT_PERIOD
      ON   h.GL_NUMBER = m.GL_ACCOUNT + '-' + isnull(m.POLICY_YEAR, '0000')
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •