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 value of a SQL query before binding it to DataGrid?

I am using C#.Net, Visual web Developer, SQL server 2000.

I have a SQL query which I am binding it to a DataGrid.

SQL : "SELECT ord_number, ord_ID, ord_split, ord_Name, ETD_Date, OSP_FSD FROM ORDERS"

In My DataGrid I have a dynamic databound column.

I am able to bind one column to this databound column using following code.

BoundColumn ETDDate = new BoundColumn();
ETDDate.HeaderText = "ETD Date";
ETDDate.DataField = "OSP_FSD";
mygrid2.Columns.AddAt(ETDDate);

but now I want to bind this databound column based on the following criteria to two different database columns.

if(ord_split = 1)
{
ETDDate.DataField = "OSP_FSD";
}
else
{
ETDDate.DataField = "ETD_Date";
}

How to get value of ord_split before binding SQL to teh DataGrid? i.e I just want to take value of ord_split and not all the values of SQL.

Please Help!

HI~

ord_split if different in each row but if you change ETDDate.DataField it will affect all rows.

I am afraid you just want to display OSP_FSD for the rows " ord_split =1 " or ETD_Date if not.

If so you can change the text inGridView'sRowDataBound Event.

how to get the top value?

hi,

this my prediction query.

select Flattened (select $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

Its giving me result where the date is less than 1/1/2007.i need only one result, where the date should be nearer to 1/1/2007. I am not able to use Top 1 inside like

select Flattened (select top 1 $time as Date, [Perf] as Perf from PredictTimeSeries([Perf],100) where $Time <'1/1/2007' ) as A from [Stud_Model] WHERE [StudID]=8

can anyone help.

Thanks,

Karthik

I haven't tried a query like that, but you can't use Top in that context. You may be able to use the TopCount function, I believe the syntax is

TopCount(<table expression>, <count>, <column-ref>) // the last two may be reversed.

TopCount is itself a table expression.

|||

hi jamie,

Its working cool.

Thanks a lot.

How to get the second row of a recordset?

Here's my SQL Statement (I'm using MS SQL 2000):

SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName
FROM Menu
ORDER BY MenuDate DESC

This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).

Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!

MikeYou want the last row? Is that correct? Use the MoveLast method of the ADO recordset object. This will take you to the second row using a top 2.

eg:
dim Conn = ".... Your connection string ...."

Set dbAPI = Server.CreateObject("ADODB.Connection")
set rs1 = Set rs1 = Server.CreateObject("ADODB.Recordset")

myCmd = "select ........"

dbAPI.Open Conn

set rs1 = dbAPI.Execute myCmd

if rs1.EOF = false then

rs1.MoveLast

myvar1 = rs1("myCol1")
myvar2 = rs1("myCol2")
... and so on

end if

dbAPI.Close

Hope this helps.|||SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM Menu
ORDER BY MenuDate DESC) Menu
ORDER BY Menu.MenuDate

I hope this will solve your problem.|||Thank you both VERY MUCH! The move last would work (can't believe I didn't think of it). I decided to use Rudra's subquery because it was faster (in other words, less typing for me).

Thanks again for solving a problem that was driving me crazy!

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

|||SELECT * FROM TABLE WHERE CONDITION ORDER BY DESC FIELDNAME LIMIT 1,1sql

How to get the second biggest number using sql query?

Hi,

Now I have a task to get the second biggest number using sql query. I know that we can get the biggest number using SQL very easily: select Max(NumColumn) from aTable.

For example, I have the following records:

NumColumn

1

2

3

4

10

11

18

If I use "select Max(NumColumn) from aTable", I will get the number: 18.

But I need to use SQL to the second biggest number: 11.

How to do that? Thanks.

Hi,

Try this

Code Snippet

select max(NumColumn) from aTable where NumColumn<=(select max(NumColumn) from atable)

-- Edited (Previous query i wrote was for Mysql.sorry about that)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Or this...

Code Snippet

SELECT MAX(NumColumn) FROM aTable WHERE NumColum < (SELECT MAX(NumColumn) FROM aTable)

|||

Hi,

To calculate the nth highest no.

Code Snippet

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Try:

if the values are unique:

with cte

as

