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

paolo123 · May 11, 2015

Hi,

i have an other problem, i would like obtaining for each user the time trend of an analysis.

I mean, considering table

Table1

AccessDate__UserCode__AnalisisName____AnalisisResult
2012-01-01____1____________a1______________10
2012-01-01____1____________a2______________15
2012-01-01____1____________a3______________12
2014-03-01____1____________a1______________12
2014-03-01____1____________a3______________13
2012-01-01____2____________a1______________14
2012-01-01____2____________a2______________15
2012-01-01____2____________a3______________20
2013-03-01____2____________a1______________3
2012-01-01____3____________a1______________2
2012-01-01____3____________a2______________1
2014-01-01____3____________a1______________10

I would like obtaining a table like this, which contains the analysis a1 time trend for each user:

Table 2

User_____analysis.number__date.a1.1_____a1.1_____date.a1.2_____a1.2_____date.a1.3_____a1.3_____date.a1.4_____a1.4

1_____________2_________2012-01-01_______10______2012-01-01_____12_________________________________________________

2_____________2_________2012-01-01______14______2013-03-01______3________________________________________________

3_____________2_________2012-01-01_______2______2014-01-01_____10_______________________________________________

Table 2 should contain 50 column.

I would like obtaining this table format in order to select users with a particular analysis time trend.

For instance users which have a1 time trend like this: five times over 10 in 5 consecutive analysis.

Do you think that table2 is useful to obtain this trend.

Do you think it is possible performing it with a sql query or i must use also php ?

Thank a lot

Best regards

Paolo

Edited May 11, 2015 by paolo123

Barand · May 11, 2015

Does this help?

SELECT 
      User
    , COUNT(*) as `Count`
    , GROUP_CONCAT(accessdate,' (',result,')' ORDER BY accessdate SEPARATOR ' , ') as Dates
FROM
    (
    SELECT 
      accessdate
    , @N:=IF((usercode<>@prevuser) OR (@prevres < 10) OR (analysisresult < 10), 1, @N+1) as seqcount
    , @startdate := IF(@N=1 , accessdate, @startdate) as startdate
    , @prevres:=analysisresult as result
    , @prevuser:=usercode as user
    FROM result
        JOIN (
              SELECT @N:=0, @startdate:=NULL, @prevres:=NULL, @prevuser:=NULL
             ) as init
    WHERE analysisname = 'a1'
    ORDER BY usercode, accessdate
    ) as detail
GROUP BY user,startdate
HAVING `Count` >= 5;

(Takes around 25 secs on my laptop with the 2.55 million record table I saved from your last topic)

paolo123 · May 11, 2015

Thanks a lot!!!

I try it this evening!

paolo123 · May 11, 2015

I am studing your query.

result corresponds to my mastertot table?

So i have to sobstitute all 3 result words with mastertot?

Barand · May 11, 2015

result corresponds to my mastertot table?

All you ever stated was that have a table1. My "result" table corresponds to your

Table1
AccessDate__UserCode__AnalisisName____AnalisisResult

2012-01-01____1____________a1______________10
2012-01-01____1____________a2______________15
2012-01-01____1____________a3______________12

that contains 2.5 million records

The only one you need to change is in the FROM clause

FROM result

although you will need to misspell "analisiname" and "analisisresult" too

Edited May 11, 2015 by Barand

paolo123 · May 11, 2015

Sorry but i do not understand the Group_concat part and the select accessdate part...

Barand · May 11, 2015

The inner subquery selects the "a1" records in user/date order then reads through them. Where there is a run of records with a result of 10 or more they are given the same "startdate" (the first date in the run)

The outer query reads these results and GROUPS BY startdate, outputting those where there is a run count of 5 or more. It also GROUP_CONCATenates the dates and result values for each record in the group

The easiest way is probably to show you the GROUP_CONCAT output so you can see what it does

| User | Count | Dates      [ GROUP_CONCAT() date  and (result value) ]                                                                        |
+------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|    1 |     6 | 2008-02-04 (17) , 2008-02-05 (14) , 2008-02-06 (17) , 2008-02-07 (18) , 2008-02-08 (12) , 2008-02-09 (18)                                     |
|    1 |     8 | 2008-03-10 (24) , 2008-03-11 (12) , 2008-03-12 (12) , 2008-03-13 (16) , 2008-03-14 (20) , 2008-03-15 (17) , 2008-03-16 (25) , 2008-03-17 (13) |
|    1 |     6 | 2008-03-25 (10) , 2008-03-26 (15) , 2008-03-27 (12) , 2008-03-08 (21) , 2008-03-29 (10) , 2008-03-30 (23)                                     |
|    1 |     6 | 2008-04-25 (23) , 2008-04-26 (22) , 2008-04-27 (21) , 2008-04-28 (12) , 2008-04-29 (14) , 2008-04-30 (22)                                     |
|    1 |     5 | 2008-05-07 (25) , 2008-05-08 (25) , 2008-05-09 (25) , 2008-05-10 (16) , 2008-05-11 (20)                                                       |
+------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+

Edited May 11, 2015 by Barand

paolo123 · May 11, 2015

I am starting to understand.... An Other question...

It is possible to consider Only the Groups of 5 or more values which are inside a defined time intervall? For instance 6 months? I mean (date of last analysis) - (date of first analysis) < 6 months

Barand · May 11, 2015

Yes. You would check the accessdate against the @startdate and reset @N to 1 if the gap was more than 6 months

Barand · May 11, 2015

This will start a new date group if it finds a gap of greater than six months

SELECT 
      User
    , COUNT(*) as `Count`
    , GROUP_CONCAT(accessdate,' (',result,')' ORDER BY accessdate SEPARATOR ' , ') as Dates
FROM
    (
    SELECT 
      accessdate
    , @startdate := IF((usercode<>@prevuser) OR (@prevres < 10) OR (analysisresult < 10)
        OR (accessdate > @startdate + INTERVAL 6 MONTH)
        , accessdate, @startdate) as startdate
    , @prevres:=analysisresult as result
    , @prevuser:=usercode as user
    FROM result
        JOIN (
              SELECT @startdate:=NULL, @prevres:=NULL, @prevuser:=NULL
             ) as init
    WHERE analysisname = 'a1'
    AND usercode = 200
    ORDER BY usercode, accessdate
    ) as detail
GROUP BY user,startdate
HAVING `Count` >= 5

paolo123 · May 11, 2015

why you wrote

AND usercode = 200

?

Barand · May 11, 2015

Sorry. I created a test user with a 6 month gap (id 200) to test the query. You can take that line out of the query

paolo123 · May 12, 2015

thanks again ! :-)

paolo123 · May 12, 2015

.. i launched the query:

SELECT

User

, COUNT(*) as `Count`

, GROUP_CONCAT(`DataRichiesta`,' (',result,')' ORDER BY `DataRichiesta` SEPARATOR ' , ') as Dates

FROM

(

SELECT

`DataRichiesta`

, @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 @startdate:=NULL, @prevres:=NULL, @prevuser:=NULL

) as init

WHERE `DescrPrestaz` = 'Creatinina'

ORDER BY `CodicePz`, `DataRichiesta`

) as detail

GROUP BY user,startdate

HAVING `Count` >= 5

and i found in the results (2178 records)

this line

10842;6;2013-07-15 (0.370) , 2013-07-15 (0.370) , 2013-07-15 (0.440) , 2013-07-15 (0.440) , 2013-07-15 (0.360) , 2013-07-15 (0.360)

i don't understand why this patient was selected... all the values are less then 1.5

do you have any idea?

All the other records seems to be ok:

head 100

101143;5;2014-04-11 (3.760) , 2014-04-12 (2.960) , 2014-04-13 (2.470) , 2014-04-18 (1.880) , 2014-04-24 (1.820)

101947;10;2014-02-16 (1.880) , 2014-02-16 (1.960) , 2014-02-25 (1.960) , 2014-02-25 (1.980) , 2014-02-25 (2.240) , 2014-04-04 (1.990) , 2014-04-09 (2.110) , 2014-06-28 (1.960) , 2014-07-30 (1.830) , 2014-07-31 (1.820)

101947;5;2014-08-18 (2.640) , 2014-10-16 (1.730) , 2014-10-27 (1.800) , 2014-10-28 (2.420) , 2014-10-31 (2.070)

1026;6;2012-06-12 (1.550) , 2012-06-13 (1.750) , 2012-08-16 (1.850) , 2012-08-17 (2.230) , 2012-08-21 (2.050) , 2012-08-23 (1.670)

102744;6;2014-09-16 (6.990) , 2014-09-17 (5.060) , 2014-09-18 (4.130) , 2014-09-19 (2.910) , 2014-09-23 (1.870) , 2014-09-27 (1.650)

102770;7;2012-02-10 (5.650) , 2012-02-12 (4.980) , 2012-02-14 (4.110) , 2012-02-16 (3.530) , 2012-02-18 (3.000) , 2012-02-22 (2.600) , 2012-02-27 (2.780)

103533;19;2012-01-02 (7.890) , 2012-01-03 (10.400) , 2012-01-04 (9.670) , 2012-01-05 (5.880) , 2012-01-06 (4.650) , 2012-01-07 (3.850) , 2012-01-09 (5.150) , 2012-01-10 (5.220) , 2012-01-11 (5.490) , 2012-01-12 (6.440) , 2012-01-13 (4.580) , 2012-01-15 (4.590) , 2012-01-16 (6.570) , 2012-01-17 (6.710) , 2012-01-19 (5.400) , 2012-01-24 (5.040) , 2012-04-09 (3.700) , 2012-04-19 (5.910) , 2012-04-23 (7.360)

103687;5;2012-03-19 (1.610) , 2012-04-07 (1.680) , 2012-04-08 (1.710) , 2012-04-11 (1.550) , 2012-08-12 (1.610)

103811;10;2014-04-23 (2.350) , 2014-04-24 (2.310) , 2014-04-25 (2.010) , 2014-04-26 (1.980) , 2014-04-27 (1.950) , 2014-04-29 (2.100) , 2014-04-30 (2.470) , 2014-05-02 (2.420) , 2014-05-13 (1.770) , 2014-05-14 (1.770)

10400;13;2014-08-28 (3.900) , 2014-08-29 (3.710) , 2014-09-01 (2.780) , 2014-09-02 (2.900) , 2014-09-04 (2.960) , 2014-09-08 (3.110) , 2014-09-25 (3.250) , 2014-09-30 (3.520) , 2014-10-29 (4.900) , 2014-10-30 (5.020) , 2014-11-01 (5.260) , 2014-11-03 (4.520) , 2014-11-11 (4.720)

104013;7;2014-08-09 (1.640) , 2014-08-10 (2.740) , 2014-08-11 (3.550) , 2014-08-12 (3.570) , 2014-08-14 (3.760) , 2014-08-15 (3.360) , 2014-08-16 (3.980)

10427;7;2013-02-04 (1.730) , 2013-03-12 (1.540) , 2013-07-10 (2.140) , 2013-07-11 (1.840) , 2013-07-11 (1.820) , 2013-07-11 (1.960) , 2013-07-12 (1.690)

10446;5;2012-01-08 (1.710) , 2012-01-10 (1.820) , 2012-05-04 (2.930) , 2012-05-04 (2.670) , 2012-05-05 (2.570)

10515;11;2014-03-03 (1.530) , 2014-03-04 (3.270) , 2014-03-06 (3.250) , 2014-03-07 (2.290) , 2014-03-08 (1.700) , 2014-04-01 (1.690) , 2014-07-14 (2.330) , 2014-08-11 (2.330) , 2014-08-12 (2.260) , 2014-08-22 (3.120) , 2014-08-23 (3.710)

105613;7;2013-03-01 (3.280) , 2013-03-02 (3.380) , 2013-03-03 (2.720) , 2013-03-04 (2.120) , 2013-03-05 (1.830) , 2013-03-06 (1.680) , 2013-05-27 (3.450)

106312;21;2014-03-14 (1.660) , 2014-03-16 (1.920) , 2014-03-17 (1.910) , 2014-03-18 (1.680) , 2014-03-20 (1.710) , 2014-03-21 (1.710) , 2014-03-22 (1.870) , 2014-03-23 (4.140) , 2014-03-23 (4.420) , 2014-03-23 (5.720) , 2014-03-24 (4.950) , 2014-03-25 (4.470) , 2014-03-26 (5.290) , 2014-03-27 (4.410) , 2014-03-28 (3.600) , 2014-03-29 (3.120) , 2014-03-30 (2.720) , 2014-03-31 (2.550) , 2014-04-01 (2.560) , 2014-04-02 (2.350) , 2014-04-04 (2.620)

106489;14;2012-01-18 (1.850) , 2012-01-19 (1.740) , 2012-01-20 (1.550) , 2012-01-22 (1.520) , 2012-01-24 (1.620) , 2012-01-27 (1.740) , 2012-01-28 (1.850) , 2012-02-22 (2.070) , 2012-03-09 (1.670) , 2012-03-10 (1.930) , 2012-03-11 (1.960) , 2012-03-12 (1.920) , 2012-03-15 (1.840) , 2012-03-16 (1.700)

106921;5;2012-01-09 (3.260) , 2012-02-25 (3.810) , 2012-02-26 (3.640) , 2012-04-13 (3.440) , 2012-05-16 (3.320)

10701;7;2014-11-29 (1.630) , 2014-11-30 (1.670) , 2014-12-01 (2.700) , 2014-12-02 (3.730) , 2014-12-03 (3.100) , 2014-12-04 (2.710) , 2014-12-05 (2.180)

107300;28;2012-01-25 (4.060) , 2012-03-13 (3.900) , 2012-04-26 (3.730) , 2012-04-28 (3.580) , 2012-05-04 (2.950) , 2012-05-07 (3.350) , 2012-05-08 (3.430) , 2012-05-16 (2.600) , 2012-05-17 (2.980) , 2012-05-18 (3.190) , 2012-05-19 (3.180) , 2012-05-20 (3.370) , 2012-05-21 (3.120) , 2012-05-22 (3.130) , 2012-05-23 (3.050) , 2012-05-24 (2.870) , 2012-05-25 (2.630) , 2012-05-26 (2.470) , 2012-05-28 (2.810) , 2012-05-29 (3.890) , 2012-05-31 (4.430) , 2012-06-01 (4.250) , 2012-06-02 (4.320) , 2012-06-03 (4.600) , 2012-06-04 (5.580) , 2012-06-05 (6.620) , 2012-06-05 (5.270) , 2012-06-09 (6.470)

1075;16;2013-02-13 (13.430) , 2013-02-15 (13.530) , 2013-02-16 (7.840) , 2013-02-17 (6.330) , 2013-02-18 (7.660) , 2013-02-20 (8.240) , 2013-02-22 (6.780) , 2013-02-25 (3.720) , 2013-02-27 (2.780) , 2013-02-28 (2.610) , 2013-03-01 (2.550) , 2013-03-03 (3.580) , 2013-03-04 (3.880) , 2013-03-06 (4.900) , 2013-03-07 (4.340) , 2013-04-18 (9.300)

107636;5;2012-05-28 (3.400) , 2012-05-29 (3.690) , 2012-05-30 (3.880) , 2012-05-31 (3.920) , 2012-06-02 (1.510)

107636;6;2012-06-24 (1.540) , 2012-06-25 (2.160) , 2012-06-26 (2.330) , 2012-06-27 (2.670) , 2012-06-28 (2.690) , 2012-06-29 (3.350)

10815;10;2012-11-22 (1.760) , 2012-11-23 (2.240) , 2012-11-24 (2.570) , 2012-11-26 (2.180) , 2012-11-27 (2.180) , 2012-11-28 (2.230) , 2012-11-30 (2.030) , 2012-12-01 (1.880) , 2012-12-11 (1.800) , 2013-01-23 (1.780)

10824;6;2012-01-01 (3.510) , 2012-01-02 (3.240) , 2012-01-03 (2.950) , 2012-01-04 (2.900) , 2012-01-05 (2.940) , 2012-01-09 (1.830)

10842;6;2013-07-15 (0.370) , 2013-07-15 (0.370) , 2013-07-15 (0.440) , 2013-07-15 (0.440) , 2013-07-15 (0.360) , 2013-07-15 (0.360)

109692;14;2013-06-30 (1.680) , 2013-07-01 (2.320) , 2013-07-02 (3.040) , 2013-07-03 (3.470) , 2013-07-04 (3.870) , 2013-07-05 (3.620) , 2013-07-06 (2.800) , 2013-07-07 (2.290) , 2013-07-08 (2.060) , 2013-07-09 (1.640) , 2013-07-09 (1.770) , 2013-07-10 (1.950) , 2013-07-11 (1.720) , 2013-07-12 (1.510)

