Count date near

[sal21]

i have a 2 table

tab1
field1 field2
1234
1458
.....

tab2
field1 date1
1234 20/01/2014
1234 22/01/2014
1458 11/11/2013
1234 21/01/2014
1234 26/01/2014
.....

with a sql....
i need to chech if for all grouped value in tab2 in field1 exist a steep of consecutive dates>=3 and insert in the correspondence of field2 in tab1 the value "S"

In this case the condition is true, beacause:

1234 20/01/2014
1234 21/01/2014
1234 22/01/2014
1234 26/01/2014

Possible?

[HansV]

How many records will tab2 have?

[sal21]

How many records will tab2 have?

Aapprox 200.xxx

[HansV]

That will probably take a lot of time! Does the following work?

UPDATE ((tab1 INNER JOIN tab2 ON tab1.field1 = tab2.field1) INNER JOIN tab2 AS tab2_1 ON tab2.field1 = tab2_1.field1 AND tab2.date1 = tab2_1.date1+1) INNER JOIN tab2 AS tab2_2 ON tab2_1.field1 = tab2_2.field1 AND tab2_1.date1 = tab2_2.date1+1 SET tab1.field2 = "S"

or

UPDATE tab1 SET field2 = "S" WHERE EXISTS (SELECT tab2.field1 FROM (tab2 INNER JOIN tab2 AS tab2_1 ON tab2.field1 = tab2_1.field1 AND tab2.date1 = tab2_1.date1-1) INNER JOIN tab2 AS tab2_2 ON tab2.field1 = tab2_2.field1 AND tab2.date1 = tab2_2.date1-2 WHERE tab2.field1=tab1.field1)

[sal21]

That will probably take a lot of time! Does the following work?

UPDATE ((tab1 INNER JOIN tab2 ON tab1.field1 = tab2.field1) INNER JOIN tab2 AS tab2_1 ON tab2.field1 = tab2_1.field1 AND tab2.date1 = tab2_1.date1+1) INNER JOIN tab2 AS tab2_2 ON tab2_1.field1 = tab2_2.field1 AND tab2_1.date1 = tab2_2.date1+1 SET tab1.field2 = "S"

or

UPDATE tab1 SET field2 = "S" WHERE EXISTS (SELECT tab2.field1 FROM (tab2 INNER JOIN tab2 AS tab2_1 ON tab2.field1 = tab2_1.field1 AND tab2.date1 = tab2_1.date1-1) INNER JOIN tab2 AS tab2_2 ON tab2.field1 = tab2_2.field1 AND tab2.date1 = tab2_2.date1-2 WHERE tab2.field1=tab1.field1)

Hans tks as usual for code....
But i need to update field2 not with "S" but with the numeric value of matching....

in my case 1234 have 3 consecutive dates ...

Note:
i cannot test now but moonday, sure i can:-)

[HansV]

What would you want to update field2 to if there is a run of 3 consecutive dates and another run of 4 consecutive dates? Or if there are 2 runs of 3 consecutive dates?

[Rudi]

Note:
i cannot test now but moonday, sure i can:-)

Hi Sal, so that will be in 29.5 days time?

Spoiler

Sorry, I just could not resist. It was itching for a comment
How Long Is a Moon Day?

[sal21]

What would you want to update field2 to if there is a run of 3 consecutive dates and another run of 4 consecutive dates? Or if there are 2 runs of 3 consecutive dates?

only with the numeric value....


if 1234 have 3 consecutive dates update the filed2 of table1 with 3, and with 4 if the consecutive dates are 4, ecc....
note:
field2 in table1have integer property

[HansV]

But what if there is a run of 3 consecutive dates and another run of 4 consecutive dates, both for field1=1234?

S0553.png

[sal21]

But what if there is a run of 3 consecutive dates and another run of 4 consecutive dates, both for field1=1234?

S0553.png

NO....
not consider other discontinous dates...

important is if in the range of dates, based the first smaller orderd, date have>= 3.....

in your case:
7, 8 and 9 febrary are sufficient

[HansV]

With 200000 records, that is going to be REALLY EXTREMELY slow, Sal. Do you really need this?

[HansV]

It could be done using two queries and a temporary table:

1) A make-table query to create a temporary table:

