Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Question Unanswered: sick query help!

    I am probably dreaming, but I am stumped on this!

    The LY data comes back and balances perfectly and the CUR balances perfectly, but when I join them together I have a few columns that are off and I cant find theproblem, I have been staring at this for a day now!

    Not sure of any good ways to give more detail to help diagnose problem without access to db....

    here goes:

    SELECT [Reverse] as Reversed, Descr, LINES.LineNu as LineNu, LineType,
    Amt01 = Sum(isNull(Amt01,0)),
    Amt02 = Sum(isNull(Amt02,0)),
    Amt03 = Sum(isNull(Amt03,0)),
    Amt04 = Sum(isNull(Amt04,0)),
    Amt05 = Sum(isNull(Amt05,0)),
    Amt06 = Sum(isNull(Amt06,0)),
    Amt07 = Sum(isNull(Amt07,0)),
    Amt08 = Sum(isNull(Amt08,0)),
    Amt09 = Sum(isNull(Amt09,0)),
    Amt10 = Sum(isNull(Amt10,0)),
    Amt11 = Sum(isNull(Amt11,0)),
    Amt12 = Sum(isNull(Amt12,0)),
    Amt13 = Sum(isNull(Amt13,0)),
    Bud01 = Sum(isNull(Bud01,0)),
    Bud02 = Sum(isNull(Bud02,0)),
    Bud03 = Sum(isNull(Bud03,0)),
    Bud04 = Sum(isNull(Bud04,0)),
    Bud05 = Sum(isNull(Bud05,0)),
    Bud06 = Sum(isNull(Bud06,0)),
    Bud07 = Sum(isNull(Bud07,0)),
    Bud08 = Sum(isNull(Bud08,0)),
    Bud09 = Sum(isNull(Bud09,0)),
    Bud10 = Sum(isNull(Bud10,0)),
    Bud11 = Sum(isNull(Bud11,0)),
    Bud12 = Sum(isNull(Bud12,0)),
    Bud13 = Sum(isNull(Bud13,0)),
    LyAmt01 = Sum(isNull(LyAmt01,0)),
    LyAmt02 = Sum(isNull(LyAmt02,0)),
    LyAmt03 = Sum(isNull(LyAmt03,0)),
    LyAmt04 = Sum(isNull(LyAmt04,0)),
    LyAmt05 = Sum(isNull(LyAmt05,0)),
    LyAmt06 = Sum(isNull(LyAmt06,0)),
    LyAmt07 = Sum(isNull(LyAmt07,0)),
    LyAmt08 = Sum(isNull(LyAmt08,0)),
    LyAmt09 = Sum(isNull(LyAmt09,0)),
    LyAmt10 = Sum(isNull(LyAmt10,0)),
    LyAmt11 = Sum(isNull(LyAmt11,0)),
    LyAmt12 = Sum(isNull(LyAmt12,0)),
    LyAmt13 = Sum(isNull(LyAmt13,0)),
    LyBud01 = Sum(isNull(LyBud01,0)),
    LyBud02 = Sum(isNull(LyBud02,0)),
    LyBud03 = Sum(isNull(LyBud03,0)),
    LyBud04 = Sum(isNull(LyBud04,0)),
    LyBud05 = Sum(isNull(LyBud05,0)),
    LyBud06 = Sum(isNull(LyBud06,0)),
    LyBud07 = Sum(isNull(LyBud07,0)),
    LyBud08 = Sum(isNull(LyBud08,0)),
    LyBud09 = Sum(isNull(LyBud09,0)),
    LyBud10 = Sum(isNull(LyBud10,0)),
    LyBud11 = Sum(isNull(LyBud11,0)),
    LyBud12 = Sum(isNull(LyBud12,0)),
    LyBud13 = Sum(isNull(LyBud13,0))
    FROM (
    SELECT [Reverse], Descr, LineNu, LineType
    FROM shelbydb.shelby.glReportLines
    where (reportcounter = 65)
    ) LINES
    LEFT OUTER JOIN (
    SELECT AcctNuHigh, AcctNuLow, LineNu, DeptNu, FundNu
    from shelbydb.shelby.glReportLinks
    where (reportcounter = 65)
    ) LINKS
    on LINKS.linenu = LINES.linenu
    LEFT OUTER JOIN (
    SELECT * FROM (
    SELECT s.conu as CoNu, s.deptnu as DeptNu,
    s.fundnu as FundNu, s.acctnu as AcctNu,
    Amt01 = Sum(isNull(Amt01,0)),
    Amt02 = Sum(isNull(Amt02,0)),
    Amt03 = Sum(isNull(Amt03,0)),
    Amt04 = Sum(isNull(Amt04,0)),
    Amt05 = Sum(isNull(Amt05,0)),
    Amt06 = Sum(isNull(Amt06,0)),
    Amt07 = Sum(isNull(Amt07,0)),
    Amt08 = Sum(isNull(Amt08,0)),
    Amt09 = Sum(isNull(Amt09,0)),
    Amt10 = Sum(isNull(Amt10,0)),
    Amt11 = Sum(isNull(Amt11,0)),
    Amt12 = Sum(isNull(Amt12,0)),
    Amt13 = Sum(isNull(Amt13,0)),
    Bud01 = Sum(isNull(Bud01,0)),
    Bud02 = Sum(isNull(Bud02,0)),
    Bud03 = Sum(isNull(Bud03,0)),
    Bud04 = Sum(isNull(Bud04,0)),
    Bud05 = Sum(isNull(Bud05,0)),
    Bud06 = Sum(isNull(Bud06,0)),
    Bud07 = Sum(isNull(Bud07,0)),
    Bud08 = Sum(isNull(Bud08,0)),
    Bud09 = Sum(isNull(Bud09,0)),
    Bud10 = Sum(isNull(Bud10,0)),
    Bud11 = Sum(isNull(Bud11,0)),
    Bud12 = Sum(isNull(Bud12,0)),
    Bud13 = Sum(isNull(Bud13,0))
    FROM shelbydb.shelby.GLSummary S
    LEFT OUTER JOIN shelbydb.shelby.GLBudget B
    on
    (s.begindate = b.begindate)
    and (s.acctnu = b.acctnu)
    and (s.conu = b.conu)
    and (s.deptnu = b.deptnu)
    and (s.fundNu = b.fundNu)
    WHERE
    s.begindate = '1/1/2004'
    group by
    S.conu, S.deptnu, S.fundnu, S.acctnu,
    b.conu, b.deptnu, b.fundnu, b.acctnu
    ) CUR
    LEFT OUTER JOIN
    (
    SELECT
    LyS.conu as LCoNu, LyS.deptnu as LDeptNu,
    LyS.fundnu as LFundNu, LyS.acctnu as LAcctNu,
    LyAmt01 = Sum(isNull(Amt01,0)),
    LyAmt02 = Sum(isNull(Amt02,0)),
    LyAmt03 = Sum(isNull(Amt03,0)),
    LyAmt04 = Sum(isNull(Amt04,0)),
    LyAmt05 = Sum(isNull(Amt05,0)),
    LyAmt06 = Sum(isNull(Amt06,0)),
    LyAmt07 = Sum(isNull(Amt07,0)),
    LyAmt08 = Sum(isNull(Amt08,0)),
    LyAmt09 = Sum(isNull(Amt09,0)),
    LyAmt10 = Sum(isNull(Amt10,0)),
    LyAmt11 = Sum(isNull(Amt11,0)),
    LyAmt12 = Sum(isNull(Amt12,0)),
    LyAmt13 = Sum(isNull(Amt13,0)),
    LyBud01 = Sum(isNull(Bud01,0)),
    LyBud02 = Sum(isNull(Bud02,0)),
    LyBud03 = Sum(isNull(Bud03,0)),
    LyBud04 = Sum(isNull(Bud04,0)),
    LyBud05 = Sum(isNull(Bud05,0)),
    LyBud06 = Sum(isNull(Bud06,0)),
    LyBud07 = Sum(isNull(Bud07,0)),
    LyBud08 = Sum(isNull(Bud08,0)),
    LyBud09 = Sum(isNull(Bud09,0)),
    LyBud10 = Sum(isNull(Bud10,0)),
    LyBud11 = Sum(isNull(Bud11,0)),
    LyBud12 = Sum(isNull(Bud12,0)),
    LyBud13 = Sum(isNull(Bud13,0))
    FROM shelbydb.shelby.GLSummary LyS
    LEFT OUTER JOIN shelbydb.shelby.GLBudget LyB
    on
    (LyS.begindate = LyB.begindate)
    and (LyS.acctnu = LyB.acctnu)
    and (LyS.conu = LyB.conu)
    and (LyS.deptnu = LyB.deptnu)
    and (LyS.fundNu = LyB.fundNu)
    where
    LyS.begindate = '1/1/2003'
    group by
    LyS.conu, LyS.deptnu, LyS.fundnu, LyS.acctnu,
    LyB.conu, LyB.deptnu, LyB.fundnu, LyB.acctnu
    ) LY
    ON
    (CUR.acctnu = LY.Lacctnu)
    and (CUR.conu = LY.Lconu)
    and (CUR.deptnu = LY.Ldeptnu)
    and (CUR.fundNu = LY.LfundNu)
    ) GL
    ON
    LINKS.acctnuLow <= GL.acctnu
    and
    LINKS.acctnuhigh >= GL.acctnu
    and
    LINKS.deptnu =
    ( case when links.deptnu <> -1
    then GL.deptnu
    else LINKS.deptnu end )
    and
    LINKS.fundnu =
    ( case when links.fundnu <> -1
    then GL.fundNu
    else LINKS.fundnu end )
    GROUP BY [Reverse], Descr, LINES.LineNu, LINES.LineType
    ORDER BY LINES.linenu

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Start by eliminating all the columns that DO work, and then post what remains along with a brief explanation of WHAT THE HECK YOU ARE TRYING TO DO!

    It takes a helluva long time to parse through something like this and then try to guess the coder's intentions. The more knowledgeable people on this forum generally have jobs, if not lives, so please respect our time.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I guess I don't have anything better to do with my time....

    Rewrite: "...AND LINKS.fundnu = (case when links.fundnu <> -1 then GL.fundNu else LINKS.fundnu end)"
    as: "...AND (LINKS.fundnu = -1 OR LINKS.fundnu = GL.deptnu)"

    Eliminate groupings not included in the SELECT clause.

    Simplify the query logic to work on only a single month. When you have the logic correct, expand it to include the other months.

    Run the individual subquerys separately to verify that they are working they way you expect them to.

    Code:
    SELECT	[Reverse] as Reversed,
    	Descr,
    	LINES.LineNu,
    	LineType, 
    	Amt01 = Sum(isNull(Amt01,0)), 
    	Bud01 = Sum(isNull(Bud01,0)), 
    	LyAmt01 = Sum(isNull(LyAmt01,0)), 
    	LyBud01 = Sum(isNull(LyBud01,0))
    FROM	(SELECT	[Reverse],
    		Descr,
    		LineNu,
    		LineType 
    	FROM	shelbydb.shelby.glReportLines 
    	WHERE	reportcounter = 65) LINES 
    	LEFT OUTER JOIN
    		(SELECT	AcctNuHigh,
    			AcctNuLow,
    			LineNu,
    			DeptNu,
    			FundNu 
    		FROM	shelbydb.shelby.glReportLinks 
    		WHERE	reportcounter = 65) LINKS 
    		ON LINKS.linenu = LINES.linenu 
    	LEFT OUTER JOIN
    		(SELECT	*
    		FROM	(SELECT	s.conu as CoNu,
    				s.deptnu as DeptNu, 
    				s.fundnu as FundNu,
    				s.acctnu as AcctNu, 
    				Amt01 = Sum(isNull(Amt01,0)),
    				Bud01 = Sum(isNull(Bud01,0))
    			FROM	shelbydb.shelby.GLSummary S 
    				LEFT OUTER JOIN shelbydb.shelby.GLBudget B 
    					on s.begindate = b.begindate
    					AND s.acctnu = b.acctnu
    					AND s.conu = b.conu
    					AND s.deptnu = b.deptnu
    					AND s.fundNu = b.fundNu
    			WHERE	s.begindate = '1/1/2004' 
    			GROUP BY S.conu,
    				S.deptnu,
    				S.fundnu,
    				S.acctnu) CUR 
    			LEFT OUTER JOIN 
    				(SELECT	LyS.conu as LCoNu,
    					LyS.deptnu as LDeptNu, 
    					LyS.fundnu as LFundNu,
    					LyS.acctnu as LAcctNu, 
    					LyAmt01 = Sum(isNull(Amt01,0)), 
    					LyBud01 = Sum(isNull(Bud01,0))
    				FROM	shelbydb.shelby.GLSummary LyS 
    					LEFT OUTER JOIN shelbydb.shelby.GLBudget LyB 
    					ON LyS.begindate = LyB.begindate
    					AND LyS.acctnu = LyB.acctnu
    					AND LyS.conu = LyB.conu
    					AND LyS.deptnu = LyB.deptnu
    					AND LyS.fundNu = LyB.fundNu
    				WHERE	LyS.begindate = '1/1/2003'
    				GROUP BY LyS.conu,
    					LyS.deptnu,
    					LyS.fundnu,
    					LyS.acctnu) LY 
    				ON CUR.acctnu = LY.Lacctnu
    				AND CUR.conu = LY.Lconu 
    				AND CUR.deptnu = LY.Ldeptnu 
    				AND CUR.fundNu = LY.LfundNu) GL 
    	ON LINKS.acctnuLow <= GL.acctnu 
    	AND LINKS.acctnuhigh >= GL.acctnu 
    	AND (LINKS.deptnu = -1 OR LINKS.deptnu = GL.deptnu)
    	AND (LINKS.fundnu = -1 OR LINKS.fundnu = GL.deptnu)
    GROUP BY [Reverse],
    	Descr,
    	LINES.LineNu,
    	LINES.LineType 
    ORDER BY LINES.linenu
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Thumbs up Thanks!

    I will give it a try in the morning, if thats still not it then I will repost as suggested.

    I greatly appreciate your help. I was not sure what would be the best way to present it, I will follow your suggestions in the future.

    -R

Posting Permissions

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