11148;12;2014-10-06 (2.030) , 2014-10-07 (2.250) , 2014-10-08 (2.670) , 2014-10-10 (2.840) , 2014-10-11 (4.350) , 2014-10-12 (5.910) , 2014-10-13 (7.480) , 2014-10-14 (5.870) , 2014-10-15 (3.730) , 2014-10-17 (1.920) , 2014-10-19 (1.540) , 2014-10-20 (1.750)

11230;5;2012-01-01 (5.320) , 2012-01-19 (5.200) , 2012-02-20 (6.670) , 2012-03-22 (7.610) , 2012-04-02 (7.480)

11230;5;2012-10-01 (8.270) , 2012-12-07 (4.670) , 2012-12-24 (6.120) , 2013-01-29 (4.680) , 2013-02-11 (7.310)

11230;8;2013-05-17 (8.740) , 2013-06-15 (4.700) , 2013-06-21 (7.450) , 2013-06-21 (7.220) , 2013-06-23 (5.320) , 2013-06-24 (5.560) , 2013-06-25 (6.280) , 2013-06-26 (4.180)

1126;11;2012-05-24 (1.800) , 2012-06-16 (2.020) , 2012-06-18 (2.050) , 2012-06-22 (1.820) , 2012-07-24 (2.670) , 2012-08-10 (2.110) , 2012-08-11 (1.840) , 2012-08-16 (1.500) , 2012-08-21 (3.170) , 2012-08-23 (1.990) , 2012-08-28 (1.530)

1130;12;2012-02-02 (9.410) , 2012-05-11 (3.660) , 2012-05-12 (4.630) , 2012-05-13 (7.840) , 2012-05-14 (9.400) , 2012-05-15 (6.710) , 2012-05-16 (7.830) , 2012-05-17 (6.300) , 2012-05-18 (8.750) , 2012-05-19 (5.980) , 2012-05-22 (6.490) , 2012-06-14 (7.580)

1130613;5;2012-09-09 (2.690) , 2012-09-09 (2.870) , 2012-09-10 (2.650) , 2012-09-10 (2.510) , 2012-09-11 (1.740)

1130791;7;2013-06-01 (3.370) , 2013-06-02 (3.340) , 2013-06-07 (4.310) , 2013-06-10 (4.880) , 2013-06-11 (5.250) , 2013-06-15 (7.070) , 2013-06-17 (6.990)

1131373;6;2012-03-02 (1.500) , 2012-03-05 (1.580) , 2012-03-08 (1.780) , 2012-03-14 (2.180) , 2012-03-22 (2.590) , 2012-05-15 (2.010)

1131384;6;2012-03-10 (1.870) , 2012-03-11 (1.730) , 2012-08-06 (2.010) , 2012-08-08 (2.140) , 2012-08-20 (1.650) , 2012-09-03 (1.600)

1131396;28;2012-10-02 (2.050) , 2012-11-27 (1.840) , 2013-02-07 (2.550) , 2013-02-12 (3.180) , 2013-02-12 (3.180) , 2013-02-13 (3.210) , 2013-02-14 (3.240) , 2013-02-15 (3.110) , 2013-02-18 (2.900) , 2013-02-22 (2.690) , 2013-02-25 (2.760) , 2013-03-04 (2.450) , 2013-03-11 (2.910) , 2013-03-12 (2.280) , 2013-03-12 (2.650) , 2013-03-13 (3.080) , 2013-03-13 (3.340) , 2013-03-14 (5.510) , 2013-03-15 (3.760) , 2013-03-16 (3.220) , 2013-03-17 (3.160) , 2013-03-18 (3.160) , 2013-03-19 (4.060) , 2013-03-22 (3.740) , 2013-03-24 (3.680) , 2013-03-25 (3.600) , 2013-03-26 (3.490) , 2013-03-28 (3.500)

1131396;30;2013-04-08 (3.350) , 2013-04-22 (3.060) , 2013-04-28 (3.590) , 2013-04-29 (3.510) , 2013-05-03 (4.290) , 2013-05-05 (4.510) , 2013-05-08 (4.120) , 2013-05-11 (3.950) , 2013-05-13 (3.810) , 2013-05-15 (3.790) , 2013-05-20 (4.020) , 2013-05-23 (4.030) , 2013-05-26 (4.030) , 2013-05-28 (4.110) , 2013-06-03 (4.650) , 2013-06-06 (5.200) , 2013-06-07 (5.670) , 2013-06-08 (5.760) , 2013-06-09 (5.010) , 2013-06-11 (4.180) , 2013-06-13 (4.160) , 2013-06-23 (3.360) , 2013-06-25 (3.460) , 2013-06-28 (3.360) , 2013-07-01 (3.270) , 2013-07-04 (3.690) , 2013-07-08 (3.440) , 2013-07-12 (3.580) , 2013-07-13 (3.430) , 2013-07-19 (4.430)

1131509;10;2013-12-07 (5.690) , 2013-12-07 (5.120) , 2013-12-07 (5.440) , 2013-12-08 (5.190) , 2013-12-09 (5.180) , 2013-12-12 (5.220) , 2013-12-16 (5.970) , 2013-12-19 (5.660) , 2014-03-11 (7.530) , 2014-04-04 (5.390)

1131540;11;2012-05-21 (1.520) , 2012-05-22 (2.790) , 2012-05-22 (2.880) , 2012-05-23 (2.410) , 2012-05-24 (2.210) , 2012-05-25 (1.990) , 2012-05-26 (1.900) , 2012-05-27 (1.920) , 2012-05-28 (2.380) , 2012-05-29 (2.550) , 2012-05-30 (3.200)

1131626;6;2012-06-23 (2.260) , 2012-06-24 (4.030) , 2012-06-24 (4.230) , 2012-06-25 (4.660) , 2012-06-26 (2.350) , 2012-06-27 (1.590)

1131844;8;2014-03-12 (3.610) , 2014-03-13 (3.880) , 2014-03-13 (4.390) , 2014-03-14 (2.730) , 2014-03-15 (3.550) , 2014-03-16 (4.040) , 2014-03-17 (3.920) , 2014-03-18 (2.240)

1132351;6;2014-05-05 (2.890) , 2014-05-07 (2.600) , 2014-05-08 (2.380) , 2014-10-10 (2.210) , 2014-10-13 (2.470) , 2014-10-17 (2.250)

1132473;5;2013-05-25 (5.400) , 2013-06-04 (6.120) , 2013-08-05 (8.310) , 2013-08-11 (7.870) , 2013-08-13 (7.400)

1132528;10;2012-02-17 (1.500) , 2012-02-17 (1.420) , 2012-02-20 (1.910) , 2012-02-23 (2.430) , 2012-02-25 (2.070) , 2012-02-27 (1.930) , 2012-02-28 (1.850) , 2012-03-01 (1.660) , 2012-03-02 (2.140) , 2012-03-03 (1.630)

1133001;5;2012-10-11 (2.250) , 2012-12-13 (2.550) , 2013-01-28 (3.600) , 2013-01-29 (3.230) , 2013-02-04 (1.750)

1133001;10;2013-05-18 (2.810) , 2013-05-20 (2.090) , 2013-08-07 (2.300) , 2013-08-08 (2.330) , 2013-08-14 (1.940) , 2013-08-28 (2.380) , 2013-08-29 (2.220) , 2013-08-30 (2.180) , 2013-09-01 (2.040) , 2013-09-06 (1.710)

1133362;5;2012-09-03 (2.010) , 2012-09-10 (2.080) , 2012-09-11 (2.090) , 2012-10-01 (2.610) , 2012-11-26 (1.960)

1133362;28;2013-08-09 (4.020) , 2013-08-10 (4.010) , 2013-08-12 (3.570) , 2013-08-14 (3.290) , 2013-08-18 (2.700) , 2013-08-27 (2.920) , 2013-09-04 (3.020) , 2013-09-07 (3.150) , 2013-09-09 (3.770) , 2013-09-12 (4.020) , 2013-09-16 (4.860) , 2013-09-22 (5.630) , 2013-09-22 (5.670) , 2013-09-23 (5.840) , 2013-09-24 (5.830) , 2013-09-25 (6.310) , 2013-09-27 (5.310) , 2013-09-28 (5.640) , 2013-09-30 (5.200) , 2013-10-03 (5.920) , 2013-10-05 (5.950) , 2013-10-09 (5.840) , 2013-10-10 (5.740) , 2013-10-11 (5.640) , 2013-10-13 (6.290) , 2013-10-21 (3.810) , 2014-01-29 (3.770) , 2014-01-30 (2.830)

1134630;5;2014-03-30 (2.170) , 2014-03-31 (1.920) , 2014-04-01 (1.950) , 2014-04-13 (2.730) , 2014-04-14 (2.120)

1134743;9;2014-06-09 (1.730) , 2014-06-14 (1.940) , 2014-06-17 (1.990) , 2014-07-20 (1.780) , 2014-07-21 (1.790) , 2014-07-22 (1.990) , 2014-07-25 (1.600) , 2014-07-27 (1.730) , 2014-08-01 (2.110)

1135159;6;2013-12-19 (1.840) , 2013-12-20 (2.030) , 2013-12-21 (1.940) , 2013-12-22 (1.780) , 2013-12-23 (2.080) , 2013-12-24 (1.760)

1135592;5;2012-04-30 (1.590) , 2012-05-16 (1.850) , 2012-06-13 (1.550) , 2012-07-27 (1.510) , 2012-10-29 (1.840)

1135727;8;2012-01-06 (2.060) , 2012-01-22 (2.010) , 2012-02-01 (2.440) , 2012-02-27 (2.590) , 2012-02-28 (2.640) , 2012-03-20 (1.850) , 2012-05-14 (1.740) , 2012-05-15 (1.610)

1135727;8;2012-05-28 (1.930) , 2012-05-30 (1.690) , 2012-06-06 (2.840) , 2012-06-09 (2.460) , 2012-06-11 (1.920) , 2012-06-13 (1.720) , 2012-06-15 (1.610) , 2012-06-19 (2.250)

1135771;17;2014-11-19 (1.650) , 2014-11-20 (2.550) , 2014-11-20 (2.280) , 2014-11-21 (3.140) , 2014-11-22 (3.790) , 2014-11-23 (3.940) , 2014-11-24 (4.030) , 2014-11-25 (3.570) , 2014-11-26 (3.270) , 2014-11-27 (2.750) , 2014-11-28 (2.450) , 2014-11-29 (2.500) , 2014-11-30 (2.330) , 2014-12-01 (2.130) , 2014-12-02 (2.060) , 2014-12-03 (1.940) , 2014-12-04 (1.630)

1136452;8;2014-12-18 (1.660) , 2014-12-19 (1.700) , 2014-12-20 (2.340) , 2014-12-21 (2.290) , 2014-12-22 (2.080) , 2014-12-23 (1.720) , 2014-12-24 (1.700) , 2014-12-25 (1.530)

1136667;9;2013-11-20 (4.810) , 2013-11-20 (4.980) , 2013-11-30 (5.320) , 2013-12-01 (5.170) , 2013-12-02 (5.570) , 2013-12-20 (5.900) , 2013-12-27 (5.760) , 2014-02-05 (4.480) , 2014-04-24 (3.510)

1137033;6;2013-11-29 (2.010) , 2014-03-08 (2.790) , 2014-03-09 (2.950) , 2014-03-10 (2.990) , 2014-03-11 (2.880) , 2014-03-17 (2.780)

