How to get Top 1 in Join on Calendar table...?

I would only like to see the latest calendar Sale per #c1 record as (select
top
1 * from calen order by ondate desc)
It doesn't matter if it's type is RA or DS, just the latest ONE.
set nocount on
-- Hist
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '03/06/2006' ,Ac
tv =
'DS' ,Num = 500 ,Type = 'A' ,recid = '123457'
into #h
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Needs' ,ondate = '01/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'A' ,recid = '123456'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '02/12/2006' ,Actv
=
'RA' ,Num = 2000 ,Type = 'S' ,recid = '123458'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '09/23/2005' ,Actv
=
'RA' ,Num = 1500 ,Type = 'S' ,recid = '223458'
insert #h
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '12/12/2005' ,Ac
tv =
'DS' ,Num = 7500 ,Type = 'A' ,recid = '123459'
insert #h
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '11/10/2005' ,Ac
tv =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223456'
-- select * from #h
-- Cal
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '05/06/2006' ,Ac
tv =
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123457'
into #ca
insert #ca
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Sale' ,ondate = '04/26/2006' ,Actv
=
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123456'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '09/26/2006' ,Actv
=
'RS' ,Num = 2500 ,Type = 'S' ,recid = '123458'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '07/02/2006' ,Actv
=
'RA' ,Num = 1200 ,Type = 'S' ,recid = '223459'
insert #ca
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123459'
insert #ca
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'RA' ,Num = 2500 ,Type = 'S' ,recid = '223456'
insert #ca
select id = 5 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '03/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
-- select * from #ca
-- c1
select id = 2 ,Co = 'Vesto Mays' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum'
,recid = '654322'
into #c1
insert #c1
select id = 1 ,Co = 'Rogers' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '654
321'
insert #c1
select id = 3 ,Co = 'Fishy' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,recid
=
'654323'
insert #c1
select id = 4 ,Co = 'Carmers' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,rec
id =
'654324'
insert #c1
select id = 5 ,Co = 'Keys' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '65432
5'
insert #c1
select id = 6 ,Co = 'Smartie' ,MC = 'Baton' ,Key4 = 'Kelsy Cupps' ,recid =
'654326'
-- select * from #c1
-- c2
select id = 1 ,uawrv = 3000 ,udsawrv = 20000 ,unewcxd = '07/01/2006' ,udscxd
=
null ,recid = '9876543'
into #c2
insert #c2
select id = 2 ,uawrv = 10000 ,udsawrv = 7000 ,unewcxd = '04/01/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 3 ,uawrv = 15000 ,udsawrv = 5000 ,unewcxd = '12/01/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 4 ,uawrv = 23000 ,udsawrv = 500 ,unewcxd = '10/12/2006' ,udscxd
=
null ,recid = '9876543'
insert #c2
select id = 5 ,uawrv = 4000 ,udsawrv = 120 ,unewcxd = '02/03/2007' ,udscxd =
null ,recid = '9876543'
insert #c2
select id = 6 ,uawrv = 2500 ,udsawrv = 12000 ,unewcxd = '12/09/2009' ,udscxd
=
null ,recid = '9876543'
-- select * from #c2
-- staff
select id = 2 ,StaffName = 'Lester Mothartes' ,userid = 'MOTHARL1' ,MC = 'Lo
s
Angeles' ,terrid = 2001 ,awrv = 300 ,dsawrv = 2000 ,recid = '654322'
into #s
insert #s
select id = 1 ,StaffName = 'Steve Arme' ,userid = 'ARMES1' ,MC = 'Baton'
,terrid = 2004 ,awrv = 300 ,dsawrv = 2000 ,recid = '654321'
insert #s
select id = 3 ,StaffName = 'Lora Candum' ,userid = 'CANDUML1' ,MC = 'Los
Angeles' ,terrid = 2009 ,awrv = 300 ,dsawrv = 2000 ,recid = '654323'
insert #s
select id = 5 ,StaffName = 'Chaz Arme' ,userid = 'ARMEC1' ,MC = 'Baton' ,te
rrid
= 2005 ,awrv = 300 ,dsawrv = 2000 ,recid = '654325'
insert #s
select id = 6 ,StaffName = 'Kelsy Cupps' ,userid = 'CUPPSK1' ,MC = 'Baton'
,terrid = 2003 ,awrv = 300 ,dsawrv = 2000 ,recid = '654326'
-- select * from #s
declare @.endDate int
set @.endDate = 365
-- ========================================
=====================
select distinct id = c1.id
,Company = c1.co
,MarketCenter = c1.MC
,AE = c1.key4
,AWRV = c2.uawrv
,DSAWRV = c2.udsawrv
,CXD = c2.unewcxd
,DSCXD = c2.udscxd
,caRef
,caRecID
,caOnDate
,haRecType
,haRecID
,haRef
,haLastDate
,hsRecID
,hsRef
,hsLastDate
from #c1 c1 with(nolock)
join #c2 c2 with(nolock) on c1.id = c2.id
-- staff
left join (select staffname ,awrv ,dsawrv ,terrid ,userid from #s with(noloc
k))
s on c1.key4 = s.staffname
-- cal
-- ********** how do I only get the latest **************
join (select id ,caRef = ref ,caRecID = recid ,caOnDate = ondate ,userid fro
m
#ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR','DS') and ondate between getdate()
and getdate()+@.enddate)
) ca on c1.id = ca.id and ca.userid = s.userid
-- ********************** <> **********************
-- hist Appt
left join (select id ,haRef = ref ,haRecID = recid ,haLastDate = ondate
,haRecType = Type from #h with(nolock)
where (left(type,1)+left(ref,5) = 'ANeeds' )) ha on c1.id = ha.id
-- hist Sale
left join (select id ,hsRef = ref ,hsRecID = recid ,hsLastDate = ondate
,hsRecType = Type from #h with(nolock)
where type ='S'
and ondate in(select top 1 ondate from #h with(nolock) where type = 'S' orde
r
by ondate asc)
) hs on c1.id = hs.id
group by c1.id ,c1.co ,c1.MC ,c1.key4 ,c2.uawrv ,c2.udsawrv ,c2.unewcxd
,c2.udscxd
,caRecID ,caOnDate ,haRecType ,haRecID ,haRef
,haLastDate ,hsRecID ,hsRef ,hsLastDate ,caRef
order by id
-- ========================================
=====================
-- clean up
drop table #h
drop table #ca
drop table #c1
drop table #c2
drop table #s
TIA
JeffP....JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> I would only like to see the latest calendar Sale per #c1 record as
> (select top 1 * from calen order by ondate desc)
> It doesn't matter if it's type is RA or DS, just the latest ONE.
I was not able to get your repro to run, since you use case inconsistently,
and I am running a case-sensitive server.
It was also difficult to under stand the keys, since you did not post
CREATE TABLE statements, only SELECT INTO.
But as I understood it:

> -- ********** how do I only get the latest **************
> join (select id ,caRef = ref ,caRecID = recid ,caOnDate = ondate ,userid
> from
> #ca with(nolock)
> where (left(type,1)+left(Actv,1) in('SR','DS') and ondate between
> getdate()
> and getdate()+@.enddate)
> ) ca on c1.id = ca.id and ca.userid = s.userid
> -- ********************** <> **********************
This was the problematic part. Try replacing the derived table with
this query:
select a.id, caRef = a.ref ,caRecID = a.recid ,caOnDate = a.ondate,
a.userid
from #ca a
JOIN (SELECT id, userid, ondate = MAX(ondate)
FROM #ca
where (left(type,1)+left(Actv,1) in('SR','DS')
and ondate between convert(char(8, getdate(), 112) and
dateadd (DAY, @.enddate, convert(char(8), getdate())
I also rewrite the condition ondate, as it seemed like you like
to incldue today. and assuming that ondate is date-only, comparing
to getdate() which has both date and time is not good.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, sorry to say that this doesn't help... also sorry to hear that you w
ork
on a case sensetive environment
Your supplemental query failed due to a group by clause as well as the "on"
portion of the join.
When I cleaned these items up, I still got a row for each condition just lik
e in
my original but with an additional join.
I'm not sure how to phrase my question, I only want the one row as in "top 1
"
which I had used with an order by clause desc which essentially was the same
as
using max(ondate) without needing a group by.
However when using top 1 in a sub query it appears that the top 1 was is nev
er
found, I'm sure there is a good reason for this, so it doesn't matter.
Still searching...
JeffP...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns979B6E52A678Yazorman@.127.0.0.1...
> JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> I was not able to get your repro to run, since you use case inconsistently
,
> and I am running a case-sensitive server.
> It was also difficult to under stand the keys, since you did not post
> CREATE TABLE statements, only SELECT INTO.
> But as I understood it:
>
> This was the problematic part. Try replacing the derived table with
> this query:
> select a.id, caRef = a.ref ,caRecID = a.recid ,caOnDate = a.ondate,
> a.userid
> from #ca a
> JOIN (SELECT id, userid, ondate = MAX(ondate)
> FROM #ca
> where (left(type,1)+left(Actv,1) in('SR','DS')
> and ondate between convert(char(8, getdate(), 112) and
> dateadd (DAY, @.enddate, convert(char(8), getdate()
)
>
> I also rewrite the condition ondate, as it seemed like you like
> to incldue today. and assuming that ondate is date-only, comparing
> to getdate() which has both date and time is not good.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
> Erland, sorry to say that this doesn't help... also sorry to hear that
> you work on a case sensetive environment
> Your supplemental query failed due to a group by clause as well as the
> "on" portion of the join.
> When I cleaned these items up, I still got a row for each condition just
> like in my original but with an additional join.
> I'm not sure how to phrase my question, I only want the one row as in
> "top 1" which I had used with an order by clause desc which essentially
> was the same as using max(ondate) without needing a group by.
> However when using top 1 in a sub query it appears that the top 1 was is
> never found, I'm sure there is a good reason for this, so it doesn't
> matter.
It probably helps if you rewrite your repro to use CREATE TABLE with
definition of keys included. It also helps if you include the expected
output. And of course, it's a good idea to clean up inconsistent use of
case. It is a good recommendation to do all development with a case-
sensitive collation, in case this would be a requirement for production.
I took a guess on what the keys would be, but as I could not get the
script to work, I could not test it. And since there was no expected
result, I would not have been able to verify it anyway. I was hoping,
though, that you could use my suggestion as a starting point.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, thank you for your suggestions...
What's happening is that in my test, I am getting expected results but in li
ve I
do not as there are more aspects.
The Fkeys are the ID.
What I want is one row for each record, with the greatest appropriate recid
for
the calendar, history records.
here is my latest, I don't expect in my world to every be in a case sensetiv
e
environment, I just won't do it.
In the following example yields...notice that for record id = 1 whe have two
nearly identical sales, however one has a greater cal ondate, which is now
returned by the final qry.
However in production with real tables, additional fields I still am getting
a
few rows, if you wish to see my real qry send me a pm.
JeffP...
id userid Ref ondate Actv Num Type recid
-- -- -- -- -- -- -- --
1 ARMEC1 SaleR 09/26/2006 RA 2500 S 123458
1 ARMEC1 SaleR 07/02/2006 RA 1200 S 223459
caRecID caRef caOnDate userid id
-- -- -- -- --
123458 SaleR 09/26/2006 ARMEC1 1
id Company MarketCenter AE AWRV DSAWRV CXD
DSCXD caActv caRef caRecID caOnDate hnfRecID hnRef hnLastDate hpRecID
hpRef hpLastDate
-- -- -- -- -- -- --
--
-- -- -- -- -- -- -- -- -- --
--
-- --
1 Rogers Baton Chaz Arme 3000 20000
07/01/2006 01/01/1900 RA SaleR 123458 09/26/2006 123456 Needs 01/26/2
006
NULL NULL NULL
2 Vesto Mays Los Angeles Lora Candum 10000 7000
04/01/2006 01/01/1900 DS SaleD 123457 05/06/2006 123457 Needs 03/06/2
006
NULL NULL NULL
4 Carmers Los Angeles Lora Candum 23000 500
10/12/2006 01/01/1900 RA RA 223456 06/12/2006 223456 Needs 11/10/2
005
NULL NULL NULL
6 Smartie Baton Kelsy Cupps 2500 12000
01/01/1900 01/01/2007 DS Sale 123456 04/26/2006 223457 Needs 11/01/2
005
223459 Propo 11/10/2005
-- begin
set nocount on
-- Hist
select id = 2 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '03/06/2006' ,Ac
tv =
'DS' ,Num = 500 ,Type = 'A' ,recid = '123457'
into #h
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Needs' ,ondate = '01/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'A' ,recid = '123456'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '02/12/2006' ,Actv
=
'RA' ,Num = 2000 ,Type = 'S' ,recid = '123458'
insert #h
select id = 1 ,userid = 'ARMEC1' ,Ref = 'First' ,ondate = '09/23/2005' ,Actv
=
'RA' ,Num = 1500 ,Type = 'A' ,recid = '223458'
insert #h
select id = 3 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '12/12/2005' ,Ac
tv =
'DS' ,Num = 7500 ,Type = 'A' ,recid = '123459'
insert #h
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Needs' ,ondate = '11/10/2005' ,Ac
tv =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223456'
insert #h
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Needs' ,ondate = '11/01/2005' ,Act
v =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223457'
insert #h
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Propo' ,ondate = '11/10/2005' ,Act
v =
'RA' ,Num = 1300 ,Type = 'A' ,recid = '223459'
-- select * from #h
-- Cal
select id = 2 ,userid = 'CANDUML1' ,Ref = 'SaleD' ,ondate = '05/06/2006' ,A
ctv
= 'DS' ,Num = 2500 ,Type = 'S' ,recid = '123457'
into #ca
insert #ca
select id = 6 ,userid = 'CUPPSK1' ,Ref = 'Sale' ,ondate = '04/26/2006' ,Actv
=
'DS' ,Num = 2500 ,Type = 'S' ,recid = '123456'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'SaleR' ,ondate = '09/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
insert #ca
select id = 1 ,userid = 'ARMEC1' ,Ref = 'SaleR' ,ondate = '07/02/2006' ,Actv
=
'RA' ,Num = 1200 ,Type = 'S' ,recid = '223459'
-- insert #ca
-- select id = 3 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,
Actv
= 'DS' ,Num = 2500 ,Type = 'S' ,recid = '123459'
insert #ca
select id = 4 ,userid = 'CANDUML1' ,Ref = 'Sale' ,ondate = '06/12/2006' ,Act
v =
'RA' ,Num = 2500 ,Type = 'S' ,recid = '223456'
insert #ca
select id = 5 ,userid = 'ARMEC1' ,Ref = 'Sale' ,ondate = '03/26/2006' ,Actv
=
'RA' ,Num = 2500 ,Type = 'S' ,recid = '123458'
-- select * from #ca
-- c1
select id = 2 ,Co = 'Vesto Mays' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum'
,recid = '654322'
into #c1
insert #c1
select id = 1 ,Co = 'Rogers' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '654
321'
insert #c1
select id = 3 ,Co = 'Fishy' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,recid
=
'654323'
insert #c1
select id = 4 ,Co = 'Carmers' ,MC = 'Los Angeles' ,Key4 = 'Lora Candum' ,rec
id =
'654324'
insert #c1
select id = 5 ,Co = 'Keys' ,MC = 'Baton' ,Key4 = 'Chaz Arme' ,recid = '65432
5'
insert #c1
select id = 6 ,Co = 'Smartie' ,MC = 'Baton' ,Key4 = 'Kelsy Cupps' ,recid =
'654326'
-- select * from #c1
-- c2
select id = 1 ,uawrv = 3000 ,udsawrv = 20000 ,unewcxd = '07/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
into #c2
insert #c2
select id = 2 ,uawrv = 10000 ,udsawrv = 7000 ,unewcxd = '04/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 3 ,uawrv = 15000 ,udsawrv = 5000 ,unewcxd = '12/01/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 4 ,uawrv = 23000 ,udsawrv = 500 ,unewcxd = '10/12/2006' ,udscxd
=
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 5 ,uawrv = 4000 ,udsawrv = 120 ,unewcxd = '02/03/2007' ,udscxd =
'01/01/1900' ,recid = '9876543'
insert #c2
select id = 6 ,uawrv = 2500 ,udsawrv = 12000 ,unewcxd = '01/01/1900' ,udscx
d =
'01/01/2007' ,recid = '9876543'
-- select * from #c2
-- staff
select id = 2 ,StaffName = 'Lester Mothartes' ,userid = 'MOTHARL1' ,MC = 'Lo
s
Angeles' ,terrid = 2001 ,awrv = 300 ,dsawrv = 2000 ,recid = '654322'
into #s
insert #s
select id = 1 ,StaffName = 'Steve Arme' ,userid = 'ARMES1' ,MC = 'Baton'
,terrid = 2004 ,awrv = 300 ,dsawrv = 2000 ,recid = '654321'
insert #s
select id = 3 ,StaffName = 'Lora Candum' ,userid = 'CANDUML1' ,MC = 'Los
Angeles' ,terrid = 2009 ,awrv = 300 ,dsawrv = 2000 ,recid = '654323'
insert #s
select id = 5 ,StaffName = 'Chaz Arme' ,userid = 'ARMEC1' ,MC = 'Baton' ,te
rrid
= 2005 ,awrv = 300 ,dsawrv = 2000 ,recid = '654325'
insert #s
select id = 6 ,StaffName = 'Kelsy Cupps' ,userid = 'CUPPSK1' ,MC = 'Baton'
,terrid = 2003 ,awrv = 300 ,dsawrv = 2000 ,recid = '654326'
-- select * from #s
declare @.endDate int
set @.endDate = 365
-- ========================================
=====================
select distinct id = c1.id
,Company = c1.co
,MarketCenter = c1.MC
,AE = c1.key4
,AWRV = c2.uawrv
,DSAWRV = c2.udsawrv
,CXD = c2.unewcxd
,DSCXD = c2.udscxd
,caActv = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)d">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.Actv
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.Actv else car.Ac
tv
end)
,caRef = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime
) >=
cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caRef
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caRef else car.
Actv
end)
,caRecID = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)ed">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caRecID
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caRecID else
car.caRecID end)
,caOnDate = (select case when cast(isnull(cab.caOndate,'01/01/1900')as datetime)red">
>= cast(isnull(cad.caOndate,'01/01/1900')as datetime)
and cast(isnull(cab.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cab.caOndate
when cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(cab.caOndate,'01/01/1900')as datetime)
and cast(isnull(cad.caOndate,'01/01/1900')as datetime) >=
cast(isnull(car.caOndate,'01/01/1900')as datetime) then cad.caOndate else
car.caOndate end)
,hnfRecID = (select case when cast(isnull(han.LastDate,'01/01/1900')as
datetime) > cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.Recid else haf.Recid end)
,hnRef = (select case when cast(isnull(han.LastDate,'01/01/1900')as datetime
) >
cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.Ref else haf.Ref end)
,hnLastDate = (select case when cast(isnull(han.LastDate,'01/01/1900')as
datetime) > cast(isnull(haf.LastDate,'01/01/1900')as datetime)
then han.LastDate else haf.LastDate end)
,hpRecID = (select case when cast(isnull(happ.LastDate,'01/01/1900')as datetime)red">
> cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.recid else haps.recid end)
,hpRef = (select case when cast(isnull(happ.LastDate,'01/01/1900')as datetime)ed">
> cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.ref else haps.ref end)
,hpLastDate = (select case when cast(isnull(happ.LastDate,'01/01/1900')as
datetime) > cast(isnull(haps.LastDate,'01/01/1900')as datetime)
then happ.lastdate else haps.lastdate end)
into #main
from #c1 c1 with(nolock)
join #c2 c2 with(nolock) on c1.id = c2.id
-- staff
left join(select staffname ,awrv ,dsawrv ,terrid ,userid from #s with(nolock
))
s on c1.key4 = s.staffname
-- cal
-- how do I only get the latest
left join (select caRecID = recid ,caRef = ref ,caOnDate = ondate ,userid ,i
d
,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SD') and ondate between getdate() and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv ) cad on c1.id = cad.id and
cad.userid = s.userid
left join (select caRecID = recid ,caRef = ref ,caOnDate = max(ondate) ,user
id
,id ,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR') and ondate between getdate() and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv) car on c1.id = car.id and
car.userid = s.userid
left join (select top 1 caRecID = recid ,caRef = ref ,caOnDate = max(ondate)
,userid ,id ,Actv from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SD','SR') and ondate between getdate()
and getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid ,Actv ) cab on c1.id = cab.id and
cab.userid = s.userid
-- hist Appt
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'AFirst' )) haf on c1.id = haf.id and
haf.userid = s.userid
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'ANeeds' )) han on c1.id = han.id and
han.userid = s.userid
-- hist proposal
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'APropo' )) happ on c1.id = happ.id and
happ.userid = s.userid
left join (select id ,Ref = ref ,RecID ,LastDate = ondate ,Type ,userid from
#h with(nolock)
where (left(type,1)+left(ref,5) = 'APrese' )) haps on c1.id = haps.id and
haps.userid = s.userid
order by c1.id
-- ========================================
=====================
select * from #ca where id = 1
-- select * from #h where id = 6
select top 1 caRecID = recid ,caRef = ref ,caOnDate = max(ondate) ,userid ,i
d
from #ca with(nolock)
where (left(type,1)+left(Actv,1) in('SR','SD') and ondate between getdate()
and
getdate()+@.enddate)
group by id ,ref ,recid ,ondate ,userid
delete #main where carecid is null
select * from #main
-- clean up
drop table #h
drop table #ca
drop table #c1
drop table #c2
drop table #s
drop table #main
-- end
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns979B5FA7E9005Yazorman@.127.0.0.1...
> JDP@.Work (JPGMTNoSpam@.sbcglobal.net) writes:
>
> It probably helps if you rewrite your repro to use CREATE TABLE with
> definition of keys included. It also helps if you include the expected
> output. And of course, it's a good idea to clean up inconsistent use of
> case. It is a good recommendation to do all development with a case-
> sensitive collation, in case this would be a requirement for production.
> I took a guess on what the keys would be, but as I could not get the
> script to work, I could not test it. And since there was no expected
> result, I would not have been able to verify it anyway. I was hoping,
> though, that you could use my suggestion as a starting point.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