(

select *, row_number() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

if the values are not unique

with cte

as

(

select *, dense_rank() over(order by NumColumn ASC) as rn

from dbo.t1

)

select *

from cte

where rn = 2

Example:

Code Snippet

use tempdb

go

declare @.t table (

NumColumn int

)

insert into @.t values(1)

insert into @.t values(2)

insert into @.t values(3)

insert into @.t values(4)

insert into @.t values(10)

insert into @.t values(11)

insert into @.t values(18)

;with cte

as

(

select *, row_number() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

insert into @.t values(1)

;with cte

as

(

select *, dense_rank() over(order by NumColumn) as rn

from @.t

)

select *

from cte

where rn = 2

go

AMB|||

Suprotim - your queries don't work as advertised

@.temp = 2 this gives you the the max

@.temp = 3 gives 2nd highest

|||

What if you have

NumColumn

1

2

3

4

10

11

17

17

17

18

18

What number should be returned?|||

Hi Sql-pro,

Which one doesn't work?

I tried it against this :

NumColumn

1

2

3

4

10

11

18

Suprotim

|||

Hi,

Sorry, not trying to be a jerk but all 3 of them didn't work against 1, 2, 3, 4

Code Snippet

select max(AccountID) from Check_Account where AccountID<=(select max(AccountID) from Check_Account)

go

= 4

--2nd highest

Declare @.temp int

set @.temp = 1

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

go

= 4

--3rd highest

Declare @.temp int

set @.temp = 2

SELECT MAX(AccountID) from Check_Account WHERE AccountID NOT IN ( SELECT TOP (@.temp - 1) AccountID FROM Check_Account ORDER BY AccountID DESC)

= 3

|||

Is your NumberColumn guaranteed to be unique? In other words, might you have values like this:

NumColumn

1

1

2

3

4

4

4

5

5

6

7

8

8

8

9

9

If so, what would the number you would like returned?

|||

Dear rusag,

Modified the query :

Code Snippet

Declare @.temp int

set @.temp = 2

;with cte

as

(

select DISTINCT * from atable

)

SELECT MAX(NumColumn) from cte WHERE NumColumn NOT IN ( SELECT TOP (@.temp -1) NumColumn FROM cte ORDER BY NumColumn DESC)

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Dear SqlPro,

Code Snippet

I took the data 1,2,3,4

' For 2nd Highest

Declare @.temp int

set @.temp = 1

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 3

' For 3rd Highest

Declare @.temp int

set @.temp = 2

SELECT MAX(NumColumn) from aTable WHERE NumColumn NOT IN ( SELECT TOP (@.temp - 1) NumColumn FROM atable ORDER BY NumColumn DESC)

Returns 2

How is it that you get different results?

Suprotim

|||

Well if you look at your 2nd highest query

You have @.temp = 1

and your subquery is SELECT TOP @.temp - 1

1 - 1 = 0 so your subquery returns nothing, and therefore your just getting the max

it's all semantics anyway, I think your approach is fine, just need to know that

@.temp = 2 returns 2nd highest

@.temp = 3 return 3rd highest

|||

Hai,

Try the following query

select NumColumn from
aTable t1
where N=(

select count(distinct t2.NumColumn)

from aTable t2 where t2.NumColumn>t1.NumColumn

)

Where N= position-1

If uwant to find second largest N should be =1 (2-1)

how to Get the result of executed query in file

All ,

Is it possible that i can get the result of executed select statement in a .txt file.

some thing like this

select * from mytablw to <some file name.txt>

Regards,

Ashish

You need to use BCP.

You either invoke it from a cmd window or use xp_cmdshell in SS.

Code Snippet

From cmd:

bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd

From SQL Server:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'

Look up BCP in BOL for more options and parameters.

how to get the real boundary of "time" dimension of a subcube dynamically?

how to get the real boundary of "time" dimension of a subcube dynamically?

e.g.

CREATE SUBCUBE [SMS2005] AS
(
SELECT
(
[time].[date].[date].&[2007-01-01T00:00:00] : [time].[date].[date].&[2007-01-10T00:00:00]
)
ON COLUMNS
FROM [SMS2005]
)
go

I want to create a global measure that show the stocks on the boundaries of the subcube'range
(in this case , they are 2007-1-1 and 2007-1-10).

Whenever a different new subcube (focused on time dimension) is built,
this measure can behave differently based on the subcube.

To this problem ,
I think the key is that
how to get the real boundary of "time" dimension of the subcube dynamically
in the definition of the globally wanted measure.

Does anyone has a inspiration?

3x.

So eagerly for the answer.|||

I wrote the following measure,but it does not work.

create calculated member
sms2005.measures.w as
([time].[date].[date].members.item(0) , [Measures].[stocks])
go

|||UP ! Is there anyone who knows how to get it?

How to get the last occurence of rows containing disticnt value in one column

I have a table that tracks GPS records broadly speaking as follows

tsDATETIME
usernameVARCHAR(16)
...
GPS data
...

I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.

For a single user I know I can do

SELECT TOP 1 * from <table> order by ts desc

But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.

I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:

SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)

It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.

--
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Even pseudo-DDL is better than narratives. Is this
what you meant?

CREATE TABLE Foobar
(event_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(16) NOT NULL,
stuff_1 INTEGER NOT NULL,
stuff_2 INTEGER NOT NULL,
..
stuff_n INTEGER NOT NULL,
PRIMARY KEY (user_name, event_time));

SELECT F1.*
FROM Foobar AS F1
WHERE F1.event_time
= (SELECT MAX(f2.event_time)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>What is the primary key? I'll assume the key consists of (ts,
>username), in which case the folllowing should do what you want:
>SELECT ts, username, ... /* other columns */
> FROM YourTable AS T
> WHERE ts =
> (SELECT MAX(ts)
> FROM YourTable
> WHERE username = T.username)

Thanks

>It really helps if you include DDL with questions like this (basically
>a CREATE TABLE statement, including keys and constraints). The exact
>table structure may make a big difference to the possible solutions.

Point noted.

>The usual recommendation that you shouldn't use SELECT * in production
>code also applies.

I realise this. That was just me being lazy (although for some of my
selects I do want the entire row).

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"--CELKO--" <jcelko212@.earthlink.net> wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications. Even pseudo-DDL is better than narratives. Is this
>what you meant?

Sorry. Will do in future.

Thanks for the solution.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/sql

how to get the inserted record id

Select @.@.identity will give you the record just inserted.
You must be specifying some criteria for the deletes. When
you specify, do a select on that field.

>--Original Message--
>hi all
>I have auto number field in my table with title ID
>I wantted to know when we add a record in table, SQL
server takes the responsibility of increntin / adding auto
number in field.
>Please advise if there is any way to get this auto number
from table. Like we have table inserted and deleted ... (
getting maximum is not working in multiple user
environemnt ) Is there any stored procedure or variable i
can query ?
>thanks.
>Khurram Alam
>In SQL Server 2000, @.@.IDENTITY will work, however, in a high volume
multi-user environment, the BOL suggest that you use the SCOPE_IDENTITY()
function instead.
For more information, please see the Books Online
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4a01c47afa$aa5f6e10$a501280a@.phx.gbl...[vbcol=seagreen]
> Select @.@.identity will give you the record just inserted.
> You must be specifying some criteria for the deletes. When
> you specify, do a select on that field.
>
>
> server takes the responsibility of increntin / adding auto
> number in field.
> from table. Like we have table inserted and deleted ... (
> getting maximum is not working in multiple user
> environemnt ) Is there any stored procedure or variable i
> can query ?

how to get the inserted record id

Select @.@.identity will give you the record just inserted.
You must be specifying some criteria for the deletes. When
you specify, do a select on that field.

>--Original Message--
>hi all
>I have auto number field in my table with title ID
>I wantted to know when we add a record in table, SQL
server takes the responsibility of increntin / adding auto
number in field.
>Please advise if there is any way to get this auto number
from table. Like we have table inserted and deleted ... (
getting maximum is not working in multiple user
environemnt ) Is there any stored procedure or variable i
can query ?
>thanks.
>Khurram Alam
>
In SQL Server 2000, @.@.IDENTITY will work, however, in a high volume
multi-user environment, the BOL suggest that you use the SCOPE_IDENTITY()
function instead.
For more information, please see the Books Online
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:0a4a01c47afa$aa5f6e10$a501280a@.phx.gbl...[vbcol=seagreen]
> Select @.@.identity will give you the record just inserted.
> You must be specifying some criteria for the deletes. When
> you specify, do a select on that field.
>
> server takes the responsibility of increntin / adding auto
> number in field.
> from table. Like we have table inserted and deleted ... (
> getting maximum is not working in multiple user
> environemnt ) Is there any stored procedure or variable i
> can query ?

How to get the index of each row from a SELECT query in SQL ?

Hi,
I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.
ie, I want to get a dataset like :
"0", "dataCol1", "dataCol2" for the first line
"1", "dataCol1", "dataCol2" for the second line
"2", "dataCol1", "dataCol2" for the third line
etc.
Anyone knows if there is a sql statement that does it ?
Thanks
Johanncheck this|||You can also create a temp table with an int identity column, insert the first table into the temp, and return it.

How to get the in Quarterly

Hi All,

I want to select the data from the table in Quarterly
i have the following query but it is suming all the
months and giving
in following query i want to show sum of 01 to
03months in row and o4 to 06 in one row...like
that if i give 200101 to 200112...it has to give me 4
quaters...how to do that..

select CONVERT(CHAR(4),period,112), sum(composite)
from cdh_price_gap
where CONVERT(CHAR(6),period,112) between '200101' and
'200106'
group by CONVERT(CHAR(4),period,112)
order by CONVERT(CHAR(4),period,112)

i used to convert to compare the date in this formate
200101 thats it...

thanksselect cast( (month(period)+2) / 3 as integer ) as quarter
, sum(composite)
from cdh_price_gap
group by cast( (month(period)+2) / 3 as integer )

rudy
http://r937.com/

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 the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

sql

How to get the database name dynamically?

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=?)