1137657;11;2014-08-17 (4.950) , 2014-08-18 (5.210) , 2014-08-19 (5.220) , 2014-08-20 (4.620) , 2014-08-21 (4.090) , 2014-08-22 (3.690) , 2014-08-25 (3.270) , 2014-08-28 (2.970) , 2014-09-01 (3.450) , 2014-09-03 (3.710) , 2014-11-28 (4.190)

1137777;6;2013-02-09 (1.960) , 2013-03-04 (3.810) , 2013-03-05 (3.730) , 2013-03-06 (3.050) , 2013-03-08 (1.830) , 2013-03-09 (1.670)

1137947;5;2012-06-14 (2.120) , 2012-07-25 (2.000) , 2012-09-11 (1.930) , 2012-10-18 (1.880) , 2012-10-20 (1.860)

1137947;9;2013-02-13 (2.000) , 2013-05-17 (1.750) , 2013-05-23 (1.990) , 2013-07-10 (1.810) , 2013-07-11 (1.530) , 2013-07-12 (1.540) , 2013-07-13 (1.600) , 2013-07-15 (1.750) , 2013-07-16 (1.530)

1138128;6;2014-01-07 (1.540) , 2014-01-14 (2.770) , 2014-01-15 (2.270) , 2014-01-17 (2.030) , 2014-01-22 (1.560) , 2014-01-23 (1.760)

1138374;19;2012-06-06 (3.740) , 2012-06-06 (3.770) , 2012-06-06 (5.240) , 2012-06-07 (6.120) , 2012-06-08 (7.450) , 2012-06-09 (5.060) , 2012-06-09 (5.010) , 2012-06-10 (3.350) , 2012-06-11 (2.550) , 2012-06-12 (2.080) , 2012-06-13 (3.040) , 2012-06-14 (3.160) , 2012-06-15 (3.510) , 2012-06-16 (1.650) , 2012-06-17 (1.890) , 2012-06-18 (1.920) , 2012-06-19 (1.790) , 2012-06-20 (1.710) , 2012-06-21 (2.110)

1138867;6;2012-09-18 (1.580) , 2012-09-19 (1.720) , 2012-09-19 (1.930) , 2012-09-20 (2.030) , 2012-09-20 (2.010) , 2012-09-21 (1.710)

1139065;14;2014-10-13 (1.660) , 2014-10-16 (1.900) , 2014-10-18 (1.550) , 2014-10-21 (2.020) , 2014-10-22 (2.160) , 2014-10-23 (2.430) , 2014-10-25 (3.250) , 2014-10-25 (3.670) , 2014-10-26 (3.700) , 2014-10-27 (4.240) , 2014-10-28 (3.600) , 2014-10-29 (3.180) , 2014-10-30 (2.790) , 2014-11-01 (2.010)

1139687;5;2014-09-23 (2.780) , 2014-09-24 (2.180) , 2014-09-25 (2.310) , 2014-09-26 (2.280) , 2014-09-27 (1.820)

114;5;2013-05-01 (2.270) , 2013-05-01 (2.600) , 2013-05-02 (2.600) , 2013-05-02 (2.360) , 2013-05-03 (1.730)

114;7;2013-06-02 (1.980) , 2013-06-07 (2.590) , 2013-06-09 (2.170) , 2013-06-11 (2.320) , 2013-06-17 (1.980) , 2013-06-21 (1.990) , 2013-06-25 (2.160)

1140136;8;2012-06-25 (1.750) , 2012-06-28 (2.100) , 2012-08-03 (1.680) , 2012-09-12 (1.860) , 2012-09-13 (1.560) , 2012-09-21 (1.580) , 2012-10-02 (1.870) , 2012-10-18 (1.830)

1140136;14;2013-01-26 (1.800) , 2013-07-03 (1.720) , 2013-07-03 (1.640) , 2013-07-04 (2.160) , 2013-07-05 (2.360) , 2013-07-06 (2.920) , 2013-07-07 (2.840) , 2013-07-09 (3.090) , 2013-07-10 (3.010) , 2013-07-11 (2.650) , 2013-07-12 (2.500) , 2013-07-14 (2.200) , 2013-07-16 (1.930) , 2013-07-22 (2.210)

1140380;16;2014-01-07 (2.180) , 2014-01-09 (2.070) , 2014-01-10 (2.100) , 2014-01-12 (2.680) , 2014-01-13 (2.960) , 2014-01-14 (2.810) , 2014-01-15 (2.430) , 2014-01-16 (2.390) , 2014-01-17 (2.290) , 2014-01-24 (2.760) , 2014-02-24 (2.150) , 2014-02-26 (2.370) , 2014-02-27 (2.310) , 2014-02-28 (2.150) , 2014-03-03 (2.340) , 2014-03-06 (2.290)

1140561;6;2012-04-26 (1.840) , 2012-04-27 (2.180) , 2012-04-28 (2.350) , 2012-04-30 (2.540) , 2012-05-01 (2.820) , 2012-05-02 (3.110)

1140807;8;2012-09-16 (3.400) , 2012-09-16 (3.630) , 2012-09-17 (2.730) , 2013-01-04 (3.600) , 2013-01-05 (2.680) , 2013-01-06 (2.160) , 2013-01-07 (2.940) , 2013-01-08 (2.350)

1140807;7;2013-05-24 (3.110) , 2013-05-30 (5.340) , 2013-05-31 (5.130) , 2013-06-26 (3.900) , 2013-06-27 (3.770) , 2013-06-27 (3.480) , 2013-07-03 (2.660)

1140872;6;2014-07-23 (1.870) , 2014-07-24 (1.820) , 2014-07-25 (1.550) , 2014-07-26 (1.510) , 2014-07-27 (1.700) , 2014-07-29 (2.020)

1140947;6;2013-11-11 (1.750) , 2013-11-21 (4.790) , 2013-11-22 (3.830) , 2013-11-25 (2.890) , 2013-11-28 (2.030) , 2013-12-02 (2.100)

