Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- tblCurrent
- Ref | CustomerID | ... .. .. . . |
- X001
- X002
- X003
- tblHistorical
- Ref | ... .. .. . . | Missing | Matched
- X001 | ... .. .. . . | TRUE | FALSE
- X001 | ... .. .. . . | FALSE | FALSE
- X002 | ... .. .. . . | TRUE | TRUE
- X002 | ... .. .. . . | TRUE | FALSE
- X003 | ... .. .. . . | FALSE | FALSE
- X003 | ... .. .. . . | TRUE | TRUE
- SELECT a.ref,
- SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
- SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
- SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
- FROM tblCurrent a
- LEFT JOIN tblHistorical b
- on a.ref = b.ref
- GROUP BY a.ref
- SELECT R.Ref,
- SUM(CASE WHEN H.Missing = TRUE THEN 1 ELSE 0 END) as MissingTrue,
- SUM(CASE WHEN H.Missing = FALSE THEN 1 ELSE 0 END) as MissingFalse,
- SUM(CASE WHEN H.Matched = TRUE THEN 1 ELSE 0 END) as MatchedTrue
- FROM tblRef R JOIN tblHistorical H
- ON R.Ref = H.Ref
- SELECT R.Ref,
- SUM(CASE WHEN H.Missing = TRUE THEN 1 ELSE 0 END) as MissingTrue,
- SUM(CASE WHEN H.Missing = FALSE THEN 1 ELSE 0 END) as MissingFalse,
- SUM(CASE WHEN H.Matched = TRUE THEN 1 ELSE 0 END) as MatchedTrue
- FROM tblRef R
- INNER JOIN tblHistorical H
- ON R.Ref = H.Ref
- Group by R.Ref
- SELECT T.REF,
- COUNT(CASE WHEN H.MISSING = 'TRUE' THEN 1 END) AS MISSINGTRUE,
- COUNT(CASE WHEN H.MISSING = 'FALSE' THEN 1 END) AS MISSINGFALSE,
- COUNT(CASE WHEN H.MATCHED = 'TRUE' THEN 1 END) AS MATCHEDTRUE
- FROM TBLREF T
- JOIN TBLHISTORICAL H
- ON R.REF = H.REF
- GROUP BY T.REF
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement