[Attachments, not available for all users?]

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

Thanks!

paolo

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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 ?

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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).

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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!!

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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...

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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...

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

i performed this query:

SELECT `CodicePz`, count(*) FROM `mastertot` group by `DataRichiesta`, `CodicePz`, `DescrPrestaz` ORDER BY count(*) DESC

 

now i sum the counts

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

so i should identify and count records with equal 

• CodicePz
• DataRichiesta
• DescrPrestaz

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

it returns no

 

COUNT(*)0

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

looking again your query return,  it doen't seem there are mispelling in the DescrPrestaz field...

i do not know where is the problem...

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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....

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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      

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

 

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

 

 

 

 

 

 

 

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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?

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

... 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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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!

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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.

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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;

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

Thanks!!

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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

[Copy all records with 2 equals data from table1 to one record in table2 (second part)]

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