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
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to get the record created date?
How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>
How to get the record created date?
How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql
How to get the record created date?
How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>
How to get the null values in my stored procedure i am getting error dbnullvalue on front
I have the following stored proc. which i am using on the front end to get all the record from table:
if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.
CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO
Thank you very much.Note use of IsNull() function.
|||You can use the case statement in sql to look at a field before its returned. For example, you could do this (uses the pubs db)
CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
IsNull(r.ReviewerComment,''),
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASCGO
select au_lname, au_fname,
AuthorCity = Case when (city is null) then '' else city end
from authors
if any city values are null, they'll come back as a '' empty string.
Hope this helps,
Scott
Friday, March 23, 2012
how to get the last record from the database?
hi friends,
i tried to get the last record in a specified column from a database. but it fails.
for example, i have a column name as "empid" in the table name "detail". in that, the records are in the form of "Emp1, Emp2, Emp3, .........Emp12."
i used this query --> select top 1 empid from detail order by empid desc
it is showing only Emp9.
it should show Emp12.
but the id's are showing in ascending order(Emp1,2,3,.....12) when i used this query --> select empid from detail
i've a gridview to show all Employee id's with paging(1-10 per page). So Emp12 would be showed in the second page. how to get that value "Emp12" from that gridview?
but i need a SQL query rather than this method (getting from gridview).
its urgent. Plz
since you are sorting a text column, you will not get a proper numerical sort. the correct text sort sequence would be
Emp1
Emp11
Emp2
Emp21
...
Emp9
in order to approach a numerical sort with text data, you need to store leading zeros with the numeric portion of your text
Emp000001
Emp000002
Emp000009
Emp000011
Emp000021
I would recommend that your seperate out the numerical data though and store it in a column with a numeric datatype.
|||
Uuh!.......... i stored Zeroes........no use............still it is showing "Emp09".............
suggest me another method............
|||Although frankly speaking I didn't like this approach of storing string int mixed values and then trying it to sort it on the int part only, I thought to create at least something to get out of this kind of problem. I eventually came up with creating a UDF. The UDF code and sample code to test the function is as below. I hope you'll like it, but my advice is to separate out the string and int part, this will help you in days to come. Anyways, below is the code.
--- My functioncreate function ParseInt(@.valuevarchar(8000))returnsbigintasbegindeclare @.char varchar(1)declare @.lengthintdeclare @.counterintdeclare @.lastIndexintdeclare @.varIntvarchar(100)set @.varInt =''set @.counter = 1set @.length =len ( @.value )set @.lastIndex = 0while @.counter <= @.lengthbeginif ( ( @.lastIndex = 0or @.lastIndex + 1 = @.counter )and ( ascii (substring ( @.value , @.counter , 1 ) )between 48and 57 ) )beginset @.varInt = @.varInt +substring ( @.value , @.counter , 1 )set @.lastIndex = @.counterendset @.counter = @.counter + 1endreturncast ( @.varIntas bigint )endGO--- Sample Data to test the function
create table tbl(empvarchar(50))declare @.iintset @.i = 1while @.i <= 30begininsert tbl ( emp )select'empid' +cast ( @.ias varchar)set @.i = @.i + 1endGOselect * , dbo.ParseInt ( emp )from tblorder by dbo.ParseInt ( emp )desc
Hope this will help.
|||Hi,
To make things simple, just add an identity column (eg: ID) to your table Detail and then fire the query
select top 1 * from Detail order by ID desc
HTH,
Suprotim Agarwal
--
http://www.dotnetcurry.com
--
|||
Hi,
In case you are want to know how to add an identity column to an existing table :
alter table Detail
add ID int IDENTITY(1,1) not null
HTH,
Suprotim Agarwal
--
http://www.dotnetcurry.com
--
Hey friends,
no one works which is provided by you.................anyway i found a solution (not for sorting)
how to get the inserted record id
This is a multi-part message in MIME format.
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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
--=_NextPart_000_0048_01C47B19.C651B960--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...
> 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
> >
> >sql
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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
--=_NextPart_000_0048_01C47B19.C651B960
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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
--=_NextPart_000_0048_01C47B19.C651B960--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...
> 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
> >
> >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 ?
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 ?
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 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 
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 first occurrence of a record from a table?
I have the following query:
SELECTNotifications.[TimeStamp])
FROMdbo.vwGrantsMaster LEFT OUTER JOIN
dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId
It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.
What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.
Any help?
Thank you in advance.
Am I in the wrong place...?|||If I understand you correctly, you wish the FIRST row entered for a particular GrantsFMBId, [TimeStamp] combination.
Perhaps something like this will produce your desired results:
Code Snippet
SELECT dt.TS
FROM (SELECT
GrantFMBId,
TS = min( [TimeStamp] )
FROM dbo.vwGrantsMaster
GROUP BY GrantFMBId
) dt
LEFT JOIN dbo.CoFundNotifications cf
ON dt.GrantFMBId = cf.GrantFMBId
Wednesday, March 21, 2012
How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?
Can you please assist me on how to get the 2nd record in case there are
3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get
the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.
How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?
-----------------------*--
This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.
Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...
SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,
(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND
@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,
******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?
(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND
@.ToDate
ORDER BY startdate ASC) AS OLDBASIC
FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?
SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC
Gives you the second highest value of val.
If you want to do this for a set values, this may be more practical:
SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary
--
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|||or
Select min(col) from
(
select top N col from table Order by col DESC
) T
Madhivanan
Monday, March 19, 2012
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
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
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
Monday, March 12, 2012
How to get rows user defined range?
Hello,
I need a keywork like LIMIT(in oracle) which let me to get rows, I defined.
For exaple I want to show records from 100. record to 200. record!
In oracle I could do like this
Select * from Customer limit 100, 100
I could do a complex query so it let me to get what I want, but I don't
think it has a performance.
Is there a key word like LIMIT in Sql Server?http://www.aspfaq.com/show.asp?id=2120
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
> defined. For exaple I want to show records from 100. record to 200.
> record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||Generate a quota query with a ranking value. You can do all sorts of range
related tricks with such a value. Search the archives of this newsgroup for
some examples.
Anith|||Is LIMIT X,Y part of ANSI-SQL ?
I had a look, but I could only find it as a keyword, not actually defined
what it was for, syntax etc.
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
defined.
> For exaple I want to show records from 100. record to 200. record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||>> Is LIMIT X,Y part of ANSI-SQL ?
No, I think it is a MySQL dialect. Most prominent SQL variants have some
syntax that partially support quota queries.
Anith
I need a keywork like LIMIT(in oracle) which let me to get rows, I defined.
For exaple I want to show records from 100. record to 200. record!
In oracle I could do like this
Select * from Customer limit 100, 100
I could do a complex query so it let me to get what I want, but I don't
think it has a performance.
Is there a key word like LIMIT in Sql Server?http://www.aspfaq.com/show.asp?id=2120
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
> defined. For exaple I want to show records from 100. record to 200.
> record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||Generate a quota query with a ranking value. You can do all sorts of range
related tricks with such a value. Search the archives of this newsgroup for
some examples.
Anith|||Is LIMIT X,Y part of ANSI-SQL ?
I had a look, but I could only find it as a keyword, not actually defined
what it was for, syntax etc.
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
defined.
> For exaple I want to show records from 100. record to 200. record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||>> Is LIMIT X,Y part of ANSI-SQL ?
No, I think it is a MySQL dialect. Most prominent SQL variants have some
syntax that partially support quota queries.
Anith
How to get rowid of uncommitted record
I have two tables - header, detail. HeaderID appears in both, is an
auto-incrementing rowID in the header table, and required also in the detail
table. It's a one-to-many relationship from the header table to the detail
table.
The code I have writing to these tables really needs to write the detail
first, and then summariize and write the header infomation, however I can't
write the detail until I get the HeaderID by writing a header record.
So...If I write a record to the header table as a placeholder, how can I
get the HeaderID of that record while I am writing it, to use it in the
detail table (or for that matter reserve a HeaderID to be written later). I
t
is the only unique ID in the header table, and I don't have anything else
that I can uniquely use to go back and get the record just written to get th
e
header table. Here's the further challenge - all of these are uncomitted
transactions until both detail and header records for the transaction have
been written, at which point I will commit them.
Tell me there is some magic way to get the id of a record I am about to
write, am writing, or have written. Help!
Thanks!Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
> I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail table. It's a one-to-many relationship from the header table to
> the detail table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't write the detail until I get the HeaderID by writing a header
> record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It is the only unique ID in the header table, and I don't have anything
> else that I can uniquely use to go back and get the record just written to
> get the header table. Here's the further challenge - all of these are
> uncomitted transactions until both detail and header records for the
> transaction have been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!
What are you using to create the records? You should have no trouble getting
the rowid of the inserted header record. For instance, I myself use ADO to
create a header record in a table inside a transaction, and pull back the
identity value exactly the same way as if it wasn't in a transaction - I
just read it from the column. I then create the detail lines to go with it,
and at the end commit the transaction. If I have to rollback, both the
header and the detail lines get rolled back. There should be no need to be
doing anything special - this is exactly the same code I was using before I
added a transaction (which was simply of case of adding a BeginTrans ADO
Connection method call before the header record was created, and a
CommitTrans method call at the end).
Dan|||Look at scope_identity() in Books Online.
It doesn't matter if the transaction is not committed.
So, after you do your first insert:
declare @.headerID INT
insert...
set @.headerID = scope_identity()
Don't forget to handle errors.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail
> table. It's a one-to-many relationship from the header table to the
> detail
> table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't
> write the detail until I get the HeaderID by writing a header record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It
> is the only unique ID in the header table, and I don't have anything else
> that I can uniquely use to go back and get the record just written to get
> the
> header table. Here's the further challenge - all of these are uncomitted
> transactions until both detail and header records for the transaction have
> been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!|||Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
command. the HeaderID is an identity colum increments by the DB. I've just
never had to do this beofre so this is new terrirotry. I guess my question
is how either when I am executing the command or after I execute it i can ge
t
the assigned headerID. Understand that this DB will process millions of
trx/day, so I can't just write a record and go back and get the max id as
something/someone else could have easly incremented it in the meantime. How
are you going back to get the id regardless of it being within a transaction
?
Thanks!
"Daniel Crichton" wrote:
> Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
>
>
> What are you using to create the records? You should have no trouble getti
ng
> the rowid of the inserted header record. For instance, I myself use ADO to
> create a header record in a table inside a transaction, and pull back the
> identity value exactly the same way as if it wasn't in a transaction - I
> just read it from the column. I then create the detail lines to go with it
,
> and at the end commit the transaction. If I have to rollback, both the
> header and the detail lines get rolled back. There should be no need to be
> doing anything special - this is exactly the same code I was using before
I
> added a transaction (which was simply of case of adding a BeginTrans ADO
> Connection method call before the header record was created, and a
> CommitTrans method call at the end).
> Dan
>
>|||Thanks!
"Raymond D'Anjou" wrote:
> Look at scope_identity() in Books Online.
> It doesn't matter if the transaction is not committed.
> So, after you do your first insert:
> declare @.headerID INT
> insert...
> set @.headerID = scope_identity()
> Don't forget to handle errors.
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>
>|||Matt wrote on Fri, 2 Dec 2005 08:31:07 -0800:
> Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
> command. the HeaderID is an identity colum increments by the DB. I've
> just never had to do this beofre so this is new terrirotry. I guess my
> question is how either when I am executing the command or after I execute
> it i can get the assigned headerID. Understand that this DB will process
> millions of trx/day, so I can't just write a record and go back and get
> the max id as something/someone else could have easly incremented it in
> the meantime. How are you going back to get the id regardless of it being
> within a transaction? Thanks!
In ADO it's pretty simple - you just use a recordset Keyset cursor type with
a server-side cursor, write the values in to the new row, and after running
the .Update method of the recordset read the value of the identity field.
That's it. No messing around with making other calls back to SQL Server, or
trying to determine the row id in some other arcane way.
Dan
auto-incrementing rowID in the header table, and required also in the detail
table. It's a one-to-many relationship from the header table to the detail
table.
The code I have writing to these tables really needs to write the detail
first, and then summariize and write the header infomation, however I can't
write the detail until I get the HeaderID by writing a header record.
So...If I write a record to the header table as a placeholder, how can I
get the HeaderID of that record while I am writing it, to use it in the
detail table (or for that matter reserve a HeaderID to be written later). I
t
is the only unique ID in the header table, and I don't have anything else
that I can uniquely use to go back and get the record just written to get th
e
header table. Here's the further challenge - all of these are uncomitted
transactions until both detail and header records for the transaction have
been written, at which point I will commit them.
Tell me there is some magic way to get the id of a record I am about to
write, am writing, or have written. Help!
Thanks!Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
> I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail table. It's a one-to-many relationship from the header table to
> the detail table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't write the detail until I get the HeaderID by writing a header
> record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It is the only unique ID in the header table, and I don't have anything
> else that I can uniquely use to go back and get the record just written to
> get the header table. Here's the further challenge - all of these are
> uncomitted transactions until both detail and header records for the
> transaction have been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!
What are you using to create the records? You should have no trouble getting
the rowid of the inserted header record. For instance, I myself use ADO to
create a header record in a table inside a transaction, and pull back the
identity value exactly the same way as if it wasn't in a transaction - I
just read it from the column. I then create the detail lines to go with it,
and at the end commit the transaction. If I have to rollback, both the
header and the detail lines get rolled back. There should be no need to be
doing anything special - this is exactly the same code I was using before I
added a transaction (which was simply of case of adding a BeginTrans ADO
Connection method call before the header record was created, and a
CommitTrans method call at the end).
Dan|||Look at scope_identity() in Books Online.
It doesn't matter if the transaction is not committed.
So, after you do your first insert:
declare @.headerID INT
insert...
set @.headerID = scope_identity()
Don't forget to handle errors.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>I have two tables - header, detail. HeaderID appears in both, is an
> auto-incrementing rowID in the header table, and required also in the
> detail
> table. It's a one-to-many relationship from the header table to the
> detail
> table.
> The code I have writing to these tables really needs to write the detail
> first, and then summariize and write the header infomation, however I
> can't
> write the detail until I get the HeaderID by writing a header record.
> So...If I write a record to the header table as a placeholder, how can I
> get the HeaderID of that record while I am writing it, to use it in the
> detail table (or for that matter reserve a HeaderID to be written later).
> It
> is the only unique ID in the header table, and I don't have anything else
> that I can uniquely use to go back and get the record just written to get
> the
> header table. Here's the further challenge - all of these are uncomitted
> transactions until both detail and header records for the transaction have
> been written, at which point I will commit them.
> Tell me there is some magic way to get the id of a record I am about to
> write, am writing, or have written. Help!
> Thanks!|||Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
command. the HeaderID is an identity colum increments by the DB. I've just
never had to do this beofre so this is new terrirotry. I guess my question
is how either when I am executing the command or after I execute it i can ge
t
the assigned headerID. Understand that this DB will process millions of
trx/day, so I can't just write a record and go back and get the max id as
something/someone else could have easly incremented it in the meantime. How
are you going back to get the id regardless of it being within a transaction
?
Thanks!
"Daniel Crichton" wrote:
> Matt wrote on Fri, 2 Dec 2005 07:58:03 -0800:
>
>
> What are you using to create the records? You should have no trouble getti
ng
> the rowid of the inserted header record. For instance, I myself use ADO to
> create a header record in a table inside a transaction, and pull back the
> identity value exactly the same way as if it wasn't in a transaction - I
> just read it from the column. I then create the detail lines to go with it
,
> and at the end commit the transaction. If I have to rollback, both the
> header and the detail lines get rolled back. There should be no need to be
> doing anything special - this is exactly the same code I was using before
I
> added a transaction (which was simply of case of adding a BeginTrans ADO
> Connection method call before the header record was created, and a
> CommitTrans method call at the end).
> Dan
>
>|||Thanks!
"Raymond D'Anjou" wrote:
> Look at scope_identity() in Books Online.
> It doesn't matter if the transaction is not committed.
> So, after you do your first insert:
> declare @.headerID INT
> insert...
> set @.headerID = scope_identity()
> Don't forget to handle errors.
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:911A2464-EE82-4A4D-999E-402C2DD6AAC4@.microsoft.com...
>
>|||Matt wrote on Fri, 2 Dec 2005 08:31:07 -0800:
> Im using .NET SQLConnection and updating with a SQLcommand.executenonquery
> command. the HeaderID is an identity colum increments by the DB. I've
> just never had to do this beofre so this is new terrirotry. I guess my
> question is how either when I am executing the command or after I execute
> it i can get the assigned headerID. Understand that this DB will process
> millions of trx/day, so I can't just write a record and go back and get
> the max id as something/someone else could have easly incremented it in
> the meantime. How are you going back to get the id regardless of it being
> within a transaction? Thanks!
In ADO it's pretty simple - you just use a recordset Keyset cursor type with
a server-side cursor, write the values in to the new row, and after running
the .Update method of the recordset read the value of the identity field.
That's it. No messing around with making other calls back to SQL Server, or
trying to determine the row id in some other arcane way.
Dan
How to get rid of time in a date/time field?
I am using vb.net 2005 with crystal and I am wanting to only show the date on the record being grabbed from SQL. currently it is showing the date along with the time stamp. Is there an easy way to do this in Crystal?I figured it out...format object :-) Easy enough!
How to get rid of a corrupt record
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?Are you getting any errors? Did you try running DBCC CHECKTABLE on that
table?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?|||Rakhi
You could try running DBCC Checktable. You can run this
with parameters that can try to repair errors. See BOL for
details.
Hope this helps
John|||Have you tried:
DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)
...Most likely the problem is NOT a corrupt record, but rather a hanging
connection that has the record locked.
Run your query and check the "management" section to see if your connection
is blocked.
Bruce
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Hi,
Try running dbcc checktable on that table with Repair options after taking a
database backup or table backup (Select * into bckuptable from table). If it
fails again u can try the below steps:
1. Create a new table same as the corrupted table
2. Write a script to copy the data in batches excluding the corrupted data.
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Thanks
Hari
MCDBA
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Thanks everyone. Will try out all these steps and report the results.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eq#M#3WnDHA.644@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try running dbcc checktable on that table with Repair options after taking
a
> database backup or table backup (Select * into bckuptable from table). If
it
> fails again u can try the below steps:
> 1. Create a new table same as the corrupted table
> 2. Write a script to copy the data in batches excluding the corrupted
data.
> 3. Rename the corrupted table to table_corrupt
> 4. Rename the new table to original name
> 5. Create all indexes for this new table
> Thanks
> Hari
> MCDBA
>
> "RB" <rakhi.bhatia@.verizon.net> wrote in message
> news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I have a corrupt record in my database that I can't query on. Nor can I
> > delete or update. Running any of the Select, Update or Delete statements
> in
> > QA using the WHERE clause with ID = <the corrupt record ID> causes the
> > system to hang until it eventually times out.
> >
> > How can I get rid of this record ?
> >
> >
>|||To add to the other posts:
If this indeed is a corrupted row, you want to find out why it became corrupted in the first place.
If you have a hw problem, you want to fix that!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RB" <rakhi.bhatia@.verizon.net> wrote in message news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?Are you getting any errors? Did you try running DBCC CHECKTABLE on that
table?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?|||Rakhi
You could try running DBCC Checktable. You can run this
with parameters that can try to repair errors. See BOL for
details.
Hope this helps
John|||Have you tried:
DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)
...Most likely the problem is NOT a corrupt record, but rather a hanging
connection that has the record locked.
Run your query and check the "management" section to see if your connection
is blocked.
Bruce
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Hi,
Try running dbcc checktable on that table with Repair options after taking a
database backup or table backup (Select * into bckuptable from table). If it
fails again u can try the below steps:
1. Create a new table same as the corrupted table
2. Write a script to copy the data in batches excluding the corrupted data.
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Thanks
Hari
MCDBA
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Thanks everyone. Will try out all these steps and report the results.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eq#M#3WnDHA.644@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try running dbcc checktable on that table with Repair options after taking
a
> database backup or table backup (Select * into bckuptable from table). If
it
> fails again u can try the below steps:
> 1. Create a new table same as the corrupted table
> 2. Write a script to copy the data in batches excluding the corrupted
data.
> 3. Rename the corrupted table to table_corrupt
> 4. Rename the new table to original name
> 5. Create all indexes for this new table
> Thanks
> Hari
> MCDBA
>
> "RB" <rakhi.bhatia@.verizon.net> wrote in message
> news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I have a corrupt record in my database that I can't query on. Nor can I
> > delete or update. Running any of the Select, Update or Delete statements
> in
> > QA using the WHERE clause with ID = <the corrupt record ID> causes the
> > system to hang until it eventually times out.
> >
> > How can I get rid of this record ?
> >
> >
>|||To add to the other posts:
If this indeed is a corrupted row, you want to find out why it became corrupted in the first place.
If you have a hw problem, you want to fix that!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RB" <rakhi.bhatia@.verizon.net> wrote in message news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>
Friday, March 9, 2012
How to get record count only from FTS
Hi,
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar
How to Get previous record thru sql query
How to Get previous record thru sql query
For the example
my table:
1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07
--
how to use the query to make them join became like this (get/join with the next record)
1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
3 userc item2 3.00 03/02/07 null null null
>.<
need help ... thanks alot
You can try something like :
select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.date != m2.Date
Hope this helps,
Vivek
|||it will work only if dates are different change it to (assuming that your first column is Identity ID)
select m1.*, m2.Item, m2.Qty, m2.Date from mytable m1 LEFT JOIN mytable m2 ON m1.user=m2.user WHERE m1.ID != m2.ID
to get data in all cases, but this will return only two sets of data per customer what if you have more records per customer?
And it will also repeat entries for your test data you will have records for users reported two times in different order 1 with 2 and 2 with 1
so you have to modify this query a little
select m1.*, m2.Item, m2.Qty, m2.Date
from mytable m1
LEFT JOIN mytable m2
ON m1.user=m2.user
andm1.ID != m2.ID
WHEREm1.ID < m2.ID
but it will work only if you have no more than 2 records per user
try and let me know if it works fro you or not.
Thanks
|||Thanks alot who has replied me ^^
Yes.. i hav try on the way thatJpazgier provided, but it's too bad i m having more than 2 records in same user and the order is not just only at the top and bottom for example it may
1 usera item1 1.00 01/02/07
2 usera item1 2.00 02/02/07
3 userc item2 3.00 03/02/07
4 usera item1 1.50 04/02/07
and after i have join it will become
1 usera item1 1.00 01/02/07 item1 2.00 02/02/07
2 usera item1 2.00 02/0207 item1 1.50 04/02/07
3 userc item2 3.00 03/02/07 null null null
4 usera item1 1.50 04/02/07 null null null
thank u very much for ans my question ^^
|||Another query for getting two consecutive rows (the row which you want along with the previous row)
select * from TableName Where DateField='02/03/07' Or DateField in(Select Top 1 DateField from TableName where DateField < ''02/03/07' Order by DateField DESC)
Thanks
Subscribe to:
Posts (Atom)