Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --from this
- SELECT * FROM
- (SELECT
- x.MutDatum,
- x.MutFactuur,
- x.MutBetalingskenmerk,
- x.Mut_Rel_ID,
- x.sBedrag,
- (CASE WHEN x.sBetaald IS NULL THEN 0 ELSE x.sBetaald END) AS sBetaald2
- FROM
- (SELECT
- MutDatum,MutFactuur,MutBetalingskenmerk,Mut_Rel_ID,SUM(MutBedragEX+MutBTW) AS sBedrag,
- (SELECT TOP 1 RelCode FROM tblBH_Relaties WHERE Rel_ID=Mut_Rel_ID) AS RelCode,
- (SELECT SUM(b.MutRekenBedrag) FROM tblBH_Mutaties b WHERE b.Mut_Ab_ID=71 AND b.MutPartNr=3 AND (b.MutSoort=7 OR b.MutSoort=3) AND b.MutFactuur=tblBH_Mutaties.MutFactuur AND b.Mut_Rel_ID=tblBH_Mutaties.Mut_Rel_ID) AS sBetaald
- FROM tblBH_Mutaties
- WHERE Mut_Ab_ID=71 AND MutPartNr=1 AND MutSoort=2
- GROUP BY MutDatum,MutFactuur,MutBetalingskenmerk,Mut_Rel_ID) AS X) AS Z WHERE z.sBetaald2<>z.sBedrag
- ORDER BY mutfactuur
- --to this
- SELECT MIN(MutDatum)AS MutDatum,MutFactuur
- ,
- MIN(MutBetalingskenmerk)AS MutBetalingskenmerk,
- Mut_Rel_ID
- , SUM(CASE WHEN MutPartNr=1 AND mutsoort = 2 THEN (mutbedragex+mutbtw) ELSE NULL END) AS topay
- , SUM(CASE WHEN MutPartNr=3 AND mutsoort IN (7, 3) THEN mutrekenbedrag ELSE 0 END) AS paid
- FROM tblBH_Mutaties
- WHERE Mut_Ab_ID = 71
- GROUP BY MutFactuur, Mut_Rel_ID
- HAVING (SUM(CASE WHEN MutPartNr=1 AND mutsoort = 2 THEN (mutbedragex+mutbtw) ELSE NULL END)<>SUM(CASE WHEN MutPartNr=3 AND mutsoort IN (7, 3) THEN mutrekenbedrag ELSE 0 END))
Advertisement
Add Comment
Please, Sign In to add comment