Can someone help me out?

Thanks.

Try the DB_NAME() function.

|||

DB_NAME() works but if i have to run this query dynamically in the database as per the database parameter how do i do it?

like

declare @.dbname varchar(100)

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%Master'

and DB_NAME=@.dbname

|||

You need to use dynamic SQL to execute commands in multiple databases at run-time. See example below on how to do it the safe way (without SQL injection risks):

Code Snippet

-- SQL Server 2005 version:

-- Will work with minimal changes in SQL Server 2000 too:

declare @.sp nvarchar(500), @.dbname nvarchar(128);

declare @.dbs cursor;

set @.dbs = cursor fast_forward for

select name from sys.databases

where name not in ('master', 'model', 'msdb', 'tempdb');

open @.dbs;

while(1=1)

begin

fetch @.dbs into @.dbname;

if @.@.fetch_status < 0 break;

set @.sp = quotename(@.dbname) + N'.sys.sp_executesql';

exec @.sp N'

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N''.'' + QUOTENAME(TABLE_NAME)),

''IsMSShipped'') = 0

'

end;

How to get the @@Identity for multiple Inserts?

Hi All,

Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @.@.Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.

Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@.@.Identity,'SomeOtherValue')

Thanks
Tanveerrewrite to a cursor insert
or
after the hughe insert, determine which are new and update/insert the depending table accordingly.
I'm not entirely sure what you mean by 'this situation' though.|||A better solution would be to create a temporary table .. lets say UserConsolidated like
(Userid,FirstName, SecondName,OtherFields) and then use this table to get data into User and UserDependent.|||At least for now, the @.@.identity values are processed as a block, in other words if you have five different inserts going (on different spids) at the same time, each block of the five will have a contiguous range of @.@.identity values. This means that if you insert 30 rows, the @.@.identity value will be for the first row, and @.@.identity+29 will be the value for the last row in your insert.

Note that this will change at some point in time, probably in the release after Yukon.

-PatP

how to get tables involved in constraint

Hi,
The following request select a constraint from TABLE_CONSTRAINT with
the name specified in the where clause:

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
constraint_name = 'FK__51OtherParties__51Claims'

It returns:

http://graphicsxp.free.fr/constraint.JPG

So I have TABLE_NAME that correspond to the first table involved in the
constraint, but how do I get 51Claims ??

Thank youHi, Sam

You can use something like this:

SELECT TC.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON TC.CONSTRAINT_NAME=RC.UNIQUE_CONSTRAINT_NAME
AND TC.CONSTRAINT_SCHEMA=RC.UNIQUE_CONSTRAINT_SCHEMA
WHERE RC.CONSTRAINT_NAME = 'FK__51OtherParties__51Claims'

Razvan|||Hi Razvan,

Many thanks, it works !

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish