a guest Dec 14th, 2018 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT c.nom AS "Mutua",
  2. doctor.nom as "Doctor/Centro médico"
  3. p.nomPeticio AS "Caso",
  4. p.dataRecepcio AS "Fecha de recepcion",
  5. case when f.nomFactura is null then n.nomNota else f.nomFactura end AS "Factura/Nota",
  6. case when f.dataFi is null then n.dataFi else f.dataFi  end AS "Fecha Factura/Nota",
  7. CONCAT(pa.cognom1, " ", pa.cognom2, ", ", pa.nom) AS "Paciente",
  8. doctor.nom as 'Doctor',
  9. clini.nom as 'Clínica',
  10. GROUP_CONCAT(cc2.nom SEPARATOR ' / ') as 'Doctor relacionado',
  11. ie.text as 'Tipo de análisis',
  12. round(ap.tarifaUnitaria,2) AS 'Importe',
  13. ap.quantitat as 'Cantidad',
  14. ap.importTotal AS "Importe total",
  15. (CASE WHEN p.pagador = 'M' then 'Mutua'
  16. when p.pagador = 'D' then 'Doctor'
  17. when p.pagador = 'P' then 'Paciente'
  18. when p.pagador = 'A' then 'Otros'
  19. else ''
  20. end
  21. ) as 'Pagador'
  22. from Peticio p
  23. left join ClientsCC cc on cc.Peticio_codiPeticio = p.codiPeticio
  24. left join `Client` cc2 on cc.Client_codiClient = cc2.codiClient
  25. left join AnalisiPeticioFactura ap on p.codiPeticio = ap.Peticio_codiPeticio
  26. left join TipusAnalisi ta on ta.codiAnalisi = ap.TipusAnalisi_codiAnalisi
  27. left join I18nesES ie on ie.codiI18n = ta.nom
  28. left JOIN Factura f ON f.codiFactura = p.Factura_codiFactura
  29. left JOIN Nota n ON n.codiNota=p.Nota_codiNota
  30. LEFT JOIN Pacient pa ON p.Pacient_codiPacient = pa.codiPacient
  31. LEFT JOIN `Client` c ON c.codiclient = p.codiMutua
  32. left join `Client` doctor on p.codiDoctor=doctor.codiClient
  33. left join Clinica clini on clini.codiClinica = p.Clinica_codiClinica
  34. where ((YEAR(f.dataFi)= "[año de facturación (Ej:2017)]" AND MONTH(f.dataFi)="[mes de facturación (Ej:2)]")
  35. or (YEAR(n.dataFi)="[año de nota(Ej:2017)]" AND MONTH(n.dataFi)="[mes de nota (Ej:2)]"))
  36. AND (doctor.referencia ='[Código Doctor/Centro médico:]')
  37. group by ap.codiAnalisiPeticioFactura
  38. order by c.nom,p.dataRecepcio;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand