View Problem

[ashab02]

Hello

I have two views. One of which calculates some costs to date and lists the sum of that along with a reference number. My second view also has costs but this costs field I have also attempted to add the sum of my first view. The problem I am having is that it brings a ridiculously high figure even though it shouldnt.

Could someone give me an idea as to what is wrong?

P.s not sure about the data types of the columns, currently the figures come in 3 decimal places.

Shab

 

[bborissov]

Can you post View definitions?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ashab02]

Sorry not sure what you mean by view definition?

 

[bborissov]

Post both views SELECT statements.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ashab02]

Hello

The first view code is as follows

SELECT     TOP 100 PERCENT SUM(dbo.Drep_Lib_ACTT.TTAMNT * 1.175) AS [Costs To Date], dbo.dbo_ACWR.WRSNUM
FROM         dbo.Drep_Lib_ACTS INNER JOIN
                      dbo.dbo_ACWR ON dbo.Drep_Lib_ACTS.TSBILL = dbo.dbo_ACWR.WRSNUM INNER JOIN
                      dbo.Drep_Lib_ACTT ON dbo.Drep_Lib_ACTS.TSPKEY = dbo.Drep_Lib_ACTT.TTIDNM
GROUP BY dbo.Drep_Lib_ACTS.TSSTAT, dbo.dbo_ACWR.WRSNUM
HAVING      (dbo.Drep_Lib_ACTS.TSSTAT = 2) AND (SUM(dbo.Drep_Lib_ACTT.TTAMNT * 1.175) > 0)

The second view code is as follows:

SELECT     TOP 100 PERCENT dbo.Drep_Lib_MF.TYPE, dbo.Drep_Lib_MF.MFDESC, dbo.dbo_CL.SURN, CONVERT(datetime, 
                      CONVERT(VARCHAR, YEAR(dbo.dbo_ACWB.WBDATE)) + '-' + CONVERT(VARCHAR, 
                      MONTH(dbo.dbo_ACWB.WBDATE)) + '-01') AS Expr1, 
                      SUM(dbo.dbo_TF.AMNT + ISNULL(dbo.REP_DRYDENS_BOARD_COSTS_TIME.[Costs To Date], 0)) AS Expr2
FROM         dbo.Drep_Lib_MF INNER JOIN
                      dbo.dbo_MT ON dbo.Drep_Lib_MF.TYPE = dbo.dbo_MT.TYPE INNER JOIN
                      dbo.dbo_CL ON dbo.dbo_MT.CKEY = dbo.dbo_CL.CODE INNER JOIN
                      dbo.dbo_ACWM INNER JOIN
                      dbo.dbo_TF ON dbo.dbo_ACWM.WMTFKY = dbo.dbo_TF.TFPKEY INNER JOIN
                      dbo.dbo_ACWB ON dbo.dbo_ACWM.WMPKEY = dbo.dbo_ACWB.WBPKEY ON 
                      dbo.dbo_MT.CODE = dbo.dbo_ACWB.WBCODE INNER JOIN
                      dbo.dbo_ACWR ON 
                      dbo.dbo_ACWB.WBPKEY = dbo.dbo_ACWR.WRDBNM LEFT OUTER JOIN
                      dbo.REP_DRYDENS_BOARD_COSTS_TIME ON 
                      dbo.dbo_ACWR.WRSNUM = dbo.REP_DRYDENS_BOARD_COSTS_TIME.WRSNUM
WHERE     (dbo.dbo_TF.TTYP IN ('cc', 'sc', 'eb', 'ebd', 'hx', 'hxd')) AND (NOT (dbo.dbo_TF.TFPKEY IN
                          (SELECT     tfpkey
                            FROM          REP_DRYDENS_BOARD_TRAN_EXCLUDE))) AND (dbo.dbo_ACWB.WBDATE > CONVERT(DATETIME, 
                      '2006-01-01 00:00:00', 102))
GROUP BY dbo.Drep_Lib_MF.TYPE, dbo.Drep_Lib_MF.MFDESC, dbo.dbo_CL.SURN, CONVERT(datetime, CONVERT(VARCHAR, 
                      YEAR(dbo.dbo_ACWB.WBDATE)) + '-' + CONVERT(VARCHAR, MONTH(dbo.dbo_ACWB.WBDATE)) + '-01'), 
                      MONTH(dbo.dbo_ACWB.WBDATE), YEAR(dbo.dbo_ACWB.WBDATE)
HAVING      (dbo.Drep_Lib_MF.MFDESC LIKE '%estate%')
ORDER BY dbo.Drep_Lib_MF.TYPE, YEAR(dbo.dbo_ACWB.WBDATE), MONTH(dbo.dbo_ACWB.WBDATE)

Hope this makes sense

 

[bborissov]

In second view you add first view [Cost to Date] for every record that have the same WRSNUM. I mean if you have two records with WRSNUM = 2 in dbo_ACWR you SUM [Cost to Date] twice

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ashab02]

I see what your saying, how do I overcome this problem?

 

[bborissov]

How about:

SUM(dbo.dbo_TF.AMNT) +
MAX(ISNULL(dbo.REP_DRYDENS_BOARD_COSTS_TIME.[Costs To Date], 0))) AS Expr2

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ashab02]

Thanks mate

i'll see what I can do with this

Much appreciated, you guys are the best.

 

[SQLBill]

If that doesn't work, post some sample data from both tables and what results you are getting and what results you feel you should get.

-SQLBill

Posting advice: FAQ481-4875