If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Reference to next record within a group
Michel,
As I said in my last post the new starting Sequence query using the tempory tables returned 1546 records for my small data sample. However, when I ran it thru the original starting sequence it returned only 721 records. Something is not right. I believe the original starting sequence query is correct as I manually verified the final result. For your information, my small data sample has 1791 records. There are 210 records considered as duplicate as a mbr can have two or more different medications filled on the same day for the same days supply. Simon "Simon L" wrote: Michel, The Starting Sequenc query that you just suggested worked and returned with 1546 records. Some sample records he memberID NextFillDate 940070653-01 10/21/2006 940070653-01 11/30/2006 940070653-01 12/16/2006 940070653-01 1/1/2007 940070653-01 1/17/2007 940076306-01 3/4/2006 940076306-01 4/8/2006 940076306-01 5/17/2006 940076306-01 6/24/2006 940076306-01 8/1/2006 940076306-01 9/12/2006 940076306-01 10/18/2006 940076306-01 11/21/2006 940076306-01 12/25/2006 I tried the Ending sequence with the following query but I got the same error message of " At most one record can be returned by this query." SELECT DISTINCT a.memberID, a.NextFillDate FROM [Test 1] AS a WHERE 0=(SELECT COUNT(*) FROM [Test 1] AS b WHERE a.memberID=b.memberID AND b.FillDate = a.NextFillDate) - (SELECT sq1Count FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) If I changed it to the following, I got zero record return. SELECT DISTINCT a.memberID, a.NextFillDate FROM [Test 1] AS a WHERE 0=(SELECT COUNT(*) FROM [Test 1] AS b WHERE a.memberID=b.memberID AND b.FillDate = a.NextFillDate) - (SELECT MAX(sq1Count) FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) I feel like we are getting close. Please bear with me as I am trying to learn this. I truly appreciate your help. Simon "Michel Walsh" wrote: That is bad news, we should have only one record for a given memberID and a given NextFillDate. Hoping for the best, but I suspect the result may not be right, try: SELECT DISTINCT a.memberID, a.NextFillDate FROM [Test 1] AS a WHERE 0=(SELECT MAX(sq2Count) FROM tempCountFillDate AS b WHERE a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) - (SELECT MAX(sq1Count) FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) Vanderghast, Access MVP "Simon L" wrote in message ... Michel, Thanks again for the response. I did test the two temp tables on a small set of data and both tables returned the results. However when I tried to run the starting sequence using the following query, it gave me an error message, " At most one record can be returned by this query." What did I do wrong? SELECT DISTINCT a.memberID, a.NextFillDate FROM [Test 1] AS a WHERE 0=(SELECT sq2Count FROM tempCountFillDate AS b WHERE a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) - (SELECT sq1Count FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) Simon "Michel Walsh" wrote: You make the temporary tables, first, on the small data set. Then, you run the new MedStartingSequence on the small data set to see if it gives the same result (as it should). Next, you repeat on the real data set, but instead of checking the validity of the result, you check the time it takes to run. Indeed, if it takes as much time as previously, that is useless to continue in this direction. Hoping it may help, Vanderghast, Access MVP |
#22
|
|||
|
|||
Reference to next record within a group
If we restart from the working, but slow, solution (slow on a large set of data).
Create table tempCountNextFill with: SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count INTO tempCountNextFill FROM meds AS a INNER JOIN meds AS c ON a.memberID=c.memberID AND a.NextFillDate = c.NextFillDate GROUP BY a.memberID, a.NextFillDate Create table tempCountFillDate with: SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count INTO tempCountFillDate FROM meds AS a INNER JOIN meds AS b ON a.memberID = b.memberID AND b.fillDate = a.NextFillDate GROUP BY a.memberID, a.NextFillDate (you may add indexes on memberID and on NextFillDate fields, once the table are created, for each of these two temp tables). Remplace MedsEndingSequence memberID NextFillDate 940183763-01 2007.03.07 940183763-01 2007.08.02 940183763-01 2007.11.22 940183763-01 2007.12.24 940183763-01 2008.01.26 where its SQL statement was SELECT DISTINCT a.memberID, a.NextFillDate FROM meds AS a WHERE 0=(SELECT COUNT(*) FROM meds AS b WHERE b.FillDate = a.NextFillDate)-(SELECT COUNT(*) FROM meds AS c WHERE c.NextFillDate= a.NextFillDate); by: SELECT DISTINCT a.memberID, a.NextFillDate FROM meds AS a WHERE 0=(SELECT sq2Count FROM tempCountFillDate AS b WHERE a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) - (SELECT sq1Count FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) which produces exactly the same result, on the small data set. Check if this new query speed is acceptable. If not, we will have to forget that approach. On the other hand, if it becomes acceptable, we will have to change MedsStartingSequence in a similar way. Hoping it may help, Vanderghast, Access MVP |
#23
|
|||
|
|||
Reference to next record within a group
Michel,
Looks like we need to find another approach to handle a large number of records. The new Ending Sequence query worked beautifully for a small sample of 1791 records. The query completed in less than 10 sec. However, when I ran it (after created two temp tables) for the large data set (108902 records), it was still running after more than 5 hours and I had to terminate it. Simon "Michel Walsh" wrote: If we restart from the working, but slow, solution (slow on a large set of data). Create table tempCountNextFill with: SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count INTO tempCountNextFill FROM meds AS a INNER JOIN meds AS c ON a.memberID=c.memberID AND a.NextFillDate = c.NextFillDate GROUP BY a.memberID, a.NextFillDate Create table tempCountFillDate with: SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count INTO tempCountFillDate FROM meds AS a INNER JOIN meds AS b ON a.memberID = b.memberID AND b.fillDate = a.NextFillDate GROUP BY a.memberID, a.NextFillDate (you may add indexes on memberID and on NextFillDate fields, once the table are created, for each of these two temp tables). Remplace MedsEndingSequence memberID NextFillDate 940183763-01 2007.03.07 940183763-01 2007.08.02 940183763-01 2007.11.22 940183763-01 2007.12.24 940183763-01 2008.01.26 where its SQL statement was SELECT DISTINCT a.memberID, a.NextFillDate FROM meds AS a WHERE 0=(SELECT COUNT(*) FROM meds AS b WHERE b.FillDate = a.NextFillDate)-(SELECT COUNT(*) FROM meds AS c WHERE c.NextFillDate= a.NextFillDate); by: SELECT DISTINCT a.memberID, a.NextFillDate FROM meds AS a WHERE 0=(SELECT sq2Count FROM tempCountFillDate AS b WHERE a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) - (SELECT sq1Count FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) which produces exactly the same result, on the small data set. Check if this new query speed is acceptable. If not, we will have to forget that approach. On the other hand, if it becomes acceptable, we will have to change MedsStartingSequence in a similar way. Hoping it may help, Vanderghast, Access MVP |
#24
|
|||
|
|||
Reference to next record within a group
Can you give a last try with NewNewMedsEndingSequence:
SELECT DISTINCT a.memberID, a.NextFillDate FROM (meds AS a INNER JOIN tempCountFillDate AS b ON a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) INNER JOIN tempCountNextFill AS c ON a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate AND c.sq1Count = b.sq2.Count ( instead of the last try we did with NewMedsEndingSequence: SELECT DISTINCT a.memberID, a.NextFillDate FROM meds AS a WHERE 0=(SELECT sq2Count FROM tempCountFillDate AS b WHERE a.memberID=b.memberID AND b.NextFillDate =a.NextFillDate) - (SELECT sq1Count FROM tempCountNextFill AS c WHERE a.memberID=c.memberID AND c.NextFillDate= a.NextFillDate) ) And add individual index on each field of the two temp tables, as well as on memberID and NextFillDate of the original table Meds (if they are not already there). Hoping it may help, Vanderghast, Access MVP "Simon L" wrote in message ... Michel, Looks like we need to find another approach to handle a large number of records. The new Ending Sequence query worked beautifully for a small sample of 1791 records. The query completed in less than 10 sec. However, when I ran it (after created two temp tables) for the large data set (108902 records), it was still running after more than 5 hours and I had to terminate it. Simon |
#25
|
|||
|
|||
Reference to next record within a group
Note that the 'new' solution just cover half the job. Compare only the
running time of NewNewMedsEndingSequence versus the running time of MedsEndingSequence; NOT versus the whole problem. The running time of the whole problem will be roughly twice the amount of time this query takes (once we will optimize the second part too). Vanderghast, Access MVP |
Thread Tools | |
Display Modes | |
|
|