Jump to content

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

paolo123

By paolo123
in MySQL Help

Recommended Posts

paolo123 Newbie

paolo123

Posted
Posted

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

  • Replies 54
  • Created
  • Last Reply
Barand Prolific Member

Barand

Posted
Posted

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)

Barand Prolific Member

Barand

Posted
Posted

 

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

Barand Prolific Member

Barand

Posted
Posted

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)                                                       |
+------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
paolo123 Newbie

paolo123

Posted
  • Author
Posted

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 Prolific Member

Barand

Posted
Posted

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 Newbie

paolo123

Posted
  • Author
Posted

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

paolo123

Posted
  • Author
Posted

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(8) 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(8) Sì  NULL     Medico varchar(55) Sì  NULL    
Barand Prolific Member

Barand

Posted
Posted

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;
Barand Prolific Member

Barand

Posted
Posted

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
Barand Prolific Member

Barand

Posted
Posted

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 Newbie

paolo123

Posted
  • Author
Posted

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;

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.