SQL Help

[MartinS]

OK, need some help here with SQ syntax. I'm good for the basics but get lost when trying to do more complex stuff, which is where you lot come in
I have been asked to update the statement below to only return rows where all three of the highlighted fields are NOT NULL.
I've managed to figure it out for the first two, but dealing with SUMS and NULLS is giving me a headache.

SELECT V.ValuationID AS VID, F.NomDiscRateAccruedPenWid AS FG3, F.PriceInflation AS FG21, SUM(AA.AssetPercentage) AS E1TO6 
FROM Valuations V 
INNER JOIN Financial F ON F.ValuationID = V.ValuationID 
INNER JOIN ValuationResults VR ON VR.ValuationID = V.ValuationID 
INNER JOIN LUAssetAllocation AA ON AA.ValuationID = V.ValuationID 
WHERE (F.InputStage = 2 OR F.InputStage = 1) 
AND AA.AssetCategoryID IN (1, 2, 3, 4, 5, 6) 
AND F.DiscountRateType = 0 
AND V.ValuationID IN (17, 1269, 1218, 1149, 1255, 854, 1240, 1204, 1136, 1135, 1175, 1235, 1237, 1191, 1260, 1185, 1128, 1257, 1173, 1202, 1270, 1096, 1201, 1148, 1186, 1187, 1182, 1141, 1119, 1120, 1227, 1248, 1194, 1261, 1192, 1127, 1092, 1213, 1244, 1179, 1176, 1219, 1123, 1143, 1064, 1174, 1107, 1108, 1231, 1170, 1180, 230, 1156, 1229, 1193, 1249, 1239, 1211, 1222, 1110, 947, 1259, 1212, 1165, 1241, 1267, 1268, 1089, 1159, 1161, 150, 1224, 614, 613, 1189, 1094, 1203, 1221, 730, 1130, 1215, 1250, 1234, 1095, 1049, 1184, 1133, 1162, 1086, 1124, 1256, 1228, 1152, 1220, 1134, 1232, 1197, 1230, 1258, 1236, 1196, 1217, 1208, 1209, 1065, 1153) 
GROUP BY V.ValuationID, F.InputStage, F.NomDiscRateAccruedPenWid, F.PriceInflation

For the first two fields, I could add in the following AND statement:

AND (F.NomDiscRateAccruedPenWid IS NOT NULL) AND (F.PriceInflation IS NOT NULL)

but that still gives me rows of data with the SUM column resulting in NULL.
Is this the best way to achieve this, or is there a better solution?
Any help/advice gratefully received.
Regards
Martin

[RonProwse]

try adding this to the end

HAVING SUM(AA.AssetPercentage) >0


or if you need to see 0 values


AND NOT ISNULL(SUM(AA.AssetPercentage))

[RonProwse]

Martin i would recommend the Transact-SQL Desk top Reference for MS SQL SERVER

Ron

[MartinS]

Thanks Ron.
I tried the first one, and it works, but the ISNULL function requires two parameters, so i added this to the end instead:

HAVING(ISNULL(SUM(AA.AssetPercentage),NULL)ISNOTNULL)
 

and it works a treat!
Thanks for the pointers.
Martin

[MartinS]

Originally Posted by RonProwse

Martin i would recommend the Transact-SQL Desk top Reference for MS SQL SERVER

Ron

Will have a hunt about and see if work want to buy it for me!
They have been promising to send me on a SQL course, so that may help a little.
Thanks again
Martin