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 i
s
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?
"Roy Harvey" wrote:
> Rich,
> This is just a wee bit hard for me to sort out. One part in
> particular has me especially
.
> The total set of data for recID = 6 is:
>
> Your desired result includes:
> 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
"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 it
s
max(expDate) of 2/1/06 - and recID 2 also has a Max(curDate) that correspond
s
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 t
o
> 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:
>|||I must be trying too hard. Your query works fine with the additional new ro
w
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 correspo
nds
> to the expDate of 1/25/06 - same situation at recID 6 where the curDate th
at
> 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 pic
ks
> 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:
>|||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 thos
e
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) fo
r
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:
>|||This gives the same result:
select recid,max(curDate) curDate, expDate
from #temp
where expDate='1/25/06'
group by recid,expDate
-oj
"Rich" <Rich@.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:
>|||On Thu, 2 Mar 2006 22:56:26 -0800, Rich
<Rich@.discussions.microsoft.com> wrote:
>so, taking this to the nth level (for posterity incase I need to refer to
>this again)....
Sounds like you have things well in hand. (I really don't have time
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.
"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 t
o
> 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 a
s
> 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 th
ose
> 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:
>|||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 th
e
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 f
or
> and then drill down to the less verbose.
> "Rich" wrote:
>