1141142;5;2014-09-18 (1.840) , 2014-09-19 (1.970) , 2014-09-20 (2.070) , 2014-09-21 (2.160) , 2014-09-23 (2.210)

1141208;7;2013-09-05 (1.690) , 2013-11-10 (2.300) , 2013-11-11 (2.200) , 2013-11-12 (1.930) , 2013-11-15 (2.800) , 2013-11-16 (1.630) , 2013-11-21 (1.900)

1141369;5;2012-01-17 (9.680) , 2012-02-02 (9.320) , 2012-05-09 (10.300) , 2012-06-06 (10.490) , 2012-07-11 (9.140)

1141369;40;2012-08-03 (2.350) , 2012-08-08 (2.100) , 2012-08-10 (2.130) , 2012-08-17 (2.180) , 2012-08-20 (1.910) , 2012-08-22 (1.850) , 2012-08-24 (1.900) , 2012-08-29 (1.630) , 2012-08-31 (1.650) , 2012-09-03 (1.620) , 2012-09-05 (1.720) , 2012-09-07 (2.000) , 2012-09-10 (1.910) , 2012-09-12 (1.910) , 2012-09-14 (1.850) , 2012-09-17 (1.780) , 2012-09-19 (1.680) , 2012-09-21 (1.840) , 2012-09-24 (1.660) , 2012-09-26 (1.630) , 2012-09-28 (1.860) , 2012-10-01 (1.690) , 2012-10-08 (1.630) , 2012-10-12 (1.660) , 2012-10-15 (1.560) , 2012-10-19 (1.660) , 2012-10-22 (1.580) , 2012-10-29 (1.540) , 2012-11-02 (1.630) , 2012-11-05 (1.550) , 2012-11-09 (1.550) , 2012-11-12 (1.600) , 2012-11-16 (1.790) , 2012-11-19 (1.660) , 2012-11-23 (1.640) , 2012-11-26 (1.560) , 2012-11-30 (1.830) , 2012-12-07 (1.600) , 2012-12-14 (1.780) , 2012-12-21 (1.730)

1141638;5;2012-11-16 (3.390) , 2012-11-17 (3.690) , 2012-11-18 (3.460) , 2012-11-20 (2.270) , 2012-11-22 (1.620)

1141751;7;2013-11-16 (2.150) , 2014-04-07 (2.150) , 2014-04-08 (2.030) , 2014-04-23 (1.710) , 2014-04-24 (1.940) , 2014-04-26 (2.030) , 2014-04-30 (1.990)

1141981;10;2014-11-18 (2.200) , 2014-11-20 (2.290) , 2014-11-21 (2.330) , 2014-11-22 (2.280) , 2014-11-24 (2.460) , 2014-11-28 (1.940) , 2014-12-01 (1.650) , 2014-12-09 (3.280) , 2014-12-11 (3.960) , 2014-12-13 (3.910)

1141992;6;2012-01-23 (1.740) , 2012-01-26 (1.530) , 2012-04-26 (1.670) , 2012-05-06 (1.930) , 2012-05-21 (1.730) , 2012-06-05 (1.710)

1141992;7;2012-07-20 (1.550) , 2012-07-30 (1.890) , 2012-08-03 (2.230) , 2012-08-16 (1.690) , 2012-09-17 (1.820) , 2012-09-25 (1.610) , 2012-10-07 (1.730)

1141992;11;2012-12-09 (1.620) , 2012-12-10 (1.530) , 2012-12-11 (1.650) , 2012-12-17 (1.840) , 2012-12-19 (1.900) , 2013-04-18 (2.230) , 2013-04-18 (2.180) , 2013-04-19 (2.330) , 2013-04-21 (1.690) , 2013-04-23 (3.440) , 2013-04-24 (4.810)

1142288;5;2014-12-06 (1.610) , 2014-12-08 (2.210) , 2014-12-10 (1.770) , 2014-12-12 (1.710) , 2014-12-15 (1.630)

1142346;7;2013-02-10 (2.860) , 2013-02-11 (2.800) , 2013-02-12 (2.840) , 2013-02-14 (2.610) , 2013-02-19 (2.300) , 2013-02-25 (2.120) , 2013-06-08 (2.480)

1142886;5;2012-04-02 (4.010) , 2012-04-03 (5.120) , 2012-04-04 (4.960) , 2012-04-05 (3.410) , 2012-04-06 (2.000)

1143066;43;2014-07-01 (1.660) , 2014-07-02 (1.610) , 2014-07-04 (1.800) , 2014-07-05 (3.180) , 2014-07-06 (3.690) , 2014-07-07 (4.140) , 2014-07-08 (3.880) , 2014-07-09 (3.470) , 2014-07-10 (2.900) , 2014-07-11 (2.600) , 2014-07-12 (2.420) , 2014-07-13 (2.440) , 2014-07-14 (2.350) , 2014-07-15 (2.230) , 2014-07-16 (2.120) , 2014-07-17 (1.990) , 2014-07-18 (1.890) , 2014-07-19 (1.910) , 2014-07-20 (2.010) , 2014-07-21 (1.970) , 2014-07-22 (2.050) , 2014-07-23 (2.160) , 2014-07-25 (2.270) , 2014-07-26 (2.170) , 2014-07-28 (1.840) , 2014-07-30 (1.840) , 2014-07-31 (1.650) , 2014-07-31 (1.780) , 2014-08-03 (1.890) , 2014-08-07 (2.060) , 2014-08-10 (2.120) , 2014-08-12 (1.850) , 2014-08-15 (1.820) , 2014-08-19 (1.950) , 2014-12-20 (2.450) , 2014-12-21 (3.610) , 2014-12-22 (3.970) , 2014-12-24 (4.350) , 2014-12-26 (3.600) , 2014-12-28 (3.370) , 2014-12-29 (3.150) , 2014-12-30 (2.530) , 2014-12-31 (2.400)

