|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
problem: select Max(date) which is less than actual Max(date) ?recID int, curDate datetime, expDate datetime) insert into #temp select 1, '11/1/05', '1/5/06' union select 1, '11/5/05', '1/13/06' union select 1, '11/12/05', '1/25/06' union select 1, '11/28/05', '1/25/06' union select 2, '11/7/05', '1/7/06' union select 2, '11/13/05', '1/12/06' union select 2, '11/27/05', '1/15/06' union select 2, '12/1/05', '2/1/06' union select 3, '11/3/05', '1/7/06' union select 3, '11/8/05', '1/12/06' union select 3, '11/17/05', '1/23/06' union select 3, '12/1/05', '2/1/06' union select 4, '11/5/05', '1/3/06' union select 4, '11/9/05', '1/7/06' union select 4, '11/19/05', '1/14/06' union select 4, '11/23/05', '1/25/06' union select 5, '11/5/05', '1/3/06' union select 5, '11/9/05', '1/7/06' union select 5, '11/19/05', '1/25/06' union select 5, '11/27/05', '1/25/06' union select 6, '11/5/05', '1/3/06' union select 6, '11/9/05', '1/7/06' union select 6, '11/19/05', '1/25/06' union select 6, '1/5/05', '1/25/06' union select 6, '11/27/05', '2/1/06' SELECT recid, Max(curDate) curDate, MAX(expDate) expDate FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06' --gives the correct result for this query but....... recID curDate expDate 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 where recIDs 2, 3, and 6 contain a Max(expDate) which is greater than 1/25/06, but recID 6 contains a curdate which is greater than the curdate for its Max(expdate) select 6, '1/5/05', '1/25/06' union select 6, '11/27/05', '2/1/06' I need to add criteria to my query above which will select the Max(expDate) for a given expDate (1/25/05 here) but wil also select that same expDate for a recID which contains a greater expDate than 1/25/06 if the curdate for that recID is greater than the curDate for the Max(expDate) of that recID. What can I add to my query so that the result set above would be recID curDate expDate 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 which would include recID 6 for the expDate of 1/25/05 which is not the actual Max(expDate) but is the expdate for Max(curDate) of recID 6? Thanks, Rich Rich,
This is just a wee bit hard for me to sort out. One part in particular has me especially confused. The total set of data for recID = 6 is: >select 6, '11/5/05', '1/3/06' union Your desired result includes:>select 6, '11/9/05', '1/7/06' union >select 6, '11/19/05', '1/25/06' union >select 6, '1/5/05', '1/25/06' union >select 6, '11/27/05', '2/1/06' >recID curDate expDate I simply do not see where a curDate in 2006 came from that input data.>6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 Your specs seem to talk about using a different value for expDate under specific circumstances, but I was unable to see anything about using any different calculation for curDate than a value of curDate. Roy Harvey Beacon Falls, CT based on the data at the beginning of this thread I use this query and get
this result SELECT recid, Max(curDate) curDate, MAX(expDate) expDate FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06' --gives the correct result for this query but....... recID curDate expDate 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 I am selecting only rows where the Max(expDate) is '1/25/06'. That would include rows with recID of 1, 4, and 5 since recID's 2, 3, and 6 have a Max(expDate) of '2/1/06' which is greater than '1/25/06'. Well, I need to write another query that selects not only the rows with a Max(expDate) of '1/25/06', but will also select rows that contain an expDate of '1/25/06' --- IF ----- the corresponding curDate for that row and recID is the Max(curDate) for that recID for the corresponding expDate of '1/25/06'. recID 6 contains a row with expDate of '1/25/06' which is not its Max(expDate). However, the corresponding curDate for that row is '1/5/06' OK. I see your problem. I screwed up. The curDate was supposed to be '1/5/06' which would make it the Max(curDate) for recID 6 for the corresponding expdate of '1/25/06'. So I need to pick up that row in addition to the rows that have a Max(expDate) of '1/25/06', even though for recID 6 expDate of 1/25/06 is not the Max(expDate), but curDate is Max(curDate) for recID 6 expDate 1/25/06. Sorry for the goof up. Any suggestions how I could write such a query? Show quote "Roy Harvey" wrote: > Rich, > > This is just a wee bit hard for me to sort out. One part in > particular has me especially confused. > > The total set of data for recID = 6 is: > > >select 6, '11/5/05', '1/3/06' union > >select 6, '11/9/05', '1/7/06' union > >select 6, '11/19/05', '1/25/06' union > >select 6, '1/5/05', '1/25/06' union > >select 6, '11/27/05', '2/1/06' > > Your desired result includes: > >recID curDate expDate > >6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 > > I simply do not see where a curDate in 2006 came from that input data. > Your specs seem to talk about using a different value for expDate > under specific circumstances, but I was unable to see anything about > using any different calculation for curDate than a value of curDate. > > Roy Harvey > Beacon Falls, CT > Here is what I came up with, including the last couple of lines of
data load with the correction. The basic idea is to write it as two queries with a UNION. The first part is what you already have working. The second part tries to get the row that meets the secondary criteria. Note that the first query is pretty much duplicated in the second for the NOT IN test. While it appears to return the result you wanted from the data provided, I do not guarantee it will do as well when you have the real data. select 6, '1/5/06', '1/25/06' union -- corrected curDate select 6, '11/27/05', '2/1/06' SELECT recid, Max(curDate) curDate, MAX(expDate) expDate FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06' UNION ALL SELECT * FROM #temp as X WHERE recid NOT IN --the list of recid from beginning (SELECT recid FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06') AND expDate = '1/25/06' AND curDate >= (select curDate from #temp as Y where X.recid = Y.recid and Y.expDate = (select max(expDate) from #temp as Z where X.recid = Z.recid)) Roy Harvey Beacon Falls, CT Thanks very much. The Union query was the route I was going to go, but I
thought that maybe there a more sophisticated way to go. I guess I need to have a little more confidence in myself. Actually, the way you did it is fairly sophisticated. My idea of using a Union query was way more verbose, and thus, I was trying to avoid it. But you nailed it thanks very much for your help. Rich Show quote "Roy Harvey" wrote: > Here is what I came up with, including the last couple of lines of > data load with the correction. The basic idea is to write it as two > queries with a UNION. The first part is what you already have > working. The second part tries to get the row that meets the > secondary criteria. Note that the first query is pretty much > duplicated in the second for the NOT IN test. > > While it appears to return the result you wanted from the data > provided, I do not guarantee it will do as well when you have the real > data. > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > select 6, '11/27/05', '2/1/06' > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > FROM #temp > GROUP BY recid > HAVING MAX(expDate)='1/25/06' > UNION ALL > SELECT * > FROM #temp as X > WHERE recid NOT IN --the list of recid from beginning > (SELECT recid > FROM #temp > GROUP BY recid > HAVING MAX(expDate)='1/25/06') > AND expDate = '1/25/06' > AND curDate >= > (select curDate from #temp as Y > where X.recid = Y.recid > and Y.expDate = > (select max(expDate) from #temp as Z > where X.recid = Z.recid)) > > Roy Harvey > Beacon Falls, CT > If I may, I added one more twist to this query. I added one more row to
#temp where recID 2 now also has an expDate of 1/25/06 which is less than its max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that corresponds to the expDate of 1/25/06 - same situation at recID 6 where the curDate that corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks up recID 6. So what I need to do is modify your query to see if I can pick up recID 2 in addition to recID 6 along with the original recID's of 1, 4, 5. This is the part where I was afraid of getting verbose. I will experiment The real scenario has hundreds of thousands of records and there are rows that match my sample. The pain. Show quote "Rich" wrote: > Thanks very much. The Union query was the route I was going to go, but I > thought that maybe there a more sophisticated way to go. I guess I need to > have a little more confidence in myself. > > Actually, the way you did it is fairly sophisticated. My idea of using a > Union query was way more verbose, and thus, I was trying to avoid it. But > you nailed it > > thanks very much for your help. > > Rich > > "Roy Harvey" wrote: > > > Here is what I came up with, including the last couple of lines of > > data load with the correction. The basic idea is to write it as two > > queries with a UNION. The first part is what you already have > > working. The second part tries to get the row that meets the > > secondary criteria. Note that the first query is pretty much > > duplicated in the second for the NOT IN test. > > > > While it appears to return the result you wanted from the data > > provided, I do not guarantee it will do as well when you have the real > > data. > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > > select 6, '11/27/05', '2/1/06' > > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > FROM #temp > > GROUP BY recid > > HAVING MAX(expDate)='1/25/06' > > UNION ALL > > SELECT * > > FROM #temp as X > > WHERE recid NOT IN --the list of recid from beginning > > (SELECT recid > > FROM #temp > > GROUP BY recid > > HAVING MAX(expDate)='1/25/06') > > AND expDate = '1/25/06' > > AND curDate >= > > (select curDate from #temp as Y > > where X.recid = Y.recid > > and Y.expDate = > > (select max(expDate) from #temp as Z > > where X.recid = Z.recid)) > > > > Roy Harvey > > Beacon Falls, CT > > I must be trying too hard. Your query works fine with the additional new row
for recID 2. Sorry bout that. Show quote "Rich" wrote: > If I may, I added one more twist to this query. I added one more row to > #temp where recID 2 now also has an expDate of 1/25/06 which is less than its > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that corresponds > to the expDate of 1/25/06 - same situation at recID 6 where the curDate that > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 > Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks > up recID 6. > > So what I need to do is modify your query to see if I can pick up recID 2 in > addition to recID 6 along with the original recID's of 1, 4, 5. > > This is the part where I was afraid of getting verbose. I will experiment > The real scenario has hundreds of thousands of records and there are rows > that match my sample. The pain. > > > > "Rich" wrote: > > > Thanks very much. The Union query was the route I was going to go, but I > > thought that maybe there a more sophisticated way to go. I guess I need to > > have a little more confidence in myself. > > > > Actually, the way you did it is fairly sophisticated. My idea of using a > > Union query was way more verbose, and thus, I was trying to avoid it. But > > you nailed it > > > > thanks very much for your help. > > > > Rich > > > > "Roy Harvey" wrote: > > > > > Here is what I came up with, including the last couple of lines of > > > data load with the correction. The basic idea is to write it as two > > > queries with a UNION. The first part is what you already have > > > working. The second part tries to get the row that meets the > > > secondary criteria. Note that the first query is pretty much > > > duplicated in the second for the NOT IN test. > > > > > > While it appears to return the result you wanted from the data > > > provided, I do not guarantee it will do as well when you have the real > > > data. > > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > > > select 6, '11/27/05', '2/1/06' > > > > > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > > FROM #temp > > > GROUP BY recid > > > HAVING MAX(expDate)='1/25/06' > > > UNION ALL > > > SELECT * > > > FROM #temp as X > > > WHERE recid NOT IN --the list of recid from beginning > > > (SELECT recid > > > FROM #temp > > > GROUP BY recid > > > HAVING MAX(expDate)='1/25/06') > > > AND expDate = '1/25/06' > > > AND curDate >= > > > (select curDate from #temp as Y > > > where X.recid = Y.recid > > > and Y.expDate = > > > (select max(expDate) from #temp as Z > > > where X.recid = Z.recid)) > > > > > > Roy Harvey > > > Beacon Falls, CT > > > so, taking this to the nth level (for posterity incase I need to refer to
this again), I added yet one more row to recID 2. This additional row is a 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06. One of these rows contains a Max(curDate) for recID 2. create table #temp( recID int, curDate datetime, expDate datetime) insert into #temp select 1, '11/1/05', '1/5/06' union select 1, '11/5/05', '1/13/06' union select 1, '11/12/05', '1/25/06' union select 1, '11/28/05', '1/25/06' union select 2, '11/7/05', '1/7/06' union select 2, '11/13/05', '1/12/06' union select 2, '11/27/05', '1/15/06' union select 2, '1/7/06', '1/25/06' union select 2, '1/8/06', '1/25/06' union select 2, '12/1/05', '2/1/06' union select 3, '11/3/05', '1/7/06' union select 3, '11/8/05', '1/12/06' union select 3, '11/17/05', '1/23/06' union select 3, '12/1/05', '2/1/06' union select 4, '11/5/05', '1/3/06' union select 4, '11/9/05', '1/7/06' union select 4, '11/19/05', '1/14/06' union select 4, '11/23/05', '1/25/06' union select 5, '11/5/05', '1/3/06' union select 5, '11/9/05', '1/7/06' union select 5, '11/19/05', '1/25/06' union select 5, '11/27/05', '1/25/06' union select 6, '11/5/05', '1/3/06' union select 6, '11/9/05', '1/7/06' union select 6, '11/19/05', '1/25/06' union select 6, '1/5/06', '1/25/06' union select 6, '11/27/05', '2/1/06' The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and to also retrieve rows that contain an expdate of 1/25/06 that is not the Max(expDate) but these additional rows contain a curdate in the same row as the expDate of 1/25/06 which is the Max(curDate) for that recID. In this sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of those 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) for the rows that contain an expDate of 1/25/06. What I did was to modify Roy's query as follows: SELECT recid, Max(curDate) curDate, MAX(expDate) expDate FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06' UNION ALL select recID, Max(curDate) m, expdate from --I added this line (SELECT * FROM #temp as X WHERE recid NOT IN --the list of recid from beginning (SELECT recid FROM #temp GROUP BY recid HAVING MAX(expDate)='1/25/06') AND expDate = '1/25/06' AND curDate > (select curDate from #temp as Y where X.recid = Y.recid and Y.expDate = (select max(expDate) from #temp as Z where X.recid = Z.recid))) t1 Group By recid, expdate --this gives recID curDate expDate 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 Show quote "Rich" wrote: > I must be trying too hard. Your query works fine with the additional new row > for recID 2. Sorry bout that. > > "Rich" wrote: > > > If I may, I added one more twist to this query. I added one more row to > > #temp where recID 2 now also has an expDate of 1/25/06 which is less than its > > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that corresponds > > to the expDate of 1/25/06 - same situation at recID 6 where the curDate that > > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 > > Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks > > up recID 6. > > > > So what I need to do is modify your query to see if I can pick up recID 2 in > > addition to recID 6 along with the original recID's of 1, 4, 5. > > > > This is the part where I was afraid of getting verbose. I will experiment > > The real scenario has hundreds of thousands of records and there are rows > > that match my sample. The pain. > > > > > > > > "Rich" wrote: > > > > > Thanks very much. The Union query was the route I was going to go, but I > > > thought that maybe there a more sophisticated way to go. I guess I need to > > > have a little more confidence in myself. > > > > > > Actually, the way you did it is fairly sophisticated. My idea of using a > > > Union query was way more verbose, and thus, I was trying to avoid it. But > > > you nailed it > > > > > > thanks very much for your help. > > > > > > Rich > > > > > > "Roy Harvey" wrote: > > > > > > > Here is what I came up with, including the last couple of lines of > > > > data load with the correction. The basic idea is to write it as two > > > > queries with a UNION. The first part is what you already have > > > > working. The second part tries to get the row that meets the > > > > secondary criteria. Note that the first query is pretty much > > > > duplicated in the second for the NOT IN test. > > > > > > > > While it appears to return the result you wanted from the data > > > > provided, I do not guarantee it will do as well when you have the real > > > > data. > > > > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > > > > select 6, '11/27/05', '2/1/06' > > > > > > > > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > > > FROM #temp > > > > GROUP BY recid > > > > HAVING MAX(expDate)='1/25/06' > > > > UNION ALL > > > > SELECT * > > > > FROM #temp as X > > > > WHERE recid NOT IN --the list of recid from beginning > > > > (SELECT recid > > > > FROM #temp > > > > GROUP BY recid > > > > HAVING MAX(expDate)='1/25/06') > > > > AND expDate = '1/25/06' > > > > AND curDate >= > > > > (select curDate from #temp as Y > > > > where X.recid = Y.recid > > > > and Y.expDate = > > > > (select max(expDate) from #temp as Z > > > > where X.recid = Z.recid)) > > > > > > > > Roy Harvey > > > > Beacon Falls, CT > > > > This gives the same result:
select recid,max(curDate) curDate, expDate from #temp where expDate='1/25/06' group by recid,expDate -- Show quote-oj "Rich" <R***@discussions.microsoft.com> wrote in message news:7634C35E-CD6B-4E14-8D6E-7DBAE3861BAC@microsoft.com... > so, taking this to the nth level (for posterity incase I need to refer to > this again), I added yet one more row to recID 2. This additional row is > a > 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of > 1/25/06. > One of these rows contains a Max(curDate) for recID 2. > > > create table #temp( > recID int, curDate datetime, expDate datetime) > > insert into #temp > select 1, '11/1/05', '1/5/06' union > select 1, '11/5/05', '1/13/06' union > select 1, '11/12/05', '1/25/06' union > select 1, '11/28/05', '1/25/06' union > select 2, '11/7/05', '1/7/06' union > select 2, '11/13/05', '1/12/06' union > select 2, '11/27/05', '1/15/06' union > select 2, '1/7/06', '1/25/06' union > select 2, '1/8/06', '1/25/06' union > select 2, '12/1/05', '2/1/06' union > select 3, '11/3/05', '1/7/06' union > select 3, '11/8/05', '1/12/06' union > select 3, '11/17/05', '1/23/06' union > select 3, '12/1/05', '2/1/06' union > select 4, '11/5/05', '1/3/06' union > select 4, '11/9/05', '1/7/06' union > select 4, '11/19/05', '1/14/06' union > select 4, '11/23/05', '1/25/06' union > select 5, '11/5/05', '1/3/06' union > select 5, '11/9/05', '1/7/06' union > select 5, '11/19/05', '1/25/06' union > select 5, '11/27/05', '1/25/06' union > select 6, '11/5/05', '1/3/06' union > select 6, '11/9/05', '1/7/06' union > select 6, '11/19/05', '1/25/06' union > select 6, '1/5/06', '1/25/06' union > select 6, '11/27/05', '2/1/06' > > > The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and > to > also retrieve rows that contain an expdate of 1/25/06 that is not the > Max(expDate) but these additional rows contain a curdate in the same row > as > the expDate of 1/25/06 which is the Max(curDate) for that recID. In this > sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of > those > 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The > goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of > 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) > for > the rows that contain an expDate of 1/25/06. > > What I did was to modify Roy's query as follows: > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > FROM #temp > GROUP BY recid > HAVING MAX(expDate)='1/25/06' > UNION ALL > select recID, Max(curDate) m, expdate from --I added this line > (SELECT * FROM #temp as X > WHERE > recid NOT IN --the list of recid from beginning > (SELECT recid FROM #temp GROUP BY recid > HAVING MAX(expDate)='1/25/06') > AND > expDate = '1/25/06' > AND curDate > > (select curDate from #temp as Y > where X.recid = Y.recid > and Y.expDate = > (select max(expDate) from #temp as Z > where X.recid = Z.recid))) t1 > Group By recid, expdate > > --this gives > recID curDate expDate > 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 > 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 > 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 > 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 > 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 > > > > "Rich" wrote: > >> I must be trying too hard. Your query works fine with the additional new >> row >> for recID 2. Sorry bout that. >> >> "Rich" wrote: >> >> > If I may, I added one more twist to this query. I added one more row >> > to >> > #temp where recID 2 now also has an expDate of 1/25/06 which is less >> > than its >> > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that >> > corresponds >> > to the expDate of 1/25/06 - same situation at recID 6 where the curDate >> > that >> > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 >> > Max(curDate) of 1/7/06. The 2nd part of the current Union query only >> > picks >> > up recID 6. >> > >> > So what I need to do is modify your query to see if I can pick up recID >> > 2 in >> > addition to recID 6 along with the original recID's of 1, 4, 5. >> > >> > This is the part where I was afraid of getting verbose. I will >> > experiment >> > The real scenario has hundreds of thousands of records and there are >> > rows >> > that match my sample. The pain. >> > >> > >> > >> > "Rich" wrote: >> > >> > > Thanks very much. The Union query was the route I was going to go, >> > > but I >> > > thought that maybe there a more sophisticated way to go. I guess I >> > > need to >> > > have a little more confidence in myself. >> > > >> > > Actually, the way you did it is fairly sophisticated. My idea of >> > > using a >> > > Union query was way more verbose, and thus, I was trying to avoid it. >> > > But >> > > you nailed it >> > > >> > > thanks very much for your help. >> > > >> > > Rich >> > > >> > > "Roy Harvey" wrote: >> > > >> > > > Here is what I came up with, including the last couple of lines of >> > > > data load with the correction. The basic idea is to write it as >> > > > two >> > > > queries with a UNION. The first part is what you already have >> > > > working. The second part tries to get the row that meets the >> > > > secondary criteria. Note that the first query is pretty much >> > > > duplicated in the second for the NOT IN test. >> > > > >> > > > While it appears to return the result you wanted from the data >> > > > provided, I do not guarantee it will do as well when you have the >> > > > real >> > > > data. >> > > > >> > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate >> > > > select 6, '11/27/05', '2/1/06' >> > > > >> > > > >> > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate >> > > > FROM #temp >> > > > GROUP BY recid >> > > > HAVING MAX(expDate)='1/25/06' >> > > > UNION ALL >> > > > SELECT * >> > > > FROM #temp as X >> > > > WHERE recid NOT IN --the list of recid from beginning >> > > > (SELECT recid >> > > > FROM #temp >> > > > GROUP BY recid >> > > > HAVING MAX(expDate)='1/25/06') >> > > > AND expDate = '1/25/06' >> > > > AND curDate >= >> > > > (select curDate from #temp as Y >> > > > where X.recid = Y.recid >> > > > and Y.expDate = >> > > > (select max(expDate) from #temp as Z >> > > > where X.recid = Z.recid)) >> > > > >> > > > Roy Harvey >> > > > Beacon Falls, CT >> > > > On Thu, 2 Mar 2006 22:56:26 -0800, Rich
<R***@discussions.microsoft.com> wrote: >so, taking this to the nth level (for posterity incase I need to refer to Sounds like you have things well in hand. (I really don't have time>this again)..... to get into the logic any further.) My only thought, in light of oj's reply, is to add more variety to the test data. Make it a LOT more complicated. I'm sure you know about the problem of programmers only testing part of their logic because they use the application the say they think it should be used. Well, us DB folks can suffer from the same problem, testing with test cases that are only a subset of what the real world might hit us with. Good luck! Roy Harvey Beacon Falls, CT Thanks all for your replies. OJ's solution is less verbose than the one I
was working with, but at least we all got the idea what I was trying to do - at least I am starting to get the idea. And Roy is correct on making the data more complex to cover more twists. Believe me, the actual data is quite a bit more complex, but at least now I am chiseling down the queries that I need. Start out with verbose till I understand what I am looking for and then drill down to the less verbose. Show quote "Rich" wrote: > so, taking this to the nth level (for posterity incase I need to refer to > this again), I added yet one more row to recID 2. This additional row is a > 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06. > One of these rows contains a Max(curDate) for recID 2. > > > create table #temp( > recID int, curDate datetime, expDate datetime) > > insert into #temp > select 1, '11/1/05', '1/5/06' union > select 1, '11/5/05', '1/13/06' union > select 1, '11/12/05', '1/25/06' union > select 1, '11/28/05', '1/25/06' union > select 2, '11/7/05', '1/7/06' union > select 2, '11/13/05', '1/12/06' union > select 2, '11/27/05', '1/15/06' union > select 2, '1/7/06', '1/25/06' union > select 2, '1/8/06', '1/25/06' union > select 2, '12/1/05', '2/1/06' union > select 3, '11/3/05', '1/7/06' union > select 3, '11/8/05', '1/12/06' union > select 3, '11/17/05', '1/23/06' union > select 3, '12/1/05', '2/1/06' union > select 4, '11/5/05', '1/3/06' union > select 4, '11/9/05', '1/7/06' union > select 4, '11/19/05', '1/14/06' union > select 4, '11/23/05', '1/25/06' union > select 5, '11/5/05', '1/3/06' union > select 5, '11/9/05', '1/7/06' union > select 5, '11/19/05', '1/25/06' union > select 5, '11/27/05', '1/25/06' union > select 6, '11/5/05', '1/3/06' union > select 6, '11/9/05', '1/7/06' union > select 6, '11/19/05', '1/25/06' union > select 6, '1/5/06', '1/25/06' union > select 6, '11/27/05', '2/1/06' > > > The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and to > also retrieve rows that contain an expdate of 1/25/06 that is not the > Max(expDate) but these additional rows contain a curdate in the same row as > the expDate of 1/25/06 which is the Max(curDate) for that recID. In this > sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of those > 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The > goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of > 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) for > the rows that contain an expDate of 1/25/06. > > What I did was to modify Roy's query as follows: > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > FROM #temp > GROUP BY recid > HAVING MAX(expDate)='1/25/06' > UNION ALL > select recID, Max(curDate) m, expdate from --I added this line > (SELECT * FROM #temp as X > WHERE > recid NOT IN --the list of recid from beginning > (SELECT recid FROM #temp GROUP BY recid > HAVING MAX(expDate)='1/25/06') > AND > expDate = '1/25/06' > AND curDate > > (select curDate from #temp as Y > where X.recid = Y.recid > and Y.expDate = > (select max(expDate) from #temp as Z > where X.recid = Z.recid))) t1 > Group By recid, expdate > > --this gives > recID curDate expDate > 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 > 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 > 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 > 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 > 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 > > > > "Rich" wrote: > > > I must be trying too hard. Your query works fine with the additional new row > > for recID 2. Sorry bout that. > > > > "Rich" wrote: > > > > > If I may, I added one more twist to this query. I added one more row to > > > #temp where recID 2 now also has an expDate of 1/25/06 which is less than its > > > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that corresponds > > > to the expDate of 1/25/06 - same situation at recID 6 where the curDate that > > > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 > > > Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks > > > up recID 6. > > > > > > So what I need to do is modify your query to see if I can pick up recID 2 in > > > addition to recID 6 along with the original recID's of 1, 4, 5. > > > > > > This is the part where I was afraid of getting verbose. I will experiment > > > The real scenario has hundreds of thousands of records and there are rows > > > that match my sample. The pain. > > > > > > > > > > > > "Rich" wrote: > > > > > > > Thanks very much. The Union query was the route I was going to go, but I > > > > thought that maybe there a more sophisticated way to go. I guess I need to > > > > have a little more confidence in myself. > > > > > > > > Actually, the way you did it is fairly sophisticated. My idea of using a > > > > Union query was way more verbose, and thus, I was trying to avoid it. But > > > > you nailed it > > > > > > > > thanks very much for your help. > > > > > > > > Rich > > > > > > > > "Roy Harvey" wrote: > > > > > > > > > Here is what I came up with, including the last couple of lines of > > > > > data load with the correction. The basic idea is to write it as two > > > > > queries with a UNION. The first part is what you already have > > > > > working. The second part tries to get the row that meets the > > > > > secondary criteria. Note that the first query is pretty much > > > > > duplicated in the second for the NOT IN test. > > > > > > > > > > While it appears to return the result you wanted from the data > > > > > provided, I do not guarantee it will do as well when you have the real > > > > > data. > > > > > > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > > > > > select 6, '11/27/05', '2/1/06' > > > > > > > > > > > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > > > > FROM #temp > > > > > GROUP BY recid > > > > > HAVING MAX(expDate)='1/25/06' > > > > > UNION ALL > > > > > SELECT * > > > > > FROM #temp as X > > > > > WHERE recid NOT IN --the list of recid from beginning > > > > > (SELECT recid > > > > > FROM #temp > > > > > GROUP BY recid > > > > > HAVING MAX(expDate)='1/25/06') > > > > > AND expDate = '1/25/06' > > > > > AND curDate >= > > > > > (select curDate from #temp as Y > > > > > where X.recid = Y.recid > > > > > and Y.expDate = > > > > > (select max(expDate) from #temp as Z > > > > > where X.recid = Z.recid)) > > > > > > > > > > Roy Harvey > > > > > Beacon Falls, CT > > > > > Well, OJ's less verbose query isn't working on my actual data because I am
picking up rows with expDates that are not Max(expDate) for a given recID where the curDate is not the Max(curDate) for the given recID and expDate. I need to pick up rows where the Max(expDate) is 1/25/06 or if a recID contains a newer Max(expDate) than 1/25/06, I pick that row/recID up if the corresponding curDate for the row with expdate = 1/25/06 is greater than the curdate for the row containing the newer expDate for the given recID. So it looks like I will have to go with the more verbose query. I was hoping it would be a simple query, but no such luck. Oh well, at least one of the solutions will work for my data. Show quote "Rich" wrote: > Thanks all for your replies. OJ's solution is less verbose than the one I > was working with, but at least we all got the idea what I was trying to do - > at least I am starting to get the idea. And Roy is correct on making the > data more complex to cover more twists. Believe me, the actual data is > quite a bit more complex, but at least now I am chiseling down the queries > that I need. Start out with verbose till I understand what I am looking for > and then drill down to the less verbose. > > "Rich" wrote: > > > so, taking this to the nth level (for posterity incase I need to refer to > > this again), I added yet one more row to recID 2. This additional row is a > > 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of 1/25/06. > > One of these rows contains a Max(curDate) for recID 2. > > > > > > create table #temp( > > recID int, curDate datetime, expDate datetime) > > > > insert into #temp > > select 1, '11/1/05', '1/5/06' union > > select 1, '11/5/05', '1/13/06' union > > select 1, '11/12/05', '1/25/06' union > > select 1, '11/28/05', '1/25/06' union > > select 2, '11/7/05', '1/7/06' union > > select 2, '11/13/05', '1/12/06' union > > select 2, '11/27/05', '1/15/06' union > > select 2, '1/7/06', '1/25/06' union > > select 2, '1/8/06', '1/25/06' union > > select 2, '12/1/05', '2/1/06' union > > select 3, '11/3/05', '1/7/06' union > > select 3, '11/8/05', '1/12/06' union > > select 3, '11/17/05', '1/23/06' union > > select 3, '12/1/05', '2/1/06' union > > select 4, '11/5/05', '1/3/06' union > > select 4, '11/9/05', '1/7/06' union > > select 4, '11/19/05', '1/14/06' union > > select 4, '11/23/05', '1/25/06' union > > select 5, '11/5/05', '1/3/06' union > > select 5, '11/9/05', '1/7/06' union > > select 5, '11/19/05', '1/25/06' union > > select 5, '11/27/05', '1/25/06' union > > select 6, '11/5/05', '1/3/06' union > > select 6, '11/9/05', '1/7/06' union > > select 6, '11/19/05', '1/25/06' union > > select 6, '1/5/06', '1/25/06' union > > select 6, '11/27/05', '2/1/06' > > > > > > The goal is to retrieve only the rows where Max(expDate) is 1/25/06, and to > > also retrieve rows that contain an expdate of 1/25/06 that is not the > > Max(expDate) but these additional rows contain a curdate in the same row as > > the expDate of 1/25/06 which is the Max(curDate) for that recID. In this > > sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of those > > 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. The > > goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) of > > 1/25/06 and also rows for recID 2 and recID6 which contain a Max(curDate) for > > the rows that contain an expDate of 1/25/06. > > > > What I did was to modify Roy's query as follows: > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > FROM #temp > > GROUP BY recid > > HAVING MAX(expDate)='1/25/06' > > UNION ALL > > select recID, Max(curDate) m, expdate from --I added this line > > (SELECT * FROM #temp as X > > WHERE > > recid NOT IN --the list of recid from beginning > > (SELECT recid FROM #temp GROUP BY recid > > HAVING MAX(expDate)='1/25/06') > > AND > > expDate = '1/25/06' > > AND curDate > > > (select curDate from #temp as Y > > where X.recid = Y.recid > > and Y.expDate = > > (select max(expDate) from #temp as Z > > where X.recid = Z.recid))) t1 > > Group By recid, expdate > > > > --this gives > > recID curDate expDate > > 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 > > 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 > > 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 > > 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 > > 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 > > > > > > > > "Rich" wrote: > > > > > I must be trying too hard. Your query works fine with the additional new row > > > for recID 2. Sorry bout that. > > > > > > "Rich" wrote: > > > > > > > If I may, I added one more twist to this query. I added one more row to > > > > #temp where recID 2 now also has an expDate of 1/25/06 which is less than its > > > > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that corresponds > > > > to the expDate of 1/25/06 - same situation at recID 6 where the curDate that > > > > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 > > > > Max(curDate) of 1/7/06. The 2nd part of the current Union query only picks > > > > up recID 6. > > > > > > > > So what I need to do is modify your query to see if I can pick up recID 2 in > > > > addition to recID 6 along with the original recID's of 1, 4, 5. > > > > > > > > This is the part where I was afraid of getting verbose. I will experiment > > > > The real scenario has hundreds of thousands of records and there are rows > > > > that match my sample. The pain. > > > > > > > > > > > > > > > > "Rich" wrote: > > > > > > > > > Thanks very much. The Union query was the route I was going to go, but I > > > > > thought that maybe there a more sophisticated way to go. I guess I need to > > > > > have a little more confidence in myself. > > > > > > > > > > Actually, the way you did it is fairly sophisticated. My idea of using a > > > > > Union query was way more verbose, and thus, I was trying to avoid it. But > > > > > you nailed it > > > > > > > > > > thanks very much for your help. > > > > > > > > > > Rich > > > > > > > > > > "Roy Harvey" wrote: > > > > > > > > > > > Here is what I came up with, including the last couple of lines of > > > > > > data load with the correction. The basic idea is to write it as two > > > > > > queries with a UNION. The first part is what you already have > > > > > > working. The second part tries to get the row that meets the > > > > > > secondary criteria. Note that the first query is pretty much > > > > > > duplicated in the second for the NOT IN test. > > > > > > > > > > > > While it appears to return the result you wanted from the data > > > > > > provided, I do not guarantee it will do as well when you have the real > > > > > > data. > > > > > > > > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > > > > > > select 6, '11/27/05', '2/1/06' > > > > > > > > > > > > > > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > > > > > > FROM #temp > > > > > > GROUP BY recid > > > > > > HAVING MAX(expDate)='1/25/06' > > > > > > UNION ALL > > > > > > SELECT * > > > > > > FROM #temp as X > > > > > > WHERE recid NOT IN --the list of recid from beginning > > > > > > (SELECT recid > > > > > > FROM #temp > > > > > > GROUP BY recid > > > > > > HAVING MAX(expDate)='1/25/06') > > > > > > AND expDate = '1/25/06' > > > > > > AND curDate >= > > > > > > (select curDate from #temp as Y > > > > > > where X.recid = Y.recid > > > > > > and Y.expDate = > > > > > > (select max(expDate) from #temp as Z > > > > > > where X.recid = Z.recid)) > > > > > > > > > > > > Roy Harvey > > > > > > Beacon Falls, CT > > > > > > it's just the matters of adding the right filter.
declare @dt datetime set @dt='1/25/06' select recid,max(curDate) curDate, expDate from #temp t1 where expDate>=@dt group by recid,expDate having max(curDate)=(select top 1 curDate from #temp t2 where t2.recid=t1.recid and t2.expDate>=@dt order by curDate desc) -- Show quote-oj "Rich" <R***@discussions.microsoft.com> wrote in message news:AB6EC5BD-CBDC-4936-B6A3-073DC5AC2035@microsoft.com... > Well, OJ's less verbose query isn't working on my actual data because I am > picking up rows with expDates that are not Max(expDate) for a given recID > where the curDate is not the Max(curDate) for the given recID and expDate. > I > need to pick up rows where the Max(expDate) is 1/25/06 or if a recID > contains a newer Max(expDate) than 1/25/06, I pick that row/recID up if > the > corresponding curDate for the row with expdate = 1/25/06 is greater than > the > curdate for the row containing the newer expDate for the given recID. So > it > looks like I will have to go with the more verbose query. I was hoping > it > would be a simple query, but no such luck. Oh well, at least one of the > solutions will work for my data. > > "Rich" wrote: > >> Thanks all for your replies. OJ's solution is less verbose than the one >> I >> was working with, but at least we all got the idea what I was trying to >> do - >> at least I am starting to get the idea. And Roy is correct on making the >> data more complex to cover more twists. Believe me, the actual data is >> quite a bit more complex, but at least now I am chiseling down the >> queries >> that I need. Start out with verbose till I understand what I am looking >> for >> and then drill down to the less verbose. >> >> "Rich" wrote: >> >> > so, taking this to the nth level (for posterity incase I need to refer >> > to >> > this again), I added yet one more row to recID 2. This additional row >> > is a >> > 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of >> > 1/25/06. >> > One of these rows contains a Max(curDate) for recID 2. >> > >> > >> > create table #temp( >> > recID int, curDate datetime, expDate datetime) >> > >> > insert into #temp >> > select 1, '11/1/05', '1/5/06' union >> > select 1, '11/5/05', '1/13/06' union >> > select 1, '11/12/05', '1/25/06' union >> > select 1, '11/28/05', '1/25/06' union >> > select 2, '11/7/05', '1/7/06' union >> > select 2, '11/13/05', '1/12/06' union >> > select 2, '11/27/05', '1/15/06' union >> > select 2, '1/7/06', '1/25/06' union >> > select 2, '1/8/06', '1/25/06' union >> > select 2, '12/1/05', '2/1/06' union >> > select 3, '11/3/05', '1/7/06' union >> > select 3, '11/8/05', '1/12/06' union >> > select 3, '11/17/05', '1/23/06' union >> > select 3, '12/1/05', '2/1/06' union >> > select 4, '11/5/05', '1/3/06' union >> > select 4, '11/9/05', '1/7/06' union >> > select 4, '11/19/05', '1/14/06' union >> > select 4, '11/23/05', '1/25/06' union >> > select 5, '11/5/05', '1/3/06' union >> > select 5, '11/9/05', '1/7/06' union >> > select 5, '11/19/05', '1/25/06' union >> > select 5, '11/27/05', '1/25/06' union >> > select 6, '11/5/05', '1/3/06' union >> > select 6, '11/9/05', '1/7/06' union >> > select 6, '11/19/05', '1/25/06' union >> > select 6, '1/5/06', '1/25/06' union >> > select 6, '11/27/05', '2/1/06' >> > >> > >> > The goal is to retrieve only the rows where Max(expDate) is 1/25/06, >> > and to >> > also retrieve rows that contain an expdate of 1/25/06 that is not the >> > Max(expDate) but these additional rows contain a curdate in the same >> > row as >> > the expDate of 1/25/06 which is the Max(curDate) for that recID. In >> > this >> > sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of >> > those >> > 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. >> > The >> > goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) >> > of >> > 1/25/06 and also rows for recID 2 and recID6 which contain a >> > Max(curDate) for >> > the rows that contain an expDate of 1/25/06. >> > >> > What I did was to modify Roy's query as follows: >> > >> > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate >> > FROM #temp >> > GROUP BY recid >> > HAVING MAX(expDate)='1/25/06' >> > UNION ALL >> > select recID, Max(curDate) m, expdate from --I added this line >> > (SELECT * FROM #temp as X >> > WHERE >> > recid NOT IN --the list of recid from beginning >> > (SELECT recid FROM #temp GROUP BY recid >> > HAVING MAX(expDate)='1/25/06') >> > AND >> > expDate = '1/25/06' >> > AND curDate > >> > (select curDate from #temp as Y >> > where X.recid = Y.recid >> > and Y.expDate = >> > (select max(expDate) from #temp as Z >> > where X.recid = Z.recid))) t1 >> > Group By recid, expdate >> > >> > --this gives >> > recID curDate >> > expDate >> > 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 >> > 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 >> > 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 >> > 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 >> > 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 >> > >> > >> > >> > "Rich" wrote: >> > >> > > I must be trying too hard. Your query works fine with the additional >> > > new row >> > > for recID 2. Sorry bout that. >> > > >> > > "Rich" wrote: >> > > >> > > > If I may, I added one more twist to this query. I added one more >> > > > row to >> > > > #temp where recID 2 now also has an expDate of 1/25/06 which is >> > > > less than its >> > > > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that >> > > > corresponds >> > > > to the expDate of 1/25/06 - same situation at recID 6 where the >> > > > curDate that >> > > > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 >> > > > Max(curDate) of 1/7/06. The 2nd part of the current Union query >> > > > only picks >> > > > up recID 6. >> > > > >> > > > So what I need to do is modify your query to see if I can pick up >> > > > recID 2 in >> > > > addition to recID 6 along with the original recID's of 1, 4, 5. >> > > > >> > > > This is the part where I was afraid of getting verbose. I will >> > > > experiment >> > > > The real scenario has hundreds of thousands of records and there >> > > > are rows >> > > > that match my sample. The pain. >> > > > >> > > > >> > > > >> > > > "Rich" wrote: >> > > > >> > > > > Thanks very much. The Union query was the route I was going to >> > > > > go, but I >> > > > > thought that maybe there a more sophisticated way to go. I guess >> > > > > I need to >> > > > > have a little more confidence in myself. >> > > > > >> > > > > Actually, the way you did it is fairly sophisticated. My idea of >> > > > > using a >> > > > > Union query was way more verbose, and thus, I was trying to avoid >> > > > > it. But >> > > > > you nailed it >> > > > > >> > > > > thanks very much for your help. >> > > > > >> > > > > Rich >> > > > > >> > > > > "Roy Harvey" wrote: >> > > > > >> > > > > > Here is what I came up with, including the last couple of lines >> > > > > > of >> > > > > > data load with the correction. The basic idea is to write it >> > > > > > as two >> > > > > > queries with a UNION. The first part is what you already have >> > > > > > working. The second part tries to get the row that meets the >> > > > > > secondary criteria. Note that the first query is pretty much >> > > > > > duplicated in the second for the NOT IN test. >> > > > > > >> > > > > > While it appears to return the result you wanted from the data >> > > > > > provided, I do not guarantee it will do as well when you have >> > > > > > the real >> > > > > > data. >> > > > > > >> > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate >> > > > > > select 6, '11/27/05', '2/1/06' >> > > > > > >> > > > > > >> > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate >> > > > > > FROM #temp >> > > > > > GROUP BY recid >> > > > > > HAVING MAX(expDate)='1/25/06' >> > > > > > UNION ALL >> > > > > > SELECT * >> > > > > > FROM #temp as X >> > > > > > WHERE recid NOT IN --the list of recid from beginning >> > > > > > (SELECT recid >> > > > > > FROM #temp >> > > > > > GROUP BY recid >> > > > > > HAVING MAX(expDate)='1/25/06') >> > > > > > AND expDate = '1/25/06' >> > > > > > AND curDate >= >> > > > > > (select curDate from #temp as Y >> > > > > > where X.recid = Y.recid >> > > > > > and Y.expDate = >> > > > > > (select max(expDate) from #temp as Z >> > > > > > where X.recid = Z.recid)) >> > > > > > >> > > > > > Roy Harvey >> > > > > > Beacon Falls, CT >> > > > > > Thanks. This query looks like it might do the trick. When I applied the
other query I ran into a problem with my actual data where the subquery was returning more than one value which I understand won't work with =, >, ... operators. I will give this a try and cross my fingers. Thanks for the help. Thank you all for your help. Show quote "oj" wrote: > it's just the matters of adding the right filter. > > declare @dt datetime > set @dt='1/25/06' > > select recid,max(curDate) curDate, expDate > from #temp t1 > where expDate>=@dt > group by recid,expDate > having max(curDate)=(select top 1 curDate > from #temp t2 > where t2.recid=t1.recid and t2.expDate>=@dt > order by curDate desc) > > > > -- > -oj > > > > "Rich" <R***@discussions.microsoft.com> wrote in message > news:AB6EC5BD-CBDC-4936-B6A3-073DC5AC2035@microsoft.com... > > Well, OJ's less verbose query isn't working on my actual data because I am > > picking up rows with expDates that are not Max(expDate) for a given recID > > where the curDate is not the Max(curDate) for the given recID and expDate. > > I > > need to pick up rows where the Max(expDate) is 1/25/06 or if a recID > > contains a newer Max(expDate) than 1/25/06, I pick that row/recID up if > > the > > corresponding curDate for the row with expdate = 1/25/06 is greater than > > the > > curdate for the row containing the newer expDate for the given recID. So > > it > > looks like I will have to go with the more verbose query. I was hoping > > it > > would be a simple query, but no such luck. Oh well, at least one of the > > solutions will work for my data. > > > > "Rich" wrote: > > > >> Thanks all for your replies. OJ's solution is less verbose than the one > >> I > >> was working with, but at least we all got the idea what I was trying to > >> do - > >> at least I am starting to get the idea. And Roy is correct on making the > >> data more complex to cover more twists. Believe me, the actual data is > >> quite a bit more complex, but at least now I am chiseling down the > >> queries > >> that I need. Start out with verbose till I understand what I am looking > >> for > >> and then drill down to the less verbose. > >> > >> "Rich" wrote: > >> > >> > so, taking this to the nth level (for posterity incase I need to refer > >> > to > >> > this again), I added yet one more row to recID 2. This additional row > >> > is a > >> > 2nd expDate of 1/25/06. So now recID 2 has 2 rows with expdate of > >> > 1/25/06. > >> > One of these rows contains a Max(curDate) for recID 2. > >> > > >> > > >> > create table #temp( > >> > recID int, curDate datetime, expDate datetime) > >> > > >> > insert into #temp > >> > select 1, '11/1/05', '1/5/06' union > >> > select 1, '11/5/05', '1/13/06' union > >> > select 1, '11/12/05', '1/25/06' union > >> > select 1, '11/28/05', '1/25/06' union > >> > select 2, '11/7/05', '1/7/06' union > >> > select 2, '11/13/05', '1/12/06' union > >> > select 2, '11/27/05', '1/15/06' union > >> > select 2, '1/7/06', '1/25/06' union > >> > select 2, '1/8/06', '1/25/06' union > >> > select 2, '12/1/05', '2/1/06' union > >> > select 3, '11/3/05', '1/7/06' union > >> > select 3, '11/8/05', '1/12/06' union > >> > select 3, '11/17/05', '1/23/06' union > >> > select 3, '12/1/05', '2/1/06' union > >> > select 4, '11/5/05', '1/3/06' union > >> > select 4, '11/9/05', '1/7/06' union > >> > select 4, '11/19/05', '1/14/06' union > >> > select 4, '11/23/05', '1/25/06' union > >> > select 5, '11/5/05', '1/3/06' union > >> > select 5, '11/9/05', '1/7/06' union > >> > select 5, '11/19/05', '1/25/06' union > >> > select 5, '11/27/05', '1/25/06' union > >> > select 6, '11/5/05', '1/3/06' union > >> > select 6, '11/9/05', '1/7/06' union > >> > select 6, '11/19/05', '1/25/06' union > >> > select 6, '1/5/06', '1/25/06' union > >> > select 6, '11/27/05', '2/1/06' > >> > > >> > > >> > The goal is to retrieve only the rows where Max(expDate) is 1/25/06, > >> > and to > >> > also retrieve rows that contain an expdate of 1/25/06 that is not the > >> > Max(expDate) but these additional rows contain a curdate in the same > >> > row as > >> > the expDate of 1/25/06 which is the Max(curDate) for that recID. In > >> > this > >> > sample, recID 2 contains 2 rows with expDate of 1/25/06 of which one of > >> > those > >> > 2 rows contains the Max(curDate) for recID 2, and dittor for recID 6. > >> > The > >> > goal is to retrieve rows for recID 1, 4, 5 which contain Max(expDate) > >> > of > >> > 1/25/06 and also rows for recID 2 and recID6 which contain a > >> > Max(curDate) for > >> > the rows that contain an expDate of 1/25/06. > >> > > >> > What I did was to modify Roy's query as follows: > >> > > >> > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > >> > FROM #temp > >> > GROUP BY recid > >> > HAVING MAX(expDate)='1/25/06' > >> > UNION ALL > >> > select recID, Max(curDate) m, expdate from --I added this line > >> > (SELECT * FROM #temp as X > >> > WHERE > >> > recid NOT IN --the list of recid from beginning > >> > (SELECT recid FROM #temp GROUP BY recid > >> > HAVING MAX(expDate)='1/25/06') > >> > AND > >> > expDate = '1/25/06' > >> > AND curDate > > >> > (select curDate from #temp as Y > >> > where X.recid = Y.recid > >> > and Y.expDate = > >> > (select max(expDate) from #temp as Z > >> > where X.recid = Z.recid))) t1 > >> > Group By recid, expdate > >> > > >> > --this gives > >> > recID curDate > >> > expDate > >> > 1 2005-11-28 00:00:00.000 2006-01-25 00:00:00.000 > >> > 4 2005-11-23 00:00:00.000 2006-01-25 00:00:00.000 > >> > 5 2005-11-27 00:00:00.000 2006-01-25 00:00:00.000 > >> > 2 2006-01-08 00:00:00.000 2006-01-25 00:00:00.000 > >> > 6 2006-01-05 00:00:00.000 2006-01-25 00:00:00.000 > >> > > >> > > >> > > >> > "Rich" wrote: > >> > > >> > > I must be trying too hard. Your query works fine with the additional > >> > > new row > >> > > for recID 2. Sorry bout that. > >> > > > >> > > "Rich" wrote: > >> > > > >> > > > If I may, I added one more twist to this query. I added one more > >> > > > row to > >> > > > #temp where recID 2 now also has an expDate of 1/25/06 which is > >> > > > less than its > >> > > > max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that > >> > > > corresponds > >> > > > to the expDate of 1/25/06 - same situation at recID 6 where the > >> > > > curDate that > >> > > > corresponds to recID 2 expDate of 2/1/06 is less than the recID 2 > >> > > > Max(curDate) of 1/7/06. The 2nd part of the current Union query > >> > > > only picks > >> > > > up recID 6. > >> > > > > >> > > > So what I need to do is modify your query to see if I can pick up > >> > > > recID 2 in > >> > > > addition to recID 6 along with the original recID's of 1, 4, 5. > >> > > > > >> > > > This is the part where I was afraid of getting verbose. I will > >> > > > experiment > >> > > > The real scenario has hundreds of thousands of records and there > >> > > > are rows > >> > > > that match my sample. The pain. > >> > > > > >> > > > > >> > > > > >> > > > "Rich" wrote: > >> > > > > >> > > > > Thanks very much. The Union query was the route I was going to > >> > > > > go, but I > >> > > > > thought that maybe there a more sophisticated way to go. I guess > >> > > > > I need to > >> > > > > have a little more confidence in myself. > >> > > > > > >> > > > > Actually, the way you did it is fairly sophisticated. My idea of > >> > > > > using a > >> > > > > Union query was way more verbose, and thus, I was trying to avoid > >> > > > > it. But > >> > > > > you nailed it > >> > > > > > >> > > > > thanks very much for your help. > >> > > > > > >> > > > > Rich > >> > > > > > >> > > > > "Roy Harvey" wrote: > >> > > > > > >> > > > > > Here is what I came up with, including the last couple of lines > >> > > > > > of > >> > > > > > data load with the correction. The basic idea is to write it > >> > > > > > as two > >> > > > > > queries with a UNION. The first part is what you already have > >> > > > > > working. The second part tries to get the row that meets the > >> > > > > > secondary criteria. Note that the first query is pretty much > >> > > > > > duplicated in the second for the NOT IN test. > >> > > > > > > >> > > > > > While it appears to return the result you wanted from the data > >> > > > > > provided, I do not guarantee it will do as well when you have > >> > > > > > the real > >> > > > > > data. > >> > > > > > > >> > > > > > select 6, '1/5/06', '1/25/06' union -- corrected curDate > >> > > > > > select 6, '11/27/05', '2/1/06' > >> > > > > > > >> > > > > > > >> > > > > > SELECT recid, Max(curDate) curDate, MAX(expDate) expDate > >> > > > > > FROM #temp > >> > > > > > GROUP BY recid > >> > > > > > HAVING MAX(expDate)='1/25/06' > >> > > > > > UNION ALL > >> > > > > > SELECT * > >> > > > > > FROM #temp as X > >> > > > > > WHERE recid NOT IN --the list of recid from beginning > >> > > > > > (SELECT recid > >> > > > > > FROM #temp > >> > > > > > GROUP BY recid > >> > > > > > HAVING MAX(expDate)='1/25/06') > >> > > > > > AND expDate = '1/25/06' > >> > > > > > AND curDate >= > >> > > > > > (select curDate from #temp as Y > >> > > > > > where X.recid = Y.recid > >> > > > > > and Y.expDate = > >> > > > > > (select max(expDate) from #temp as Z > >> > > > > > where X.recid = Z.recid)) > >> > > > > > > >> > > > > > Roy Harvey > >> > > > > > Beacon Falls, CT > >> > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics