MORE SQL Help

[MartinS]

Whilst I'm still waiting to be sent on a SQL training course, I'm still having to SQL that appears to get harder!
Anyway, I am doing some selections for a charting application, and the data selected contains two records for the same ID, one is a 'preliminary' record and the other is a 'final' one. For some IDs there is only a preliminary record, for others there is both. My selection includes only records where each of the required fields are not NULL, but what i also need to do is select the final record (if present, otherwise the preliminary) for each ID. I tried to update my selection using the MAX function around the preliminary/final marker, and this works for most cases, except where the data for the preliminary record and the final record are different.
Do any of you SQL experts out ther know if there is a way to perform this selection?
Here is what i have so far:

SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, MAX(F.InputStage) AS ISTAGE
FROM Valuations V 
INNER JOIN Financial F ON F.ValuationID = V.ValuationID 
INNER JOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage 
INNER JOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage 
WHERE (F.InputStage = 1 OR F.InputStage = 2) 
AND (VR.DBLiabFunding IS NOT NULL) AND (VR.MPLiabFRS17OrIAS19 IS NOT NULL) AND (VD.PlanLenYears IS NOT NULL) 
GROUP BY V.ValuationID, VR.DBLiabFunding, VR.MPLiabFRS17OrIAS19, VD.PlanLenYears

Thanks in advance
Martin

[RonProwse]

try looking at either the DENSE RANK funtion or the RANK function

Ron

[MartinS]

Thanks Ron
Will do.
Martin

[uwila]

try the following:

SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, F.InputStage AS ISTAGE
FROM Valuations V
INNERJOIN

(
select COALESCE(F.ValuationID,P.ValuationID) ValuationID, COALESCE(F.InputStage,P.InputStage) InputStage
from
(select ValuationID, InputStage
from Financial F where (InputStage = 1)) P
left outer join
(select ValuationID, InputStage
from Financial F where (InputStage = 2)) F on F.ValuationID = P.ValuationID) F ON F.ValuationID = V.ValuationID

INNERJOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNERJOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage
WHERE (VR.DBLiabFunding ISNOTNULL)AND(VR.MPLiabFRS17OrIAS19 ISNOTNULL)AND(VD.PlanLenYears ISNOTNULL)

The bit in red creates a temporary table which contains rows for final records and prelim records if no final record exists. Note that if a final record exists without a prelim record, then this one will be excluded.

I haven't tested the full query, but the red bit does exactly what I said

Cheers, Chris

[Garf]

Id have just used a case statement like thus....



select

table1.field1,
table1.field2,
case table1.field3
when null then blahblah
else blahblah
end as fieldwhatever,
table1.field4

from

table 1

[uwila]

or even try the following simpler one:

SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, F.InputStage AS ISTAGE
FROM Valuations V
INNERJOIN

(select ValuationID, max(InputStage) InputStage
from Financial F
group by ValuationID) F ON F.ValuationID = V.ValuationID

INNERJOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNERJOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage
WHERE (VR.DBLiabFunding ISNOTNULL)AND(VR.MPLiabFRS17OrIAS19 ISNOTNULL)AND(VD.PlanLenYears ISNOTNULL)

The bit in red creates a temporary table which contains rows for final records and prelim records if no final record exists. Note that if a final record exists without a prelim record, then this one will be excluded.

I haven't tested the full query, but the red bit does exactly what I said

Cheers, Chris

[uwila]

Originally Posted by Garf

Id have just used a case statement like thus....



select

table1.field1,
table1.field2,
case table1.field3
when null then blahblah
else blahblah
end as fieldwhatever,
table1.field4

from

table 1

...there are no NULL records - there's always either a IS=1 record or both IS=1 and IS=2 records.

Anyhow, I've actually tested both of mine now, so there

[Garf]

Originally Posted by uwila

...there are no NULL records - there's always either a IS=1 record or both IS=1 and IS=2 records.

Anyhow, I've actually tested both of mine now, so there

ahh ok didn't see thr null bit.

[MartinS]

Originally Posted by uwila

or even try the following simpler one:

SELECT V.ValuationID AS VID, VR.DBLiabFunding AS JK7, VR.MPLiabFRS17OrIAS19 AS JK32, VD.PlanLenYears AS M12, F.InputStage AS ISTAGE
FROM Valuations V
INNERJOIN

(select ValuationID, max(InputStage) InputStage
from Financial F
group by ValuationID) F ON F.ValuationID = V.ValuationID

INNERJOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNERJOIN ValuationDocuments VD ON VD.ValuationID = V.ValuationID AND F.InputStage = VD.InputStage
WHERE (VR.DBLiabFunding ISNOTNULL)AND(VR.MPLiabFRS17OrIAS19 ISNOTNULL)AND(VD.PlanLenYears ISNOTNULL)

The bit in red creates a temporary table which contains rows for final records and prelim records if no final record exists. Note that if a final record exists without a prelim record, then this one will be excluded.

I haven't tested the full query, but the red bit does exactly what I said

Cheers, Chris

Thanks Chris - that works perfectly!
I tried to get my head around the RANK stuff, but the examples made no sense in the context i was working in, and eventully ended up with a huge selection that found all the 2s which matched my criteria, and joined them to the 1s with the same criteria not in the list of 2s! I like your way much more!!!
Thanks again Chris - green incoming.
Martin

[MartinS]

Small problem - although it works perfectly with the specific selection, there are another 8 that need the same change, but using the same code it doesn't work when one of more of the of the fields I'm checking against are from the Financial table and even though I've spent the morning messing around with the SQL to try and get it to work, I can't

I also noticed that there could be a time when the user wanted to show Final records only, so modified the temporary selection code (where required) as follows:

INNER JOIN (SELECT ValuationID, MAX(InputStage) AS IStage
FROM Financial F GROUP BY ValuationID) AS FIN ON FIN.ValuationID = V.ValuationID AND IStage = 2

I have a fixed piece of SQL for each selection, and can append the extra code using string.Format when the condition is true, but I just want to check that this is the right place to insert this?
Thanks in advance
Martin