SELECT Q.field1, Min(Q.date1) AS StartDate, Max(Q.date1)-MIN(Q.date1)+1 AS Run INTO NewTab
FROM (SELECT T1.field1, T1.date1, Count(*) AS C FROM tab2 AS T1 INNER JOIN tab2 AS T2 ON T1.field1 = T2.field1 AND T1.date1 >= T2.date1 GROUP BY T1.field1, T1.date1) AS Q
GROUP BY Q.field1, Q.date1-Q.C
HAVING Max(Q.date1)-MIN(Q.date1)>1

2) An update query that fills field2 in tab1 from the temporary table:

UPDATE NewTab INNER JOIN tab1 ON NewTab.field1 = tab1.field1 SET tab1.field2 = [NewTab].[Run]
WHERE (((NewTab.StartDate)=DMin("StartDate","NewTab","field1='" & [NewTab].[field1] & "'")))

But, as I mentioned, I fear it will be extremely slow if tab2 is large.

[HansV]

Sal sent me his real data. The queries are much faster than I had expected. The make-table query takes less than half a minute or so on my computer to process almost 370,000 records, the update query takes less than 10 seconds.

Make-table query:

SELECT Q.CARTA, Min(Q.DATA_CONT) AS StartDate, Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)+1 AS Run INTO NewTab
FROM (SELECT T1.CARTA, T1.DATA_CONT, Count(*) AS C FROM T3541 AS T1 INNER JOIN T3541 AS T2 ON T1.CARTA = T2.CARTA AND T1.DATA_CONT >= T2.DATA_CONT GROUP BY T1.CARTA, T1.DATA_CONT) AS Q
GROUP BY Q.CARTA, Q.DATA_CONT-Q.C
HAVING Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)>1;

Update query:

UPDATE NewTab INNER JOIN DB_CARTE ON NewTab.CARTA = DB_CARTE.CARTA SET DB_CARTE.S = [NewTab].[Run]
WHERE (((NewTab.StartDate)=DMin("StartDate","NewTab","CARTA='" & [NewTab].[CARTA] & "'")));

[Rudi]

So I have to ask then...is it just raw processing power that got through this data so efficiently or was it improvements in the database engine, or just REALLY good SQL syntax

[HansV]

1) The SQL of the make-table query (courtesy of former Access MVP Vanderghast) is pretty neat.
2) Fortunately, there was an index on the relevant fields.
3) Having an 8-core CPU helps...

[sal21]

Sal sent me his real data. The queries are much faster than I had expected. The make-table query takes less than half a minute or so on my computer to process almost 370,000 records, the update query takes less than 10 seconds.

Make-table query:

SELECT Q.CARTA, Min(Q.DATA_CONT) AS StartDate, Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)+1 AS Run INTO NewTab
FROM (SELECT T1.CARTA, T1.DATA_CONT, Count(*) AS C FROM T3541 AS T1 INNER JOIN T3541 AS T2 ON T1.CARTA = T2.CARTA AND T1.DATA_CONT >= T2.DATA_CONT GROUP BY T1.CARTA, T1.DATA_CONT) AS Q
GROUP BY Q.CARTA, Q.DATA_CONT-Q.C
HAVING Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)>1;

Update query:

UPDATE NewTab INNER JOIN DB_CARTE ON NewTab.CARTA = DB_CARTE.CARTA SET DB_CARTE.S = [NewTab].[Run]
WHERE (((NewTab.StartDate)=DMin("StartDate","NewTab","CARTA='" & [NewTab].[CARTA] & "'")));

Hans, as usula the querys code work perfect!
tks!!!!

Note:
I forgot one important tips.
and this could speed up the first query ...
in DB_CARTE have a field SE with null values, and this is not important to retrive fasted data.
In this case i think is important to esclude when the qury make the service table or when execute the second query.
I think to insert a Where clausole similar ... Where SE not is null

[HansV]

Try this version - test carefully!

SELECT Q.CARTA, Min(Q.DATA_CONT) AS StartDate, Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)+1 AS Run INTO NewTab
FROM (SELECT T1.CARTA, T1.DATA_CONT, Count(*) AS C FROM T3541 AS T1 INNER JOIN T3541 AS T2 ON (T1.DATA_CONT >= T2.DATA_CONT) AND (T1.CARTA = T2.CARTA) WHERE Not T1.SE Is Null AND Not T2.SE Is Null GROUP BY T1.CARTA, T1.DATA_CONT) AS Q
GROUP BY Q.CARTA, Q.DATA_CONT-Q.C
HAVING Max(Q.DATA_CONT)-MIN(Q.DATA_CONT)>1;