Changes between Version 3 and Version 4 of DAPS

Timestamp:

08/29/13 11:00:37 (12 years ago)

Author:

Richard Bramley

Comment:

--

DAPS

@@ -64 +64 @@
  a. {{{USP_UPDATE_MATCH_5A_NHS_NO_MATCH}}}
  a. {{{USP_UPDATE_MATCH_5B_CASENOTE_NUMBER_MATCH}}}
+
+
+== Specific Runs
+
+=== Graphic 2 Extract of S Numbers
+
+{{{#!sql
+WITH demo AS (
+SELECT 
+        [SYSTEM_NUMBER_CURRENT]
+    ,[FORENAMES]
+    ,[SURNAME]
+    ,[POSTCODE]
+    ,[GENDER]
+    ,[DATE OF BIRTH]
+    ,[BATCH_ID]
+    ,[MATCH_DEMOGRAPHIC_LEVEL]
+  FROM [DWPATMATCH].[dbo].[DBS_TRACING]
+)
+
+SELECT
+        male.FORENAMES
+        ,male.SURNAME
+        ,male.[DATE OF BIRTH]
+        ,ISNULL(postcode.POSTCODE, '') [PostCode]
+        ,ISNULL(male.SYSTEM_NUMBER_CURRENT, '') [Male_S_Number]
+        ,ISNULL(female.SYSTEM_NUMBER_CURRENT, '') [Female_S_Number]
+        ,ISNULL(postcode.SYSTEM_NUMBER_CURRENT, '') [Postcode_S_Number]
+    ,COALESCE(male.SYSTEM_NUMBER_CURRENT, female.SYSTEM_NUMBER_CURRENT, postcode.SYSTEM_NUMBER_CURRENT, '') [S Number]
+    ,CASE WHEN (male.SYSTEM_NUMBER_CURRENT <> female.SYSTEM_NUMBER_CURRENT)
+                        OR (male.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
+                        OR (female.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
+                        THEN 'Yes' ELSE '' END Mismatch
+FROM    demo male
+LEFT JOIN       demo female ON female.SURNAME = male.SURNAME
+                                                and female.FORENAMES = male.FORENAMES
+                                                and female.[DATE OF BIRTH] = male.[DATE OF BIRTH]
+                                                and female.BATCH_ID = 10930 and female.GENDER = 'f'
+LEFT JOIN       demo postcode ON postcode.SURNAME = male.SURNAME
+                                                and postcode.FORENAMES = male.FORENAMES
+                                                and postcode.[DATE OF BIRTH] = male.[DATE OF BIRTH]
+                                                and postcode.BATCH_ID = 10929
+WHERE   male.BATCH_ID = 10930 and male.GENDER = 'm'
+
+}}}