SELECT c.nom AS "Mutua",
doctor.nom as "Doctor/Centro médico"
p.nomPeticio AS "Caso",
p.dataRecepcio AS "Fecha de recepcion",
case when f.nomFactura is null then n.nomNota else f.nomFactura end AS "Factura/Nota",
case when f.dataFi is null then n.dataFi else f.dataFi  end AS "Fecha Factura/Nota",
CONCAT(pa.cognom1, " ", pa.cognom2, ", ", pa.nom) AS "Paciente",
doctor.nom as 'Doctor',
clini.nom as 'Clínica',
GROUP_CONCAT(cc2.nom SEPARATOR ' / ') as 'Doctor relacionado',
ie.text as 'Tipo de análisis',
round(ap.tarifaUnitaria,2) AS 'Importe',
ap.quantitat as 'Cantidad',
ap.importTotal AS "Importe total",
(CASE WHEN p.pagador = 'M' then 'Mutua'
when p.pagador = 'D' then 'Doctor'
when p.pagador = 'P' then 'Paciente'
when p.pagador = 'A' then 'Otros'
else ''
end
) as 'Pagador'
from Peticio p
left join ClientsCC cc on cc.Peticio_codiPeticio = p.codiPeticio
left join `Client` cc2 on cc.Client_codiClient = cc2.codiClient
left join AnalisiPeticioFactura ap on p.codiPeticio = ap.Peticio_codiPeticio
left join TipusAnalisi ta on ta.codiAnalisi = ap.TipusAnalisi_codiAnalisi
left join I18nesES ie on ie.codiI18n = ta.nom
left JOIN Factura f ON f.codiFactura = p.Factura_codiFactura
left JOIN Nota n ON n.codiNota=p.Nota_codiNota
LEFT JOIN Pacient pa ON p.Pacient_codiPacient = pa.codiPacient
LEFT JOIN `Client` c ON c.codiclient = p.codiMutua
left join `Client` doctor on p.codiDoctor=doctor.codiClient
left join Clinica clini on clini.codiClinica = p.Clinica_codiClinica
where ((YEAR(f.dataFi)= "[año de facturación (Ej:2017)]" AND MONTH(f.dataFi)="[mes de facturación (Ej:2)]")
or (YEAR(n.dataFi)="[año de nota(Ej:2017)]" AND MONTH(n.dataFi)="[mes de nota (Ej:2)]"))
AND (doctor.referencia ='[Código Doctor/Centro médico:]')
group by ap.codiAnalisiPeticioFactura
order by c.nom,p.dataRecepcio;