Insert Statement w/ max
-
I'm attempting to do the following procedure:
I have the following table that contains facility ids & dates
FacId Date
501 1/2006
501 6/2006
149 3/2005
011 11/2004
I want to take those records and retrieve the min & max dates for each facility without returning more than one record for facility
I figured i would create a table as follows:
create table #tmp
(
FacId int,
MinPunchDT varchar(12),
MaxPunchDT varchar(12)
)
and then insert my results so my output would look like this:
FacId, MinPunchDT MaxPunchDT
501 1/2006 6/2006
149 3/2005 null
011 11/204 null
However when i try to do the insert I get back incorrect results:
insert into #tmp
select
A.FacId,
max(A.PunchDT) as MaxPunchDT
from FacIds A
group by
A.FacId,
A.PunchDT
FacId MaxPunchDT
12 1/2005
501 10/2005
145 3/2005
501 6/2005
I want the result to be as follows:
FacId MaxPunchDT
12 1/2005
501 10/2005
145 3/2005
Thanks for your input
Galahad
-
select
A.FacId,
max(A.PunchDT) as MaxPunchDT
from FacIds A
group by A.FacId
_____________
Code for TallyGenerator -
Thanks alot, I could have sworn that I ran that statement and didn't get back the max!
So now i guess that I can't get both values inserted at the same time (Min & Max Dates) so I would do this:
insert into #tmp
select
A.FacId,
'',
max(A.PunchDT) as MaxPunchDT
from FacIds A
group by A.FacId
And then to get the min values in I tried this but got error:
update #tmp
set MinPunchDT=
min(B.PunchDT) as MinPunchDT
from #tmp A
inner join FacIds B
A.FacId=B.FacId
group by A.FacId
Advice please...thx again
-
You can get the min & max in the one query without worrying about spooling to a temp table...
select
A.FacId,
max(A.PunchDT) as MaxPunchDT
min(A.PunchDT) as MinPunchDT
from FacIds A
group by A.FacId
-
Makes sense and works good. I don't know why when i first try to get the min & max i didn't seem to work. May have been an issue w/ teh while loop in my stored proc.
Anyways, thanks for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply