ckdmh/SQLQuerymakesfullinfodeathdate.sql at main · davidpohagan/ckdmh · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
drop table [Client_BTP_RW].[DOH101_fullinforanked]
SELECT mpi.[Pseudo_NHS_Number]
      ,mpi.[X_CCG_OF_REGISTRATION]
      ,mpi.[X_CCG_OF_RESIDENCE]
      ,mpi.[Dob]
      ,mpi.[Sex]
      ,mpi.[EthnicGroupAlgorithm]
      ,mpi.[EthnicMainGroup]
      ,mpi.[InterpreterRequired]
      ,mpi.[Deceased]
      ,[DeathDate]
      ,mpi.[LSOA_Code]
      ,mpi.[IMD_Score]
      ,mpi.[imd_numeric]
      ,[quintile_section]
       ,[Value]
      ,[Date]
      ,[First60Date]
      ,[First30Date]
      ,DATEDIFF(year, try_cast(mpi.[Dob]+'-01' as date),[Date]) as AgeatDate
      ,DATEDIFF(year, try_cast(mpi.[Dob]+'-01' as date), [DeathDate]) as AgeatDeath
      ,[ConceptID]
      ,[value_num]
      ,[hasCKD]
      ,[Label]
      ,[Isover90dunder60]
      ,[daysSinceFirst]
      ,[daysSinceFirst30]
      ,[DM_flag]
      ,[Dem_flag]
      ,[Dep_flag]
      ,[Shiz_flag]
      ,[BP_flag]
      ,[Adhd_flag]
      ,[Anx_flag]
      ,[Aut_flag]
      ,[bipolar_flag]


into [Client_BTP_RW].[DOH101_fullinforanked]

  FROM [Client_BTP_RW].[DOH003_MPI] as mpi

  left join [Client_BTP_RW].[DOH002_MPI] as mpi1
  on mpi1.[Pseudo_NHS_Number] = mpi.[Pseudo_NHS_Number]

  left join [Client_BTP_RW].[DOH101_rankedegfrckd_wholecohort] as rckd
  on rckd.[Pseudo_NHS_Number] =    mpi.[Pseudo_NHS_Number]

  left join [Client_BTP_RW].[DOH101_DM] as dm
  on rckd.[Pseudo_NHS_Number] = dm.[Pseudo_NHS_Number]

    left join [Client_BTP_RW].[DOH101_Dem] as dem
  on rckd.[Pseudo_NHS_Number] = dem.[Pseudo_NHS_Number]

   left join [Client_BTP_RW].[DOH101_Dep] as dep
  on rckd.[Pseudo_NHS_Number] = dep.[Pseudo_NHS_Number]


    left join [Client_BTP_RW].[DOH101_Schiz] as sz
  on rckd.[Pseudo_NHS_Number] = sz.[Pseudo_NHS_Number]
    left join [Client_BTP_RW].[DOH101_hypertension] as bp
  on rckd.[Pseudo_NHS_Number] = bp.[Pseudo_NHS_Number]

    left join [Client_BTP_RW].[DOH101_adhd] as ad
  on rckd.[Pseudo_NHS_Number] = ad.[Pseudo_NHS_Number]

    left join [Client_BTP_RW].[DOH101_anx] as ax
  on rckd.[Pseudo_NHS_Number] = ax.[Pseudo_NHS_Number]

    left join [Client_BTP_RW].[DOH101_aut] as au
  on rckd.[Pseudo_NHS_Number] = au.[Pseudo_NHS_Number]

    left join [Client_BTP_RW].[DOH101_bipol] as bi
  on rckd.[Pseudo_NHS_Number] = bi.[Pseudo_NHS_Number]


  order by [Pseudo_NHS_Number], [Date]