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

Finding a column in all tables within the DB

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

Quote:

Originally Posted by Rob0000

How do I list all tables that contain a particular column? For example, how do I list all tables that contain the column lastname within my database called manager? Thanx.

i ran into this issue the other day

SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%lastname %' )

if your doing this in SQL server just open up a sql command and execute this

Find versus Select error in SQL Express.

I am trying to look up records by the first letter of the last name in SQL Express ADO recordsets.

"Select * from people where people.lastname like 'a%'"

works correctly. (a*) works also.

ptRs.movefirst

PtRs.Find "lastname LIKE 'a%'", 0, adSearchForward

Does not work, does not find anything, but if I use 'a %' ('a<space>%') it finds the first last name starting with a.

Why does a% not work. Can you not use wild characters in Find Statements in SQL Express?

Hi,

find is not a sql server statement rather than a ADO statement: http://www.devguru.com/technologies/ado/quickref/recordset_find.html

For wildcards in ADO you have to use the asteriks.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the ADO versus SQL, I did not understand that.

IT returns the exact same with asterix in the find?

Am I doing it wrong or missing something?

find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credo
For such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
Anith
|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:

> hey Everyone.
> Could anyone give show me a query that would return just the records from a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo
|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:

> For such problems always post your table structures, sample data & expected
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>

find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
--
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credoFor such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
Anith|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:

> hey Everyone.
> Could anyone give show me a query that would return just the records from
a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:

> For such problems always post your table structures, sample data & expecte
d
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>

find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
--
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credoFor such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
--
Anith|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:
> hey Everyone.
> Could anyone give show me a query that would return just the records from a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:
> For such problems always post your table structures, sample data & expected
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>