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

mhmmm i lost some data during import from mastertot -> mastertot2 mastertot: number of records: 2550171 mastertot2: SELECT COUNT(`Acido Urico`) as count1 , COUNT(`Albumina`) as count2 , COUNT(`Azotemia`) as count3 , COUNT(`Creatinina`) as count4 , COUNT(`Glicemia`) as count5 , COUNT(`HGB Emoglobina`) as count6 , COUNT(`INR`) as count7 , COUNT(`Litio`) as count8 , COUNT(`Peptide Natriuretico Tipo B`) as count9 , COUNT(`Peptide Natriuretico Tipo B N-Terminale`) as count10 , COUNT(`Potassio`) as count11 , COUNT(`Proteina C Reattiva`) as count12 , COUNT(`Proteine`) as count13 , COUNT(`Proteine Totali`) as count14 , COUNT(`Sodio`) as count15 FROM mastertot2 sum of counts 2489937 Aggregate functions ignore only NULL values?

ok, thanks! just an other curiosity, how can i count all value != NULL in a group of columns with a single query (if it is possible)? I refer to table result (see previous post), i would like count all values != NULL in columns a1, a2, a3 this number should be equal to number of rows of source table

... just a question to check if i understood your queries, in particular GROUP_CONCAT statement : i made this exercise: i made a query in order to select patients with same surname (cognome) and counting for each surname how many they are, i differentiate them using name and codicepz. Is it correct? SELECT `Cognome` , COUNT(distinct `Nome`, `CodicePz`) as `Count` , GROUP_CONCAT(distinct `Nome`, `CodicePz` ORDER BY `Nome` SEPARATOR ' , ') as Dates FROM mastertot_head10000 GROUP BY `Cognome` Thanks

you are right, but i received this table from the hospital analysis lab, each record is an analysis and probably they want immediatly the age at the moment of the analysis... i should talk with their dba... next friday they will give me other data like therapy .... it means other queries :-)) thanks!

it is stored in this strange format: yymmdd for example: 651004 means: 65 years 10 months 4 days this is the age at the AnalysisDate.

where i add the WHERE eta > 650000 to select only the patients with età > 65 years ? at the and? thanks SELECT User , COUNT(*) as `Count` , GROUP_CONCAT(datarichiesta,' (',result,')' ORDER BY datarichiesta SEPARATOR ' , ') as Dates FROM ( SELECT datarichiesta , @N := IF((codicepz<>@prevuser) OR (@prevres < '1.5') OR (risultato < '1.5') OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , @N+1, @N) as groupno , @startdate := IF((codicepz<>@prevuser) OR (@prevres < '1.5') OR (risultato < '1.5') OR (datarichiesta > @startdate + INTERVAL 6 MONTH) , datarichiesta, @startdate) as startdate , @prevres:=risultato as result , @prevuser:=codicepz as user FROM mastertot JOIN ( SELECT @N:=0, @prevres:=NULL, @prevuser:=NULL ) as init WHERE descrprestaz = 'Creatinina' ORDER BY codicepz, datarichiesta ) as detail GROUP BY user,groupno HAVING `Count` >= 5 WHERE eta > 650000;

So @N is a unique index for each record of the query result?

Thanks!!

I noticed that variable @N is no more present in your new query, so it is not necessary?

here it is mastertot table structure mastertot Colonna Tipo Null Predefinito Commenti MIME DataRichiesta date Sì NULL CodicePz varchar(15) Sì NULL Cognome varchar(50) Sì NULL Nome varchar(50) 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 RepartoProvenienza varchar(50) Sì NULL Laboratorio varchar(45) Sì NULL CodImpegnativa varchar(32) Sì NULL DataEmissione varchar(9) Sì NULL CodMedico varchar( Sì NULL Medico varchar(55) Sì NULL