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

Find uniques on part of a multi-column PK (with where clause)

Hello all,
Follows a simplified DDL of the table where i'm working on:
CREATE TABLE Test
(
TestID bigint NOT NULL ,
SubTestID bigint NOT NULL ,
Name varchar (50),
Master bigint NOT NULL ,
CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
(
TestID,
SubTestID
)
)
What I'm trying to achieve is to collect and retrieve all rows of which
Master differs from the value 2 and TestID is unique.
So far, the best I achieved was:
SELECT
counter.TestID
FROM
(
SELECT
TestID,
COUNT(TestID) AS Total
FROM
Test
WHERE Master <> 2
GROUP BY
TestID
) as counter
WHERE counter.Total = 1
However this strategy doesn't suit me since I need the TestID/SubTestID
pair to be returned. Needless to say, introducing SubTestID in the
above query will invalidate de aggregate function.
Can someone please help?Mario
Needless to say, that posting sample data + expected result will help to
bring a right solution. No offence.
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Lazy solution:
SELECT TestID, MAX(SubTestID ) AS SubTestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1
More structured solution:
SELECT t1.TestID, t1.SubTestID
FROM Test t1
INNER JOIN (
SELECT TestID
FROM Test
WHERE Master <> 2
GROUP BY TestID
HAVING COUNT (*) =1) a
ON t1.TestID = a.TestID
Jacco Schalkwijk
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Try,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
COUNT(distinct TestID) = 1
) as a
on t.TestID = a.TestID
go
AMB
"Mario F." wrote:

> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||One method:
SELECT
a.TestID,
a.SubTestID
FROM Test a
JOIN
(
SELECT
TestID
FROM Test
WHERE Master <> 2
GROUP BY
TestID
HAVING COUNT(*) = 1
) AS counter ON counter.TestID = a.TestID
Hope this helps.
Dan Guzman
SQL Server MVP
"Mario F." <marfig@.gmail.com> wrote in message
news:1113307096.029253.91660@.o13g2000cwo.googlegroups.com...
> Hello all,
> Follows a simplified DDL of the table where i'm working on:
> CREATE TABLE Test
> (
> TestID bigint NOT NULL ,
> SubTestID bigint NOT NULL ,
> Name varchar (50),
> Master bigint NOT NULL ,
> CONSTRAINT PK_Test PRIMARY KEY CLUSTERED
> (
> TestID,
> SubTestID
> )
> )
> What I'm trying to achieve is to collect and retrieve all rows of which
> Master differs from the value 2 and TestID is unique.
> So far, the best I achieved was:
> SELECT
> counter.TestID
> FROM
> (
> SELECT
> TestID,
> COUNT(TestID) AS Total
> FROM
> Test
> WHERE Master <> 2
> GROUP BY
> TestID
> ) as counter
> WHERE counter.Total = 1
> However this strategy doesn't suit me since I need the TestID/SubTestID
> pair to be returned. Needless to say, introducing SubTestID in the
> above query will invalidate de aggregate function.
> Can someone please help?
>|||Correction,
select
t.*
from
test as t
inner join
(
SELECT
TestID
FROM
Test
WHERE
Master <> 2
GROUP BY
TestID
having
min(SubTestID) = max(SubTestID)
) as a
on t.TestID = a.TestID
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> t.*
> from
> test as t
> inner join
> (
> SELECT
> TestID
> FROM
> Test
> WHERE
> Master <> 2
> GROUP BY
> TestID
> having
> COUNT(distinct TestID) = 1
> ) as a
> on t.TestID = a.TestID
> go
>
> AMB
> "Mario F." wrote:
>|||The Having clause. Of course. Thank you everyone.
Mario F.
*** Sent via Developersdex http://www.examnotes.net ***sql