How to get the latest & earliest date?

Hi,

How to get the latest date from ColumnDateTime?

and the earlier as well ?

select max(columnDateTime), -- for lastest date
min(columnDateTime) -- for earliest date|||Thanks a lot :)|||what if I want to use it with "WHERE" Condition ?|||

Maybe something like

select max(columnDateTime)
from yourSourceTable
where anIntegerColumn = 14

If your column is a varchar column that WHERE condition will look like:

where aVarcharColumn = 'theTargetString'

how to get latest record from a table?

hi guys.
i have a table that store all transactions. In this table, a member can has multiple records, there is a field to store the date.
1. the problem i'm facing is, i need to retrieve only the latest record. for example, the transaction table contains of 20 records for 5 members, i need to retrieve the latest record for 5 members.
2. another problem that i face is, how can i make comparison with the date. let say i want to retrieve the record that more than 90 days?

thanks for all advises.

Engloon:

For #1 you need to make sure that you have an index on "member id" so that you can efficiently retrieve records based on the "member id."

For #2 you need to have an index baded on the date and you need to make sure that your field is has a "datetime" datatype.

declare @.member table
( memberId integer,
tranDT datetime
)
insert into @.member
select 5, '1/9/6' union all
select 5, '5/8/6' union all
select 5, '12/15/6' union all
select 5, '3/12/7'
--select * from @.member

