finding max date

I am not sure how to phrase this query...if i could have MAX in my
where clause it would be:
SELECT jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays,
sum(jpm.NbrDaysPaidFor) AS DaysPaidFor, sum(jpm.Amount) AS TotalPaid
FROM table1 jj
inner join table2 jp on jj.id = jp.id
inner join table3 jpm on jp.payeeid = jpm.payeeid
inner join table4 jpc on jpm.paychecknbr = jpc.paychecknbr
inner join table5 jt on jpc.trid = jt.trid
where (jj.enddt BETWEEN @.StartDt AND @.EndDt)
AND (max(jt.verifieddt) BETWEEN @.StartDt AND @.EndDt)
I need to get the MAX verifieddt and then check to see if it is between
the startdt and enddt passed. Can someone help?
Thank you!*untested*
SELECT jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays,
sum(jpm.NbrDaysPaidFor) AS DaysPaidFor, sum(jpm.Amount) AS TotalPaid
FROM table1 jj
inner join table2 jp on jj.id = jp.id
inner join table3 jpm on jp.payeeid = jpm.payeeid
inner join table4 jpc on jpm.paychecknbr = jpc.paychecknbr
inner join table5 jt on jpc.trid = jt.trid
where (jj.enddt BETWEEN @.StartDt AND @.EndDt)
group by jj.id, jj.lastname, jj.firstname, jj.StartDt, jj.TotalDays
having (max(jt.verifieddt) BETWEEN @.StartDt AND @.EndDt)|||I did forget my group by statement b/c I left out the rest of my where
for ease of reading. I probably should have just put the whole thing
up here...when I tried 'having', I got an error that the column was
invalid in the having b/c it is not contained in either an aggregate
function or the group by clause.
so, here's the whole hairy mess:
SELECT t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays,
sum(t3.NbrDaysPaidFor) AS DaysPaidFor, sum(t3.Amount) AS TotalPaid
FROM table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.payeeid = t3.payeeid
inner join table4 t4 on t3.paychecknbr = t4.paychecknbr
inner join table5 t5 on t4.trid = t5.trid
where t1.totaldays > 1
AND t1.statuscode = 'PF'
AND t3.StatusCode = 'VE'
GROUP BY t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays
having (((((t1.enddt BETWEEN @.StartDt AND @.EndDt) AND
(max(t5.verifieddt) BETWEEN @.StartDt AND @.EndDt))
OR (t1.enddt < @.StartDt AND max(t5.verifieddt) BETWEEN @.StartDt AND
@.EndDt))
AND t4.statuscode IN (SELECT code FROM table6 WHERE codetype =
'status'
AND (code = 'GO' OR code = 'GP')))
OR (max(t5.verifieddt) < @.StartDt AND (t4.VoidDt BETWEEN @.StartDt AND
@.EndDt)))|||When I run your query I get the 'column invalid in HAVING clause' error on
t1.enddt, t4.statuscode and t4.voiddt. Since you are already grouping on
t1.id, I would say that it is safe to also group on t1.enddt. Throw that
into your GROUP BY clause and now you just have t4 to worry about.
I'm not sure about this bit:
max(t5.verifieddt) < @.StartDt AND t4.VoidDt BETWEEN @.StartDt AND @.EndDt
Which t4.VoidDt value are you talking about here? Are you expecting several
t4 rows for each t1 row? (it looks like it).
In English, is this line trying to say "where 'the maximum value of
t5.verifieddt on those rows whose corresponding t4.voiddt is between
@.startDt and @.endDt' is less than @.startDt"?
"Confused" <cschanz@.gmail.com> wrote in message
news:1138386931.324271.157020@.f14g2000cwb.googlegroups.com...
>I did forget my group by statement b/c I left out the rest of my where
> for ease of reading. I probably should have just put the whole thing
> up here...when I tried 'having', I got an error that the column was
> invalid in the having b/c it is not contained in either an aggregate
> function or the group by clause.
> so, here's the whole hairy mess:
> SELECT t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays,
> sum(t3.NbrDaysPaidFor) AS DaysPaidFor, sum(t3.Amount) AS TotalPaid
> FROM table1 t1
> inner join table2 t2 on t1.id = t2.id
> inner join table3 t3 on t2.payeeid = t3.payeeid
> inner join table4 t4 on t3.paychecknbr = t4.paychecknbr
> inner join table5 t5 on t4.trid = t5.trid
> where t1.totaldays > 1
> AND t1.statuscode = 'PF'
> AND t3.StatusCode = 'VE'
> GROUP BY t1.id, t1.lastname, t1.firstname, t1.startdt, t1.totaldays
> having (((((t1.enddt BETWEEN @.StartDt AND @.EndDt) AND
> (max(t5.verifieddt) BETWEEN @.StartDt AND @.EndDt))
> OR (t1.enddt < @.StartDt AND max(t5.verifieddt) BETWEEN @.StartDt AND
> @.EndDt))
> AND t4.statuscode IN (SELECT code FROM table6 WHERE codetype =
> 'status'
> AND (code = 'GO' OR code = 'GP')))
> OR (max(t5.verifieddt) < @.StartDt AND (t4.VoidDt BETWEEN @.StartDt AND
> @.EndDt)))
>|||> In English, is this line trying to say "where 'the maximum value of
> t5.verifieddt on those rows whose corresponding t4.voiddt is between
> @.startDt and @.endDt' is less than @.startDt"?
Eek, word problems, which I always detested.
"Confused", please see http://www.aspfaq.com/5006 ... this way, you can
provide us proper specs, we can give a tested and working solution, and end
this madness.
A