Count date near
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Count date near
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?
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?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Count date near
HansV wrote:How many records will tab2 have?
Aapprox 200.xxx
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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)
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)
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Count date near
HansV wrote: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:-)
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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?
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Count date near
Hi Sal, so that will be in 29.5 days time?sal21 wrote:Note:
i cannot test now but moonday, sure i can:-)
Spoiler
Sorry, I just could not resist. It was itching for a comment
How Long Is a Moon Day?
How Long Is a Moon Day?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Count date near
only with the numeric value....HansV wrote: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?
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
But what if there is a run of 3 consecutive dates and another run of 4 consecutive dates, both for field1=1234?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Count date near
HansV wrote:But what if there is a run of 3 consecutive dates and another run of 4 consecutive dates, both for field1=1234?
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
With 200000 records, that is going to be REALLY EXTREMELY slow, Sal. Do you really need this?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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.
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.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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] & "'")));
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] & "'")));
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Count date near
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
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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...
2) Fortunately, there was an index on the relevant fields.
3) Having an 8-core CPU helps...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4362
- Joined: 26 Apr 2010, 17:36
Re: Count date near
Hans, as usula the querys code work perfect!HansV wrote: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] & "'")));
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
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count date near
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;
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;
Best wishes,
Hans
Hans