1143165;11;2012-08-06 (3.050) , 2012-09-27 (3.470) , 2012-10-13 (3.180) , 2012-11-07 (3.340) , 2012-11-18 (2.670) , 2012-11-18 (3.010) , 2012-11-19 (2.530) , 2012-11-24 (2.700) , 2012-11-26 (2.460) , 2012-11-30 (2.230) , 2012-12-19 (3.450)

1143165;8;2013-02-13 (6.850) , 2013-02-14 (6.550) , 2013-02-15 (5.570) , 2013-02-16 (4.920) , 2013-02-18 (4.800) , 2013-02-20 (5.350) , 2013-02-22 (6.050) , 2013-03-04 (7.730)

1143173;31;2014-01-15 (1.530) , 2014-01-16 (1.650) , 2014-01-17 (1.920) , 2014-01-18 (1.980) , 2014-01-19 (2.410) , 2014-01-20 (2.760) , 2014-01-21 (2.300) , 2014-01-22 (2.620) , 2014-01-23 (3.150) , 2014-01-24 (2.630) , 2014-01-25 (2.330) , 2014-01-26 (2.130) , 2014-01-27 (2.180) , 2014-01-29 (2.530) , 2014-01-30 (2.600) , 2014-01-31 (2.410) , 2014-02-05 (2.500) , 2014-03-19 (2.570) , 2014-03-20 (2.460) , 2014-03-21 (2.240) , 2014-03-22 (1.990) , 2014-03-23 (1.940) , 2014-03-24 (1.990) , 2014-03-25 (1.930) , 2014-04-06 (2.240) , 2014-04-08 (2.420) , 2014-04-09 (2.670) , 2014-04-11 (2.600) , 2014-04-15 (3.740) , 2014-04-17 (4.290) , 2014-04-18 (4.520)

1143370;9;2012-01-27 (2.590) , 2012-01-28 (2.520) , 2012-01-29 (2.670) , 2012-01-30 (2.690) , 2012-01-31 (2.730) , 2012-02-01 (2.790) , 2012-02-04 (2.130) , 2012-02-07 (1.830) , 2012-02-10 (1.880)

1143408;21;2014-01-08 (6.060) , 2014-01-09 (6.850) , 2014-01-09 (6.680) , 2014-01-10 (5.260) , 2014-01-11 (4.280) , 2014-01-12 (4.910) , 2014-01-13 (5.360) , 2014-01-14 (5.170) , 2014-01-15 (4.420) , 2014-01-16 (3.330) , 2014-01-17 (2.730) , 2014-01-18 (2.650) , 2014-01-19 (2.980) , 2014-01-20 (2.510) , 2014-01-21 (2.630) , 2014-01-22 (2.750) , 2014-01-23 (2.990) , 2014-01-27 (3.760) , 2014-01-28 (3.790) , 2014-01-30 (5.170) , 2014-01-30 (5.230)

1143477;6;2012-08-24 (3.370) , 2012-08-25 (3.600) , 2012-08-25 (3.620) , 2012-08-26 (3.160) , 2012-08-27 (2.600) , 2012-08-30 (1.860)

114360;11;2012-02-21 (1.570) , 2012-02-24 (1.640) , 2012-02-27 (1.500) , 2012-03-05 (1.590) , 2012-03-08 (1.730) , 2012-03-12 (1.620) , 2012-03-19 (1.540) , 2012-03-23 (1.670) , 2012-04-05 (1.880) , 2012-04-12 (1.550) , 2012-05-21 (1.850)

Edited May 12, 2015 by paolo123

paolo123 · May 12, 2015

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

Barand · May 12, 2015

It happened because all six readings were taken on the same date and so there is a group of records with the same startdate and a count > 5.

Now to find a cure :confused:

paolo123 · May 12, 2015

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

Barand · May 12, 2015

I was using @N to count the records in each group but realized that I could do that without the @N

However I have now put it back as an incrementing group identifier so I am not relying on the dates. This should cure the "six-in-one-day" problem

SELECT 
      User
    , COUNT(*) as `Count`
    , GROUP_CONCAT(datarichiesta,' (',result,')' ORDER BY datarichiesta SEPARATOR ' , ') as Dates
FROM
    (
    SELECT 
      datarichiesta
    , @N := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10)
        OR (datarichiesta > @startdate + INTERVAL 6 MONTH)
        , @N+1, @N) as groupno
    , @startdate := IF((codicepz<>@prevuser) OR (@prevres < 10) OR (risultato < 10)
        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 = 'a1'
    ORDER BY codicepz, datarichiesta
    ) as detail
GROUP BY user,groupno
HAVING `Count` >= 5;

paolo123 · May 12, 2015

:happy-04: Thanks!!

Barand · May 12, 2015

I forgot to initialize the @startdate variable

Change

SELECT @N:=0, @prevres:=NULL, @prevuser:=NULL

to

SELECT @startdate:=NULL, @N:=0, @prevres:=NULL, @prevuser:=NULL

paolo123 · May 12, 2015

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

Barand · May 12, 2015

Yes. Each record in a run of results meeting the criteria is allocated the number of that group. When the run ends the group number is incremented and continues being incremented until we hit another group. We then GROUP BY the group number (@N) to get the group count and concatenate the records in the group

paolo123 · May 12, 2015

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;

Barand · May 12, 2015

Put it with the existing WHERE

WHERE descrprestaz = 'Creatinina' AND eta = 650000

650000 ??? - how are you storing eta (age?) ?

paolo123 · May 12, 2015

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.

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information