/*
memberId tranDT
-- --
5 2006-01-09 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-12-15 00:00:00.000
5 2006-03-12 00:00:00.000
*/

select top 1
memberId,
tranDt
from @.member
order by tranDt desc

/*
memberId tranDt
-- --
5 2006-12-15 00:00:00.000
*/

select memberId,
tranDt
from @.member
where tranDt < getdate() - 90
order by tranDt desc

/*
memberId tranDt
--
5 2006-12-15 00:00:00.000
5 2006-05-08 00:00:00.000
5 2006-01-09 00:00:00.000
*/

|||

1. This should give you an idea about how to get the latest entry for each member. (I assume you have a MemberID column.)

Code Snippet

SELECT
MemberID,
max( TransactionDate )
FROM MyTable
GROUP BY MemberID
ORDER BY MemberID

2. Add a WHERE clause, something like this:

Code Snippet

WHERE TransactionDate < ( dateadd( day, -90, getdate() ))

|||many thanks to Arnie Rowland and Kent Waldrop. I appreciate your help.
but the @.member table contains not only 1 member. I'm thinking to use distinct function to get only 1 record for each member.
Will let you know if I can solve the problem.

|||here is the result of my sql query
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
8 18-Sep-2005
8 18-Sep-2005
187 16-May-2006
187 14-May-2006
187 06-Jun-2006
187 29-Jul-2005
187 05-Jan-2007
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
303 19-Dec-2006
306 03-Oct-2005
306 16-Dec-2005

the result that i desire would be like this
MembershipID Date
3 03-Jan-2007
8 05-Aug-2006
187 16-May-2006
195 14-Mar-2006
239 29-Aug-2005
275 07-Aug-2005
303 28-Dec-2005
306 03-Oct-2005
|||

Use the following query...

Code Snippet

Select Identity(Int,1,1) as UID,Id,Date Into #Temp from Members;

Select Mem.Id,Mem.Date From #Temp Mem
Join (Select Min(UId) UID,Id from #Temp Group By Id) as LastRec On LastRec.UID = Mem.UID;

Drop Table #Temp;

|||

Strange, it 'sounds' like you are 'blowing off' the solution that I provided. Too bad, because using the data you posted, and the query I provided, the exact resultset you asked for is produced.

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
TransactionDate datetime
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005' )

SELECT
MembershipID,
TransactionDate = max( TransactionDate )
FROM @.MyTable
GROUP BY MembershipID
ORDER BY MembershipID

MembershipID TransactionDate
3 2007-01-03 00:00:00.000
8 2006-08-05 00:00:00.000
187 2007-01-05 00:00:00.000
195 2006-03-14 00:00:00.000
239 2005-08-29 00:00:00.000
275 2005-08-07 00:00:00.000
303 2006-12-19 00:00:00.000
306 2005-12-16 00:00:00.000

|||

But your query doesn't give the last row, it gives the max tran date. He wants the row with the max tran date:

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
Date datetime,
someOtherColumn int default (100),
yetAnotherColumn int default (100)
)

SET NOCOUNT ON

INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 3, '03-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '05-Aug-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 8, '18-Sep-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '16-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '14-May-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '06-Jun-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '29-Jul-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 187, '05-Jan-2007' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 195, '14-Mar-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 239, '29-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 275, '07-Aug-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '28-Dec-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 303, '19-Dec-2006' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '03-Oct-2005' )
INSERT INTO @.MyTable (MembershipId, Date) VALUES ( 306, '16-Dec-2005' )

select membershipId, date, someOtherColumn, yetAnotherColumn
from (select membershipId, date, someOtherColumn, yetAnotherColumn,
row_number() over (partition by membershipId order by date desc) as rowNum
from @.MyTable) as myTable
where rowNum = 1

|||

You are right Louis -but in defense, I was working with the data he provided, assuming that we were working with the 'first step'. (It's so much easier when folks provide us with an adequate explanition and sample data...)

The query that I provided should be used as a derived table and then would provide the requested data -and it works in both SQL 2000 and SQL 2005.

(However, there is one significant issue -how to deal with duplicate MembershipID/Date records -and of course, your proposed solution has that same problem).

Code Snippet

DECLARE @.MyTable table
( MembershipID int,
[Date] datetime,
MyOtherColumn int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 3, '03-Jan-2007', 1 )
INSERT INTO @.MyTable VALUES ( 8, '05-Aug-2006', 2 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 3 )
INSERT INTO @.MyTable VALUES ( 8, '18-Sep-2005', 4 )
INSERT INTO @.MyTable VALUES ( 187, '16-May-2006', 5 )
INSERT INTO @.MyTable VALUES ( 187, '14-May-2006', 6 )
INSERT INTO @.MyTable VALUES ( 187, '06-Jun-2006', 7 )
INSERT INTO @.MyTable VALUES ( 187, '29-Jul-2005', 8 )
INSERT INTO @.MyTable VALUES ( 187, '05-Jan-2007', 9 )
INSERT INTO @.MyTable VALUES ( 195, '14-Mar-2006', 10 )
INSERT INTO @.MyTable VALUES ( 239, '29-Aug-2005', 11 )
INSERT INTO @.MyTable VALUES ( 275, '07-Aug-2005', 12 )
INSERT INTO @.MyTable VALUES ( 303, '28-Dec-2005', 13 )
INSERT INTO @.MyTable VALUES ( 303, '19-Dec-2006', 14 )
INSERT INTO @.MyTable VALUES ( 306, '03-Oct-2005', 15 )
INSERT INTO @.MyTable VALUES ( 306, '16-Dec-2005', 17 )

SELECT
t.MembershipID,
t.[Date],
t.MyOtherColumn
FROM @.MyTable t
JOIN ( SELECT
MembershipID,
[Date] = max( [Date] )
FROM @.MyTable
GROUP BY MembershipID
) dt
ON ( t.MembershipID = dt.MembershipID
AND t.[Date] = dt.[Date]
)
ORDER BY t.MembershipID

MembershipID Date MyOtherColumn
-
3 2007-01-03 00:00:00.000 1
8 2006-08-05 00:00:00.000 2
187 2007-01-05 00:00:00.000 9
195 2006-03-14 00:00:00.000 10
239 2005-08-29 00:00:00.000 11
275 2005-08-07 00:00:00.000 12
303 2006-12-19 00:00:00.000 14
306 2005-12-16 00:00:00.000 17

|||i'm so sorry, i should give an example of the result i need in the begining.
and many thanks to Arnie and all programmers who tried to help me.
i really appreciate it.

how to get latest file

how to get the name of the latest file of a particular directory
thru query
i.e
i have files in a directory
1130am.txt
11.45am.txt
1200am.txt
my query should return 1200am.txtOriginally posted by vrsrinivas
how to get the name of the latest file of a particular directory
thru query

i.e
i have files in a directory

1130am.txt
11.45am.txt
1200am.txt

my query should return 1200am.txt

Assuming that all your file names are prefixed with a time stamp then you should be able to use the MAX() function to return the desired record.

create table #test (theFileName varchar(20))

insert into #test (theFileName) values ('1130am.txt')
insert into #test (theFileName) values ('11.45am.txt')
insert into #test (theFileName) values ('1200am.txt')

select max(theFileName) from #test|||I'm thinking the hard part is getting the info from the directory...

Delete From Ledger_Folder

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

Delete From Ledger_Folder_Parsed

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')