paolo123

there is a minimum requirement for the number of forum posts before you are allowed to send attachments? Thanks! paolo

i was also thinking about coping data in php vectors in order to search for more complex time analysis profile. Do you think it is a good idea? There are particular dimension limit for vectors in php or other type of negative aspects like long elaboration time? Thanks

Yes! Thanks!! i did not think about inserting this dates restriction on groups at end, when all groups are already populated! It is not so immediate enter in the SQL logic. Could you suggest me a good tutorial or book about SQL ?

yes you were right ! sorry i did not explain me very well... between the first analysis and the last analysis of the run (composed by 5+ analysis) there must be an interval > 1month. For example: this sequence is not considered; 1-1-2014 a1 > 10 3-1-2014 a1> 10 7-1-2014 a1> 10 19-1-2014 a1 >10 20-1-2014 a1>10 24-1-2014 a1>10 4-2-2014 a1< 10 This sequence is considered; 1-1-2014 a1 > 10 3-1-2014 a1> 10 7-1-2014 a1> 10 19-1-2014 a1 >10 20-1-2014 a1>10 6-2-2014 a1>10 4-2-2014 a1< 10 So i consider the patients with an overthreshold analysis value for a long time (>1 month in this example).

i launched the following query and the counts are ok ! :-) The number of analysis executed in the same day: 116199 SELECT SUM(contatore) FROM ( SELECT `CodicePz`, count(*) as contatore FROM `mastertot` group by `DataRichiesta`, `CodicePz`, `DescrPrestaz` HAVING contatore>1 )as t The number of patients with same analysis executed more then 1 time in same day: 55965 SELECT count(*) as NumberOfPatientsWithSameAnalysusMoreThen1TimeInSameDay FROM ( SELECT `CodicePz`, count(*) as contatore FROM `mastertot` group by `DataRichiesta`, `CodicePz`, `DescrPrestaz` HAVING contatore>1 )as t 116199 - 55965 = 60243 60234 is the difference between (mastertot records) and (mastertot2 values != NULL) 2550171 - 2489937 = 60234 Thanks!!

i am looking about adding the statement, but i think that it is different from the six month statement, because i should have a buffer to contain the records and if the last record is before 1 month i drop all the records, while if the last record is over 1 month i keep all the records, but i reach the last record only when the run ends ,,, i mean i do not know how getting previous records...

It would be usefull defining a minimum Time interval along which the 5 or more values over threashold have to stay. I mean for example over 1 month under 6 month. Because the case in which all 5 values over threashold are taken in same day is not clinically significative...

i performed this query: SELECT `CodicePz`, count(*) FROM `mastertot` group by `DataRichiesta`, `CodicePz`, `DescrPrestaz` ORDER BY count(*) DESC now i sum the counts

so i should identify and count records with equal CodicePz DataRichiesta DescrPrestaz

it returns no COUNT(*)0

looking again your query return, it doen't seem there are mispelling in the DescrPrestaz field... i do not know where is the problem...

you are right :-) probably there are some misspelling. Infact your query SELECT DescrPrestaz , COUNT(*) as sampleTotal FROM mastertot GROUP BY DescrPrestaz; returns Acido Urico 58330 Albumina 39201 Azotemia 205605 Creatinina 327128 Glicemia 275360 HGB Emoglobina 376942 INR 406428 Litio 1292 Peptide Natriuretico Tipo B 2426 Peptide Natriuretico Tipo B N-Terminale 8639 Potassio 268849 Proteina C Reattiva 122901 Proteine 136830 Proteine Totali 64891 Sodio 255349 SUM 2550171 now i have to find the misspellings....

structure table mastertot2 Colonna Tipo Null Predefinito Commenti MIME CodicePz varchar(15) No DataRichiesta date No Acido Urico decimal(10,3) Sì NULL Albumina decimal(10,3) Sì NULL Azotemia decimal(10,3) Sì NULL Creatinina decimal(11,3) Sì NULL Glicemia decimal(11,3) Sì NULL HGB Emoglobina decimal(11,3) Sì NULL INR decimal(11,3) Sì NULL Litio decimal(11,3) Sì NULL Peptide Natriuretico Tipo B decimal(8,3) Sì NULL Peptide Natriuretico Tipo B N-Terminale decimal(10,3) Sì NULL Potassio decimal(11,3) Sì NULL Proteina C Reattiva decimal(11,3) Sì NULL Proteine decimal(10,3) Sì NULL Proteine Totali decimal(11,3) Sì NULL Sodio decimal(11,3) Sì NULL

structure della table mastertot_withoutname Colonna Tipo Null Predefinito Commenti MIME DataRichiesta date Sì NULL CodicePz varchar(15) Sì NULL Genere varchar(1) Sì NULL DataNascita varchar(15) Sì NULL eta int(11) Sì NULL codprestaz varchar( Sì NULL DescrPrestaz varchar(50) Sì NULL risultato decimal(8,3) Sì NULL udm varchar(20) Sì NULL CodImpegnativa varchar(32) Sì NULL DataEmissione varchar(9) Sì NULL

if you want i can share the mastertot.csv file (without patients name surname) with google drive in order to check the query which copies data from mastertot to mastertot2 ... INSERT INTO mastertot2 (CodicePz,DataRichiesta, `Acido Urico`, `Albumina`, `Azotemia`, `Creatinina`, `Glicemia`, `HGB Emoglobina`, `INR`, `Litio`, `Peptide Natriuretico Tipo B`, `Peptide Natriuretico Tipo B N-Terminale`, `Potassio`, `Proteina C Reattiva`, `Proteine`, `Proteine Totali`, `Sodio`) SELECT CodicePz , DataRichiesta , MIN(IF(DescrPrestaz='Acido Urico',risultato,NULL)) as 'Acido Urico' , MIN(IF(DescrPrestaz='Albumina',risultato,NULL)) as 'Albumina' , MIN(IF(DescrPrestaz='Azotemia',risultato,NULL)) as 'Azotemia' , MIN(IF(DescrPrestaz='Creatinina',risultato,NULL)) as 'Creatinina' , MIN(IF(DescrPrestaz='Glicemia',risultato,NULL)) as 'Glicemia' , MIN(IF(DescrPrestaz='HGB Emoglobina',risultato,NULL)) as 'HGB Emoglobina' , MIN(IF(DescrPrestaz='INR',risultato,NULL)) as 'INR' , MIN(IF(DescrPrestaz='Litio',risultato,NULL)) as 'Litio' , MIN(IF(DescrPrestaz='Peptide Natriuretico Tipo B',risultato,NULL)) as 'Peptide Natriuretico Tipo B' , MIN(IF(DescrPrestaz='Peptide Natriuretico Tipo B N-Terminale',risultato,NULL)) as 'Peptide Natriuretico Tipo B N-Terminale' , MIN(IF(DescrPrestaz='Potassio',risultato,NULL)) as 'Potassio' , MIN(IF(DescrPrestaz='Proteina C Reattiva',risultato,NULL)) as 'Proteina C Reattiva' , MIN(IF(DescrPrestaz='Proteine',risultato,NULL)) as 'Proteine' , MIN(IF(DescrPrestaz='Proteine Totali',risultato,NULL)) as 'Proteine Totali' , MIN(IF(DescrPrestaz='Sodio',risultato,NULL)) as 'Sodio' FROM mastertot GROUP BY CodicePz , DataRIchiesta