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 sql statement executed from external application?

Hello,

In my database (SQL Server 2005), some data were inserted from a external application.

In order to validate the data , I want to get the SQL statment executed by the application.

Is this possible?

Thanks

Robert

Hi Robert,

The Data Manipulation sql statements are not audited or logged by default.

If you want to capture SQL Statements from the application, you can use SQL Profiler.

If you need this for auditing, You could you use server side traces (its uses the same api as SQL Profiler but runs in the background).

Jag

|||

Hi Jag,

Thanks for your replay.

SQL Profiler is a good tool, I finished my work with it.

Robert

How to get the first occurrence of a record from a table?

i also have a query like this !

select DISTINCT(HospitalName),AvgTotalPatients,TotalPatient from TotalPatients ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

Hospitalnames are unique and AvgTotalPatients is also unique for every hospital now what i get is all records of all hopsitals reason is because in column TotalPatient there are entered many records for every hoapital so it takes all of them, what i want that it should select distinct hospitals with their AVgTotalPatients and only first occurence of TotalPatient.

what should i do? please help urgent

It sounds like you are aggregating data in some fashion.

It might be easier to help you find a good solution if you provided the code that creates [TotalPatients], or at least a better understanding of what/how [AvgTotalPatients] and [TotalPatient] really means.

However, your thinking 'should' be somewhat like this:

Code Snippet

SELECT
HospitalName,
AvgTotalPatients = avg( TotalPatients ),
TotalPatient = min( TotalPatients )
FROM TotalPatients
GROUP BY HospitalName
ORDER BY
HospitalName

|||

Thnx Arnie. I found solution to it 2 days back and it was something like this !

select DISTINCT(TP.HospitalName),TP.AvgTotalPatients,TP.TotalPatient from TotalPatients TP where TotalPatient = ( Select TOP 1(TotalPatient ) from TotalPatients where HospitalName = TP.HospitalName) ORDER BY HospitalName,AvgTotalPatients,TotalPatient;

very complicated one but i got what i wanted

Regards

How to get Specific rows from Table

hi

i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200

HI,

do it as

for n to n1 records do as

select top n1-n * from

(

select top n1 * from

)

order by key desc

|||

If your total result set is small enough (> 1000 records) you could insert into a table variable with an identity column and then select back out referencing the identity column in the where clause. For example:

DECLARE @.tblTable TABLE

(

TableID INT IDENTITY(1,1)

,OtherID INT

,Value VARCHAR(50)

)

INSERT @.tblTable (OtherID, Value) VALUES (1, 'One')

INSERT @.tblTable (OtherID, Value) VALUES (2, 'Two')

INSERT @.tblTable (OtherID, Value) VALUES (3, 'Three')

INSERT @.tblTable (OtherID, Value) VALUES (4, 'Four')

INSERT @.tblTable (OtherID, Value) VALUES (5, 'Five')

DECLARE @.Start INT, @.End INT

SELECT @.Start = 1, @.End = 3

SELECT *

FROM @.tblTable

WHERE TableID BETWEEN @.Start AND @.End

|||

It seems that you are trying to do pagination in database.

Below is the code snippet for a stored procedure. This takes page number and numbers of records in a Page.

CREATE PROCEDURE Pagination
@.Page int,
@.Size int
AS

DECLARE @.Start int, @.End int
BEGIN TRANSACTION GetDataSet
SET @.Start = (((@.Page - 1) * @.Size) + 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
SET @.End = (@.Start + @.Size - 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @.@.ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
-- Any kind of select statement is possible with however many joins
-- as long as the data selected can fit into the temporary table.
IF @.@.ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @.Start) AND (Row <= @.End)
IF @.@.ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @.@.ERROR

Regards

Sachin

How to get Server Name? (Using VC++, MSSQL & ODBC)

Hi. Is there a way in order to get the Server name using MSSQL ODBC in Visual C++? Here's my code:

CString wstr_connect ;
wstr_connect.Format( "DSN=%s;UID=%s;PWD=%s", gstr_DBName, gstr_UserID, gstr_Password ) ;
CDatabase mydb ;
BOOL ret = mydb.OpenEx( wstr_connect, CDatabase::noOdbcDialog ) ;
CString info = mydb.GetConnect( ) ;

GetConnect only returns the database name, user ID, password and WSID. How do i get the server name?

Thanks in advance!I'm not using CDatabase at the moment, but check out this page:
http://www.cppdoc.com/example/mfc/classdoc/MFC/CDatabase.html

There is a function called GetConnectInfo(); maybe that can help you?|||I think it depends on the driver you are using. I think one problem you are having is that you are not actually providing a "DBName"; your Format function has a string with "DSN=" which I am nearly certain means that you are using a datasource. The server is specified in the datasource.

Note that the CDatabase class has a member m_hdbc; it can be used to get a handle for use with the SDK version of ODBC. Look at the sample code for CDatabase::m_hdbc; it might lead you to a good answer.|||Maybe I'm missing something, but couldn't you just use:SELECT @.@.servername-PatP|||Maybe I'm missing something, but couldn't you just use:SELECT @.@.servername-PatPI don't know how to do that in VC; cwong asked how to do it "using MSSQL ODBC in Visual C++".

How to get row count from an inner query

Hi All,

I have the following SQL query:

select temp.emp_id, temp.rownum

from

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

) temp

where temp.rownum <=10

group by temp.emp_id

I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.

Hi,

I wrote something similar in here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2188269&SiteID=1

But the problem may be, that the Number of Rows in the outer Statement dosent match to the number of rows in the inner Statement.

Best Regards

Raimund

|||

How about this,

Code Block

with temp

as

(

select emp_id, row_number() over (order by emp_id) as rownum from employee

)

,countfinder

as

(

select count(*) as [rowcount] from temp

)

select temp.emp_id, temp.rownum, (select [rowcount] from countfinder) [totalrowcount] from temp

where temp.rownum <=10 group by temp.emp_id

|||Brilliant.....this was a very novel way of doing it. Thank you very much. I will try out the same in my implementation.