List of randomised, transplantable Kidneys, lacking Recipients

[marshalc]

SELECT
  "WP4" || rt.centre_code ||
  CASE ra.list_code WHEN 1 THEN 0 WHEN 2 THEN 0 WHEN 3 THEN 9 WHEN 4 THEN 9 END ||
  substr('00' || cd.sequence_number, -2, 2) || co.location as TrialID,
  co.id,
  cr.id,
  ca.id
FROM compare_organ as co,
  compare_donor as cd,
  compare_randomisation as ra,
  compare_retrievalteam as rt
LEFT OUTER JOIN compare_recipient as cr
  ON co.id = cr.organ_id
LEFT OUTER JOIN compare_organallocation as ca
  ON ca.organ_id = co.id
WHERE co.donor_id = cd.id
  AND ra.donor_id = cd.id
  AND cd.retrieval_team_id = rt.id
  AND cr.id ISNULL
  AND co.transplantable = 1

WP441901L 13
WP441901R 14
WP444901L 29
WP444901R 30
WP456901L 41
WP457901L 43
WP445903R 52
WP451905L 55
WP452901L 61
WP456904L 87
WP456906L 105
WP456906R 106
WP452903L 115
WP451909L 131
WP445001L 225
WP441005L 231
WP441005R 232
WP456003L 249
WP443000L 253
WP443000R 254
WP445002L 255
WP445002R 256
WP452001L 257
WP452001R 258
WP451009R 264
WP453000L 265
WP453000R 266
WP456004L 269
WP443003L 271
WP451000L 273
WP451000R 274
WP451011L 281
WP451011R 282
WP452003R 286
WP443004L 289
WP443004R 290
WP441008L 293
WP441008R 294
WP441009L 297
WP456005L 299
WP456005R 300
WP457006L 301
WP457006R 302
WP451013L 303
WP446001L 307
WP446001R 308
WP451015L 309
WP451015R 310
WP455001L 313
WP455001R 314
WP453009L 315
WP453009R 316
WP457007L 319
WP457007R 320
WP457008R 322
WP453000L 323
WP453000R 324
WP453010L 325
WP457009R 330
WP451016R 332
WP441010L 337
WP441010R 338
WP457010L 339
WP457010R 340
WP441012L 345
WP453012L 353
WP456007L 355
WP456007R 356
WP443005L 359
WP443005R 360
WP446002L 363
WP446002R 364
WP457011R 366
WP453910R 368
WP457012L 369
WP457012R 370
WP457013L 371
WP457013R 372
WP457014L 373
WP457014R 374
WP457015R 378
WP452007L 379
WP456008R 382
WP456009L 385
WP443006L 387
WP452907R 394
WP445003L 401
WP445003R 402
WP446003L 403
WP446003R 404
WP441001L 147 12 WP453002R 180 154 WP443902R 64 169 WP441906R 86 170 WP446904R 98 171 WP451006R 218 183 WP453904R 108 184 WP452005L 291 202 WP452002L 259 203 WP441009R 298 212 WP453006R 244 213 WP453011L 341 226 WP441006L 261 249 WP441007L 277 250 WP441007R 278 251 WP454002R 390 253 WP453014L 383 255 WP454002L 389 256

[marshalc]

@mertenssarah to investigate what has happened to these cases and report...

[mertenssarah]

@AllyBradley Do you want me to let you know which cases were transplanted in a consented patient so you can prioritise these cases for the transplant technicians in UK to complete the online forms?

[mertenssarah]

@marshalc Could you investigate the following trial IDs:

WP453000L 265 WP453000R 266 WP443000L 253 WP443000R 254 WP451000L 273 WP451000R 274 WP453000L 323 WP453000R 324

These are not valid trial IDs. Maybe this has something to do with the error 500 things that have been happening ( #142 )

[mertenssarah]

@AllyBradley

Here is the list with kidneys transplanted in patients consented to the trial:

WP441901R 14 WP457901L 43 WP456904L 87 WP445001L 225 WP445002L 255 WP445002R 256 WP452001L 257 WP443003L 271 WP452003R 286 WP443004L 289 WP443004R 290 WP441009L 297 WP457006R 302 WP451013L 303 WP451015L 309 WP455001L 313 WP455001R 314 WP453009L 315 WP453009R 316 WP457009R 330 WP441010L 337 WP441010R 338 WP456007L 355 WP456007R 356 WP446002L 363 WP446002R 364 WP457012R 370 WP457014L 373 WP457015R 378 WP452007L 379 WP443006L 387 WP445003L 401 WP445003R 402 WP446003L 403 WP446003R 404 WP452005L 291 202 WP452002L 259 203 WP453011L 341 226 WP441006L 261 249 WP441007L 277 250 WP441007R 278 251 WP453014L 383 255

Here is the list of kidneys not transplanted/not allocated. Can I add this to the database or do you want the TTs in UK doing this? WP441901L 13 WP444901L 29 WP444901R 30 WP456901L 41 WP445903R 52 WP451905L 55 WP452901L 61 WP445903R 52 WP451905L 55 WP452901L 61 WP456906L 105 WP456906R 106 WP452903L 115 WP451909L 131 WP441005L 231 WP441005R 232 WP452001R 258 WP451009R 264 WP456004L 269 WP451011L 281 WP451011R 282 WP441008L 293 WP441008R 294 WP456005L 299 WP456005R 300 WP457007L 319 WP457007R 320 WP453010L 325 WP457010L 339 WP457010R 340 WP456008R 382 WP456009L 385 WP453002R 180 154

Here is the list of kidneys that were transplanted but in non-consented patients: WP456003L 249 WP457006L 301 WP446001L 307 WP453012L 353 WP457011R 366 WP453910R 368 WP457012L 369

Here is the list of kidneys that were allocated to a non-recruiting site (Do you want me to add these to the database?) WP446001R 308 WP451015R 310 WP441012L 345 WP443005R 360 WP441001L 147 12 WP443902R 64 169 WP441906R 86 170 WP446904R 98 171 WP451006R 218 183 WP453904R 108 184 WP441009R 298 212 WP453006R 244 213

There are also a few kidneys where we don't know yet if there is consent: At time of transplant it was not known if there was consent or consent was going to be asked later. I asked Aukje and Sijbrand to let me know if the patients did consent or not. No answer yet. WP457008R 322 WP451016R 332 WP443005L 359 WP457013L 371 WP457013R 372 WP457014R 374 WP452907R 394 WP454002L 389 256 WP454002R 390 253

[AllyBradley]

Thanks for this @mertenssarah The data entry will start today with the consented patients first. I'll get the TTs here to add all the data (including the non-transplanted ones) and will keep you updated with progress.

[marshalc]

These appear to be interesting failings of the SQL query trying to generate the Trial ID. By looking at the organ id (e.g. 265), we get the following from the system...

WP453000L 265 --> Is Not Randomised... need to work out why that isn't being filtered by the query WP453000R 266 --> Is Not Randomised WP443000L 253 --> Is Not Randomised WP443000R 254 --> Is Not Randomised WP451000L 273 --> Is Not Randomised WP451000R 274 --> Is Not Randomised WP453000L 323 --> Is Not Randomised WP453000R 324 --> Is Not Randomised