Query per pazienti con problemi di prostata

Come al solito mi rivolgo ai colleghi esperti di sql per postgresql. Mi servirebbe una query con la seguente impostazione:

1. Pazienti di sesso maschile con età superiore a 50 anni (numero complessivo)
   
2. Che hanno attivato un problema di: IPB e/o prostatite (ICD9 da 600 a 602) e/o K in situ (ICD9: 233.4) e/o k prostata (ICD9: 185)
   
3. Che hanno in terapia almeno una confezione di farmaci appartenenti alla categoria G04C% 
   

Ringrazio anticipatamente chi potrà darmi una mano.

Prova con :

Select  InitCap( u.nome)  As  mmg ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And Days( p.nascita , Today())*24/8766 > 49.9 )
                      _Assisititi_M_over_50  ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And b.cp_code ILike '600%' And Not ( b.pb_status = 'I' And b.modalita = 'A' )))
                      _BPH_ ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And b.cp_code ILike '601%' And Not ( b.pb_status = 'I' And b.modalita = 'A' )))
                      _Prostatite_ ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And b.cp_code ILike '602%' And Not ( b.pb_status = 'I' And b.modalita = 'A' )))
                      _Prost_altre_Patologie_ ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And b.cp_code ILike '233.4%' And Not ( b.pb_status = 'I' And b.modalita = 'A' )))
                       _Prost_K_in_situ_ ,
(Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M' And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And b.cp_code ILike '185%' And Not ( b.pb_status = 'I' And b.modalita = 'A' )))
                      _Prost_K_ ,
( Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M'
And p.codice IN ( Select b.codice From cart_pazpbl b Where b.codice = p.codice And ( b.cp_code ILike '600%' Or b.cp_code ILike '601%' Or b.cp_code ILike '602%' Or b.cp_code ILike '233.4%' Or b.cp_code ILike '185%' ) And Not ( b.pb_status = 'I' And b.modalita ='A' )))
                        _BPH_Prost_K_ ,
Case  WHEN  ( Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M'
And p.codice IN ( Select t.codice From cart_terap t Where t.codice = p.codice And t.co_atc ILike 'G04C%' And t.te_c_flag = 'C' And Left( te_tipo, 1) = 'C' ))      > 0              THEN  
( Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M'
And p.codice IN ( Select t.codice From cart_terap t Where t.codice = p.codice And t.co_atc ILike 'G04C%' And t.te_c_flag = 'C' And Left( te_tipo, 1) = 'C' ))                         ELSE
( Select Cast( Count( Distinct p.codice) As SmallInt ) From pazienti p Left Outer Join nos_002 n On p.codice = n.codice Where n.pa_medi = u.userid And p.pa_convenzione = 'S' And ( n.pa_drevoca Is Null Or n.pa_drevoca > Today() Or ( n.pa_drevoca Between Date( Today())-365 And Today() And n.motivo_revoca = 'S')) And p.decesso Is Null And p.sesso = 'M'
And p.codice IN ( Select t.codice From cart_terap t Where t.codice = p.codice And t.co_atc ILike 'G04C%' And t.data_open Between Date( Today())-250 And Today()))     END
                           _Prost_Therapy_
FROM   v_utenti u  
ORDER BY  1

                    

                     

                     

                     

 

      


       





 


Verifica e adattala alle Tue esigenze ( eliminando o disattivando con /* ....  */  le colonne superflue )


Ti saluto ,  Sergio

Grazie Sergio.
Funziona benissimo.