No longer grouping correctly

For some reason, rmsacctnum is not being grouped and the 2 results I get should be added together at the end for , same for Sum rmstranamt and Sum rmstranamt 10 like in my 2nd query below. The first query is where the problem lies:

SELECT rm.rmsacctnum,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
SUM(rf10.rmstranamt10) AS [Sum rmstranamt 10],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND SUM(rf10.rmstranamt10) <> 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND SUM(rf10.rmstranamt10) = 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance

FROM RMASTER rm

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rf.rmstrancde IN ('10', '16','18','19','20','21','22','29','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
AND rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance, rf.rmstranamt
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

OUTPUT:

-
4313030999894992 85.00 3265.12 3180.12 3528.43 <-- rmsacctnum is not grouped anymore!
4313030999894992 178.31 3265.12 3086.81 3528.43

Before I added the CASE Statements I had this and it was grouping and summing fine:

SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],
cb.CurrentBalance
FROM RMASTER rm

INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM

WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

OUTPUT:

-

4313030999894992 263.31 6530.24 6266.93 3528.43

SOLVED IT

I had some missing SUM in my Case. Then took out rf.rmstranamt in my GROUP BY

No data in Data Mining cube.

Hi,

I've created a Cluster data mining model and seems to correctly return data. However I've created a data mining dimension and cube but when I go to query the cube it doesn't return any data if I select any members on the data mining dimension. Any suggestions to where I can look to resolve this?

Thanks

Sanjay

Hi,

I found out how to see the data in the cube. I was using the data mining dimension in relation with the dimension it was based on. So now I can see data for the years that I've trained my model on. Is there anyway to make the cube predict the values for the other years in the cube with data?

Thanks

Sanjay

|||

Are you saying that for example you've created a cluster model on a set of items, and you want to see the cluster values for items not in the set? And you want the slicing behavior to work against those values as well?

If this is the case, unfortunately, that's not the way it works. The cluster dimension is only on the cases for which it was trained. You could apply the clustering to the data outside the cube, write the results to a table and process it as a regular dimension, though.

|||

Hi,

Ideally yes. For example if I generate a cluster model and train it with a couple of years data, potentially I would then like to use the data mining dimension and cube to predict or classify the current years data. I will look to use your suggestion.

Thanks

Sanjay

NewLine and Carriage return issue in PDF format

If I insert the following expression in a textbox it will display
correctly in HTML format but not in PDF format. Can anyone show me how
to accomplish the same thing so that it will display correctly in both
HTML and and PDF format.
vbCrLf & vbCrLf & vbCrLf & "My Text"
In HTML format, it will display "My Text" on the 4th "row". Whereas in
PDF format, it displays "My Text" on the first row of the textbox.This has to be an issue (bug) with PDF and how it formats initial blank
space before the literal text. I am not sure if this is addressed in a
service pack for 2000 or in 2005 (I am using 2000 to test this) but here is
a workaround that I found that places an ASCII record seperator in the first
position before the CRLF's as:
=CHR(30)&vbCrLf & vbCrLf & vbCrLf & "My Text"
That should work.
Rodney Landrum
<Nergock@.gmail.com> wrote in message
news:1143588675.322590.215140@.u72g2000cwu.googlegroups.com...
> If I insert the following expression in a textbox it will display
> correctly in HTML format but not in PDF format. Can anyone show me how
> to accomplish the same thing so that it will display correctly in both
> HTML and and PDF format.
> vbCrLf & vbCrLf & vbCrLf & "My Text"
> In HTML format, it will display "My Text" on the 4th "row". Whereas in
> PDF format, it displays "My Text" on the first row of the textbox.
>