DefconDotNet

Untitled

Apr 19th, 2013
103
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.37 KB | None | 0 0
  1. --from this
  2.  
  3. SELECT * FROM
  4. (SELECT
  5.     x.MutDatum,
  6.     x.MutFactuur,
  7.     x.MutBetalingskenmerk,
  8.     x.Mut_Rel_ID,
  9.     x.sBedrag,
  10.     (CASE WHEN x.sBetaald IS NULL THEN 0 ELSE x.sBetaald END) AS sBetaald2
  11.     FROM
  12.     (SELECT
  13.         MutDatum,MutFactuur,MutBetalingskenmerk,Mut_Rel_ID,SUM(MutBedragEX+MutBTW) AS sBedrag,
  14.         (SELECT TOP 1 RelCode FROM tblBH_Relaties WHERE Rel_ID=Mut_Rel_ID) AS RelCode,
  15.         (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
  16.  
  17.  FROM tblBH_Mutaties
  18.  WHERE Mut_Ab_ID=71 AND MutPartNr=1 AND MutSoort=2
  19.  GROUP BY MutDatum,MutFactuur,MutBetalingskenmerk,Mut_Rel_ID) AS X) AS Z  WHERE  z.sBetaald2<>z.sBedrag
  20.  ORDER BY mutfactuur
  21.  
  22.  
  23.  
  24. --to this
  25.  
  26. SELECT MIN(MutDatum)AS MutDatum,MutFactuur
  27.     ,
  28.       MIN(MutBetalingskenmerk)AS MutBetalingskenmerk,
  29.     Mut_Rel_ID
  30.     , SUM(CASE WHEN MutPartNr=1 AND mutsoort = 2 THEN (mutbedragex+mutbtw) ELSE NULL END) AS topay
  31.     , SUM(CASE WHEN MutPartNr=3 AND mutsoort IN (7, 3) THEN mutrekenbedrag ELSE 0 END) AS paid
  32. FROM tblBH_Mutaties
  33. WHERE Mut_Ab_ID = 71
  34. GROUP BY MutFactuur, Mut_Rel_ID
  35. 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