Obiettivi di salute
4 partecipanti
Pagina 1 di 1
Obiettivi di salute
Dott.Hiram Mar 6 Nov 2012 - 11:35
Per la mia ASL dovrei estrarre:
-Pazienti con diagnosi di scompenso cardiaco e valori di creatininemia, sodio, potassio dall'01/01/2012 al 30/11/2012 con data delle determinazioni e valori;
-Pazienti diabetici in carico al 31/03/2012 con data e valori della emoglobina glicata.
Grazie per l'aiuto.
-Pazienti con diagnosi di scompenso cardiaco e valori di creatininemia, sodio, potassio dall'01/01/2012 al 30/11/2012 con data delle determinazioni e valori;
-Pazienti diabetici in carico al 31/03/2012 con data e valori della emoglobina glicata.
Grazie per l'aiuto.
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Cervino Gio 8 Nov 2012 - 1:09
Nella mia ASL utilizziamo per lo Scompenso, una query del tipo :
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( l.data_open)) Is Null Then 0 Else Max( Year( l.data_open)) End
From cart_problemi l Where l.codice = p.codice
And ( l.cp_code Like '428%' Or l.cp_code Like '429.3%' Or l.cp_code Like '425.4%'))
_Scompenso_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Creatinina' And a.ac_val Is Not Null And a.data_open Between query_d-365
And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice
And a1.ac_des Like 'Creatinina' And (a1.data_open > a.data_open
Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ) )
_creatinina_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,2)) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid > a.rowid)) And a1.ac_val Is Not Null ))
_creatinina_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And ( a.ac_des Like 'Sodio' And a.ac_val Is Not Null ) And a.data_open Between query_d-365
And query_d And Not Exists ( Select a1.codice From cart_accert a1 Where a1.codice = a.codice
And a1.ac_des Like 'Sodio' And (a1.data_open > a.data_open Or (a1.data_open = a.data_open
And a1.rowid > a.rowid)) And a1.ac_val Is Not Null) )
_sodio_d_,
(Select Cast( Max( a.ac_val) As Int) From cart_accert a Where a.codice=p.codice And ( a.ac_des
Like 'Sodio' And a.ac_val Is Not Null ) And a.data_open Between query_d-365 And query_d
And Not Exists ( Select a1.codice From cart_accert a1
Where a1.codice = a.codice And a1.ac_des Like 'Sodio' And (a1.data_open > a.data_open
Or (a1.data_open = a.data_open And a1.rowid > a.rowid)) And a1.ac_val Is Not Null) )
_Sodio_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Potassio' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Potassio' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d )
_potassio_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,2))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Potassio' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ))
_potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso < query_d )
Order By 4
e per il Diabete :
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( b.data_open)) Is Null Then 0 Else Max( Year( b.data_open)) End
From cart_pazpbl b Where b.codice = p.codice And b.cp_code Like '250%' And Not (b.pb_status = 'I' And b.modalita ='A') And b.certezza Not Like 'S' And b.data_open < '2012-03-31')
_DM_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d )
_HbA1c_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,1))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ))
_Hba1c_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _DM_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso < query_d )
Order By 4
Ovviamente prima dell' estrazione finale, eliminare la colonna : p.cognome + ' ' + p.nome As assistito,
Saluti , Sergio Tomasini , Orzivecchi ( BS )
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( l.data_open)) Is Null Then 0 Else Max( Year( l.data_open)) End
From cart_problemi l Where l.codice = p.codice
And ( l.cp_code Like '428%' Or l.cp_code Like '429.3%' Or l.cp_code Like '425.4%'))
_Scompenso_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Creatinina' And a.ac_val Is Not Null And a.data_open Between query_d-365
And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice
And a1.ac_des Like 'Creatinina' And (a1.data_open > a.data_open
Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ) )
_creatinina_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,2)) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid > a.rowid)) And a1.ac_val Is Not Null ))
_creatinina_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And ( a.ac_des Like 'Sodio' And a.ac_val Is Not Null ) And a.data_open Between query_d-365
And query_d And Not Exists ( Select a1.codice From cart_accert a1 Where a1.codice = a.codice
And a1.ac_des Like 'Sodio' And (a1.data_open > a.data_open Or (a1.data_open = a.data_open
And a1.rowid > a.rowid)) And a1.ac_val Is Not Null) )
_sodio_d_,
(Select Cast( Max( a.ac_val) As Int) From cart_accert a Where a.codice=p.codice And ( a.ac_des
Like 'Sodio' And a.ac_val Is Not Null ) And a.data_open Between query_d-365 And query_d
And Not Exists ( Select a1.codice From cart_accert a1
Where a1.codice = a.codice And a1.ac_des Like 'Sodio' And (a1.data_open > a.data_open
Or (a1.data_open = a.data_open And a1.rowid > a.rowid)) And a1.ac_val Is Not Null) )
_Sodio_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Potassio' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Potassio' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d )
_potassio_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,2))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Potassio' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ))
_potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso < query_d )
Order By 4
e per il Diabete :
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( b.data_open)) Is Null Then 0 Else Max( Year( b.data_open)) End
From cart_pazpbl b Where b.codice = p.codice And b.cp_code Like '250%' And Not (b.pb_status = 'I' And b.modalita ='A') And b.certezza Not Like 'S' And b.data_open < '2012-03-31')
_DM_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d )
_HbA1c_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,1))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null ))
_Hba1c_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _DM_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso < query_d )
Order By 4
Ovviamente prima dell' estrazione finale, eliminare la colonna : p.cognome + ' ' + p.nome As assistito,
Saluti , Sergio Tomasini , Orzivecchi ( BS )
Dott.Hiram ha scritto:Per la mia ASL dovrei estrarre:
-Pazienti con diagnosi di scompenso cardiaco e valori di creatininemia, sodio, potassio dall'01/01/2012 al 30/11/2012 con data delle determinazioni e valori;
-Pazienti diabetici in carico al 31/03/2012 con data e valori della emoglobina glicata.
Grazie per l'aiuto.
Cervino- Membro Junior
- Messaggi : 245
Punti : 5092
Voti per importanza dei messaggi : 22
Data d'iscrizione : 03.03.11
Età : 69
Località : Orzivecchi (BS)
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Dott.Hiram Ven 9 Nov 2012 - 12:01
Che strano: mi estrae anche qualche deceduto. (solo alcuni).
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Admin Ven 9 Nov 2012 - 18:18
Dott.Hiram ha scritto:Che strano: mi estrae anche qualche deceduto. (solo alcuni).
Per escludere tutti i deceduti il penultimo rigo devi sostituirlo con:
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Admin- Admin
- Messaggi : 537
Punti : 5503
Voti per importanza dei messaggi : 89
Data d'iscrizione : 20.02.11
Località : CASERTA -
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Dott.Hiram Sab 24 Nov 2012 - 10:53
Allora:
-Per il diabete tutto O.K.;
-Per lo scompenso estrae i valori di creatinina ma non i valori di sodio e potassio.
-Per il diabete tutto O.K.;
-Per lo scompenso estrae i valori di creatinina ma non i valori di sodio e potassio.
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Admin Sab 24 Nov 2012 - 19:04
Per lo scompenso prova questa:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select if Max( Year (s.data_open)) Is Null Then 0 Else Max( Year (s.data_open)) Endif
From cart_problemi s Where s.codice = p.codice
And (s.cp_code Like '428%' Or s.cp_code Like '429.3%' Or s.cp_code Like '425.4%'))
_Scompenso_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_creatinina,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Creatinina_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_sodio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Sodio_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_potassio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
N.b. Ricorda che valori di 0 equivalgono a "norm." e 90 "Fuori norma"
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select if Max( Year (s.data_open)) Is Null Then 0 Else Max( Year (s.data_open)) Endif
From cart_problemi s Where s.codice = p.codice
And (s.cp_code Like '428%' Or s.cp_code Like '429.3%' Or s.cp_code Like '425.4%'))
_Scompenso_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_creatinina,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Creatinina_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_sodio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Sodio_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_potassio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
N.b. Ricorda che valori di 0 equivalgono a "norm." e 90 "Fuori norma"
Admin- Admin
- Messaggi : 537
Punti : 5503
Voti per importanza dei messaggi : 89
Data d'iscrizione : 20.02.11
Località : CASERTA -
Re: Obiettivi di salute
Dott.Hiram Dom 25 Nov 2012 - 10:43
Mi da il seguente errore :
"SQL state = 37000
General error : alias '_' is not unique
select disinct u.codice_regionale As mmg_code, cast ('2012-12-31'As date) As query_d,p.cognome + ' ' + nome As assistito, cast(If p.sesso Like'
"SQL state = 37000
General error : alias '_' is not unique
select disinct u.codice_regionale As mmg_code, cast ('2012-12-31'As date) As query_d,p.cognome + ' ' + nome As assistito, cast(If p.sesso Like'
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Admin Dom 25 Nov 2012 - 10:48
Basta cancellare dopo _Creatinina_ e dopo _Sodio_ ' '_, e tutto procede.
Admin- Admin
- Messaggi : 537
Punti : 5503
Voti per importanza dei messaggi : 89
Data d'iscrizione : 20.02.11
Località : CASERTA -
Re: Obiettivi di salute
Dott.Hiram Dom 25 Nov 2012 - 12:18
Grazie Giuseppe, ho appllicato le correzioni che mi hai indicato nella query ma comunque mi tira fuori un errore SQL, che ti riporto di seguito:
ERRORE SQL:
SQLSTATE = 37000
[Sybase][ODBC Driver][Adaptive Server Anywhere] Syntax error or access violation: near ' ' in ...(12)) As cod reg, [ ] p.nascita, p....
Select Distinct u.codice_regionale As mmg_code, Cast ('2012-12-31' As date) As query_d, p.cognome + '
Per completezza allego anche la query completa con le correzioni che mi avevi suggerito precedentemente:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select if Max( Year (s.data_open)) Is Null Then 0 Else Max( Year (s.data_open)) Endif
From cart_problemi s Where s.codice = p.codice
And (s.cp_code Like '428%' Or s.cp_code Like '429.3%' Or s.cp_code Like '425.4%'))
_Scompenso_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_creatinina,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Creatinina_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_sodio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Sodio_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_potassio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
Molte grazie
ERRORE SQL:
SQLSTATE = 37000
[Sybase][ODBC Driver][Adaptive Server Anywhere] Syntax error or access violation: near ' ' in ...(12)) As cod reg, [ ] p.nascita, p....
Select Distinct u.codice_regionale As mmg_code, Cast ('2012-12-31' As date) As query_d, p.cognome + '
Per completezza allego anche la query completa con le correzioni che mi avevi suggerito precedentemente:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select if Max( Year (s.data_open)) Is Null Then 0 Else Max( Year (s.data_open)) Endif
From cart_problemi s Where s.codice = p.codice
And (s.cp_code Like '428%' Or s.cp_code Like '429.3%' Or s.cp_code Like '425.4%'))
_Scompenso_, ' '_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_creatinina,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Creatinina' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Creatinina_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_sodio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Sodio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Sodio_,
(Select max(a.data_open) From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND a1.data_open >a.data_open And a1.ac_val >'')) d_potassio,
(Select IF max(ac_val) like 'norm%' THEN CAST('0' as DEC (5,2))
ELSE IF max(ac_val) like '%norma' THEN CAST('99' as DEC (5,2))
ELSE If CharIndex(',', Max(ac_val))>0 Then Cast( SubString( Max( ac_val),1,( CharIndex(',', Max( ac_val)) - 1 )) + '.' + SubString( Max( ac_val), ( CharIndex(',', Max( ac_val)) +1 )) As Dec (5,2)) Else Cast( Max( a.ac_val) As Dec (5,2)) Endif Endif Endif From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Potassio' And a.ac_val >'' And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Creatinina' AND STRING (DATEFORMAT(a1.data_open, 'YYYYMMDD'), a1.data_upd) >STRING (DATEFORMAT(a.data_open, 'YYYYMMDD'), a.data_upd) And a1.ac_val >''))_Potassio_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _scompenso_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
Molte grazie
Dott.Hiram- Membro Senior
- Messaggi : 648
Punti : 5678
Voti per importanza dei messaggi : 15
Data d'iscrizione : 21.02.11
Re: Obiettivi di salute
Admin Dom 25 Nov 2012 - 19:35
Prova a sostituire le prime righe con:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg, p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
Oppure
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito, p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
Oppure
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
Almeno una di queste deve funzionare.
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg, p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
Oppure
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d, p.cognome + ' ' + p.nome As assistito, p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
Oppure
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
Almeno una di queste deve funzionare.
Admin- Admin
- Messaggi : 537
Punti : 5503
Voti per importanza dei messaggi : 89
Data d'iscrizione : 20.02.11
Località : CASERTA -
Re: Obiettivi di salute
lucapuccetti Sab 1 Dic 2012 - 11:55
Scusate la query
non mi va mi ritorna
error SQL
mi dice che table or View not found
correlation name 'b' not found
ho la versione 13.38
e' un problema di struttura del database con questa versione?
grazie
Luca Puccetti Pisa
- Codice:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( b.data_open)) Is Null Then 0 Else Max( Year( b.data_open)) End
From cart_pazpbl b Where b.codice = p.codice And b.cp_code Like '250%' And Not (b.pb_status = 'I' And b.modalita ='A') And b.certezza Not Like 'S' And b.data_open < '2012-03-31') _DM_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d ) _HbA1c_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,1))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )) _Hba1c_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _DM_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
non mi va mi ritorna
error SQL
mi dice che table or View not found
correlation name 'b' not found
ho la versione 13.38
e' un problema di struttura del database con questa versione?
grazie
Luca Puccetti Pisa
lucapuccetti- Nuovo Membro
- Messaggi : 44
Punti : 4771
Voti per importanza dei messaggi : 10
Data d'iscrizione : 02.07.11
Re: Obiettivi di salute
Admin Sab 1 Dic 2012 - 12:39
lucapuccetti ha scritto:Scusate la query
- Codice:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
Convert( VarChar(15), p.nascita, 111) As nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select Case When Max( Year( b.data_open)) Is Null Then 0 Else Max( Year( b.data_open)) End
From cart_pazpbl b Where b.codice = p.codice And b.cp_code Like '250%' And Not (b.pb_status = 'I' And b.modalita ='A') And b.certezza Not Like 'S' And b.data_open < '2012-03-31') _DM_,
(Select Cast( Max( a.data_open) As Char(15)) From cart_accert a Where a.codice=p.codice
And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d ) _HbA1c_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,1))
From cart_accert a Where a.codice=p.codice And a.ac_des Like 'Emoglo%Glicata' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )) _Hba1c_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _DM_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 4
non mi va mi ritorna
error SQL
mi dice che table or View not found
correlation name 'b' not found
ho la versione 13.38
e' un problema di struttura del database con questa versione?
grazie
Luca Puccetti Pisa
Prova questa:
Select Distinct u.codice_regionale As mmg_code, Cast( '2012-12-31' As Date) As query_d,
p.cognome + ' ' + p.nome As assistito,
Cast( If p.sesso Like 'M' Then Substr( p.codice_fiscale, 6, 4) || Substr( n.pa_uslcode, 3, 4) Else Substr( n.pa_uslcode, 3, 4) || Substr( p.codice_fiscale, 6, 4) Endif As Char (12)) As cod_reg,
p.nascita, p.sesso, p.provincia_nascita As prov_nascita,
(Select if Max( Year( b.data_open)) Is Null Then 0 Else Max( Year( b.data_open)) Endif
From cart_pazpbl b Where b.codice = p.codice And b.cp_code Like '250%' And Not (b.pb_status = 'I' And b.modalita ='A') And b.certezza Not Like 'S' And b.data_open < '2012-03-31') _DM_,
(Select Max( a.data_open) From cart_accert a Where a.codice=p.codice
And a.ac_des Like '%Emoglo%Glicata%' And a.ac_val Is Not Null And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )
And a.data_open Between query_d-365 And query_d ) _HbA1c_d_,
(Select Cast( Max( If Locate(a.ac_val,',') > 0 Then Left(a.ac_val, Locate(a.ac_val,',')-1)+'.'+ Right(a.ac_val, Length(a.ac_val) - Locate(a.ac_val, ',')) Else a.ac_val Endif ) As Dec(5,1))
From cart_accert a Where a.codice=p.codice And a.ac_des Like '%Emoglo%Glicata%' And a.ac_val Is Not Null And a.data_open Between query_d-365 And query_d And Not Exists (Select a1.codice From cart_accert a1 Where a1.codice=a.codice And a1.ac_des Like 'Emoglo%Glicata' And (a1.data_open > a.data_open Or (a1.data_open=a.data_open And a1.rowid>a.rowid)) And a1.ac_val Is Not Null )) _Hba1c_
FROM ( pazienti p Left Outer Join nos_002 n On p.codice = n.codice )
Left Outer Join v_utenti u On n.pa_medi = u.userid
Where mmg_code Like '%' And u.nome Like '%'
And p.pa_convenzione = 'S'
And _DM_ > ' '
And ( pa_drevoca Is Null Or pa_drevoca > query_d Or ( pa_drevoca Between query_d-365
And query_d And motivo_revoca = 'S')) And ( pa_dscelta Is Null Or pa_dscelta < query_d )
And p.nascita < query_d And ( p.decesso Is Null Or p.decesso > query_d )
Order By 3
Admin- Admin
- Messaggi : 537
Punti : 5503
Voti per importanza dei messaggi : 89
Data d'iscrizione : 20.02.11
Località : CASERTA -
Argomenti simili» OBIETTIVI DI SALUTE
» Bilanci di salute (PLS)
» obbiettivi di salute
» OBBIETTIVI DI SALUTE
» case della salute
» Bilanci di salute (PLS)
» obbiettivi di salute
» OBBIETTIVI DI SALUTE
» case della salute
Pagina 1 di 1
Permessi in questa sezione del forum:
Non puoi rispondere agli argomenti in questo forum.|
|
|