Min not working

Hi all:
Because me not this functioning the following one query.

simply me not the function respects min, because?, that I am doing badly?

Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime

Thanks.:confused:the problem is because you have c.Link4 in the GROUP BY but not in the SELECT

also, please note: DISTINCT is not a function

try this --select Min(c.gentime) as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
group
by a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
order
by a.CardNumber
, c.recvtime|||thanks by answering, the problem is, that the inner join does not respect me the rank dated, I need a value by each employee, by each I gave of the rank.

Thanks.:confused:|||okay, try this --select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
and c.gentime =
( select min(c.gentime)
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10 )
order
by a.CardNumber
, c.recvtime|||thanks but the following error marks me:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

:eek:|||i'm going to move this query to the SQL Server forum

i don't understand the error message, maybe someone else does|||Not completely sure what is going on here, but trying moving the subquery to the JOIN clause:select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
inner
join --subquery
(select link3,
min(c.gentime) as gentime
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10
group by link3) subquery
on subquery.link3 = b.recordid
and subquery.gentime = c.gentime
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
order
by a.CardNumber
, c.recvtime

Migration to new servers

I have to migrate reports to two new servers. I tried simply moving the
databases over, but RS freaked. rsactivate and the other utilities are
problematic, to say the least (I freaked).
It seems like the easiest thing would be to install SQL RS on the new
servers, copy over all of the project files/reports, and then from within VS,
reset the project properties to the new server and redeploy.
Does this make sense? Is there an easier, or more reliable way?
--
Thanks,
CGWYou are correct. easiest is to install RS on new servers and then to the new
RS URL, you can just post your reports directly from Visual studio, or
however you deploy your reports today. This is the cleanest way to deploy as
well.
"CGW" wrote:
> I have to migrate reports to two new servers. I tried simply moving the
> databases over, but RS freaked. rsactivate and the other utilities are
> problematic, to say the least (I freaked).
> It seems like the easiest thing would be to install SQL RS on the new
> servers, copy over all of the project files/reports, and then from within VS,
> reset the project properties to the new server and redeploy.
> Does this make sense? Is there an easier, or more reliable way?
> --
> Thanks,
> CGW|||Thank you!
CGW
"Ram" wrote:
> You are correct. easiest is to install RS on new servers and then to the new
> RS URL, you can just post your reports directly from Visual studio, or
> however you deploy your reports today. This is the cleanest way to deploy as
> well.
> "CGW" wrote:
> > I have to migrate reports to two new servers. I tried simply moving the
> > databases over, but RS freaked. rsactivate and the other utilities are
> > problematic, to say the least (I freaked).
> >
> > It seems like the easiest thing would be to install SQL RS on the new
> > servers, copy over all of the project files/reports, and then from within VS,
> > reset the project properties to the new server and redeploy.
> >
> > Does this make sense? Is there an easier, or more reliable way?
> > --
> > Thanks,
> >
> > CGW