ID Field Reset

This morning I made a change to two tables in my live system. All I did was add a new column in each called 'suspense' with a data type of bit and a default value of '0'.

When the users started using the system they noticed that the record ID numbers had reset and were starting from 1 and going up 2, 3, 4 etc. The last good id number was about 20500. The id columns are set as primary key with identity and increment of 1.

This shouldn't happen should it? Is it a bug? I never touched the ID column, just added the new one.I bet you made the change using Enterprise Mangler. EM tends to make these changes not with a simple alter table add column, but with a drop and re-create of the table. You can try running DBCC CHECKIDENT(table) on the table, which should reset the identity column nicely.|||That's interesting. I am going to test that on my test system now to see what happens.

Thanks!

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
atte, Hernn
You need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernn
>

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
atte, HernnYou need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernn
>

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
--
atte, HernánYou need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernán
>

I/O Blob Through TableAdapter

What's a good way to work with Blobs and TableAdapters, in terms of declaring compatible column types in SQL Server and DataTable fields?

A byte array would seem to be the best solution. To my knowledge it will be the only one that works too.

|||Thanks for the response. I'll use that to focus my "R&D" around this point.

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>sql

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in Advance
Hi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegr oups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

I want to display only my numeric values in SQL

I have a query that it has a lot of filters to avoid letters or special characters, I was wondering If there is a way to filter the column with one statement to displays only the numbers

here is the sample code

select /*top 200*/

v.arpnumber, o.last, o.First, v.coOwnerFn, v.coOwnerLn, o.address1, o.address2, o.city, o.zip, o.state,o.province,o.country, o.datecreated, o.login, o.pwd

from ownersdb.dbo.AffinityRewardsMembers o ,dbo.contacts v

where system = 'lead'

and o.armembernum = v.arpnumber

and v.contacttype = 'owner'

and v.arpnumber is not null

and v.arpnumber <> ''

and v.arpnumber not between 'a%' and 'z%'

and v.arpnumber not between '%a%' and '%z%'

and v.arpnumber not like '% %'

and v.arpnumber not like '%pk%'

and v.arpnumber not like '%pb%'

and v.arpnumber not like '%z%'

and v.arpnumber not like '%w%'

and o.ownerid is not null

and v.officeid in ('37','32','30','31','20','22','23')

and o.ReportPrinted = 0

and o.login is not null

and o.pwd is not null

/*and city is not null

and city <> ''*/

order by v.ln

the column I'm trying is arpNumber

instead of using all these filter I just want to put

i.e

v.arpnumber is numeric

Any suggestions ?

Thanks in advance

One way you can do that is by writing a user defined function called isNumeric using .net CLR with regular expressions.

Then you can check the arpNumber if it is numeric or not in one word.

|||

"Isnumeric" also has issues. Give a look here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Despite this, a quick and dirty work-around that I sometimes use (that is NOT foolproof!) is to check

isNumeric (@.aStringToBeTested + 'D2') = 1

This eliminates some of the problems -- it eliminates the money "marker" and it eliminates many "float" datatype problems. However, please give the writeup a look. (and like Dale says, give the IsReallyNumeric function a look.)

|||

Try using the isReallyNumeric function found here:

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

(Thanks Arnie)

Add the function to your database then

replace your pattern tests with

Code Snippet

and dbo.isReallyNumeric(v.arpnumber)=1

|||

I guess I am a bit confused.

Do you want ot ONLY RETRIEVE rows where [v.arpnumber] contains only numbers,
OR,

Do you want to DISPLAY the [v.arpnumber] field, stripping out any non-numeric characters?

If it the first, then DaleJ's suggestion is good and should work for you, otherwise you will need a function similar to the following:

Code Snippet

CREATE FUNCTION dbo.fnNumbersOnly
( @.InParam varchar(500) )
RETURNS varchar(500)
AS
BEGIN
IF patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
WHILE patindex( '%[^0-9]%', @.InParam ) > 0
BEGIN
SET @.InParam = Stuff( @.InParam, patindex( '%[^0-9]%', @.InParam), 1, '' )
END
END
RETURN @.InParam
END
GO

-- Test
DECLARE @.TestParam varchar(50)
SET @.TestParam = 'ab3452SDF094c&h*¤,1r340.95849%eew#y/'
SELECT dbo.fnNumbersOnly( @.TestParam )

--
3452094134095849

Usage:

SELECT TOP 200
arpnumber = dbo.fnNumbersOnly( v.arpnumber ),
o.last,
etc.,

|||

as a matter of fact i'm trying to retrieve data that will update another store and these values most be numeric, I'll check that func that I read that seems to be a good idea.

sql

I want to add two text columns

Hi all,
I have a two text columns in my table with more than 100,000 rows.
I want to create a third text column with the data from text column 1 + text
column 2.
Is there an easy way to concatinate two text fields?
Thanks
RajuNo, there is no easy way. Why would you want to do this? Is your SQL
statement too long when you list both columns? This seems like a weird
requirement to me. Usually you use separate TEXT columns because you need
them separate. Otherwise, most applications could have just used a single
text column in the first place.
If you have less than 8000 characters in each column, you could use a view,
and say:
SELECT
..,
ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
+ CONVERT(VARCHAR(8000), textCol2)
FROM
..
Then, you don't have to change the underlying table, or any of the
procedures / apps that insert/update the data. (Or, better yet, if you
don't need TEXT, change the columns to VARCHAR.)
Otherwise, the best way would probably be to add the 3rd column, use an
external app to loop through, concatenate, and update the new column, and
then delete the first two columns. (An external app will be much more
friendly with joining the two values than SQL Server will be internally.)
A
"Raju" <npraju1@.hotmail.com> wrote in message
news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a two text columns in my table with more than 100,000 rows.
> I want to create a third text column with the data from text column 1 +
> text
> column 2.
> Is there an easy way to concatinate two text fields?
> Thanks
> Raju
>|||Aaron,
Thank you for your response.
Actually we have legacy application, which has summary in one text column
and description in one text column.
Our new application has only one column, now my job is to get those two
columns into one new column in our new application.
Hope this helps.
BTW
Unfortunately I can't use your solution as both the columns have huge data,
more than 8000 bytes.
Thanks
Raju
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWjN6eCAGHA.1256@.TK2MSFTNGP15.phx.gbl...
> No, there is no easy way. Why would you want to do this? Is your SQL
> statement too long when you list both columns? This seems like a weird
> requirement to me. Usually you use separate TEXT columns because you need
> them separate. Otherwise, most applications could have just used a single
> text column in the first place.
> If you have less than 8000 characters in each column, you could use a
view,
> and say:
> SELECT
> ...,
> ConcatenatedColumn = CONVERT(VARCHAR(8000), textCol1)
> + CONVERT(VARCHAR(8000), textCol2)
> FROM
> ...
> Then, you don't have to change the underlying table, or any of the
> procedures / apps that insert/update the data. (Or, better yet, if you
> don't need TEXT, change the columns to VARCHAR.)
> Otherwise, the best way would probably be to add the 3rd column, use an
> external app to loop through, concatenate, and update the new column, and
> then delete the first two columns. (An external app will be much more
> friendly with joining the two values than SQL Server will be internally.)
> A
>
> "Raju" <npraju1@.hotmail.com> wrote in message
> news:%23mM%237ZCAGHA.2040@.TK2MSFTNGP14.phx.gbl...
>

I tried to sign on to my web site using the ASPnet_membership table and got a error with m

This is the error that I got. Is it a Resevered Table name or something

Invalid column name 'LastLoginDate'.
Invalid column name 'LastLoginDate'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'LastLoginDate'.
Invalid column name 'LastLoginDate'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

What do you mean by "I tried to sign on to my web site using the ASPnet_membership table and got a error with my table."? HOW were you trying to sign in? Using a control? By entering a record into the table?

LastLoginDate IS one of the fields in the aspnet_Membership table.

Can you post the stack trace?

I need to find total for rows with a certain value: how?

I have a table that contains a column that collects discreet data (1 for on
and 0 for off, just those two values) and a date column which is a timestamp
containing a date and time. There are other columns in this table but they
are unimportant in this discussion.
A process inserts rows into this table about every second or so, depending
on the state of an object.
It has been requested of me that I prepare a report. Among other items,
this report must show the total time an object is turned on. In other
words, for every row that is set to 1, I need to add up the time and let the
users know how long that item was set to on for a given period of time, say
24 hours i.e. how long was that object in an on state during the last 24
hours.
I consider myself to be pretty handy at SQL but I must confess I'm at a loss
to come up with a query for this.
Any ideas and examples would be greatly appreciated?
Thanks in advance,
Chris SmithOh I neglected one important piece...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||jeez, typo... And this produces total Millliseconds, Change the ms inside
the datediff function to whatever you want it to produce if milliseconds is
not what you want:
sec, minute, hour, day, etc...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTimeMs
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LogDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Where On.OnFlag = 1
And Off.OnFlag = 0
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||I assume that the table also contains an ObjecID - to identofy WHICH Object
is being Tuened On Or OFF...
Select ObjectID, Sum(DateDiff(ms, On.LogDT, Off.LogDT)) OnTime
From LogTable On
Join LogTable Off
On Off.ObjectID = On.ObjectID
And Off.LogDT =
(Select Min(LofDT)
From LogTable
Where ObjectID = On.ObjectID
And LogDT > On.LogDT)
Group By ObjectID
"someone" wrote:

> I have a table that contains a column that collects discreet data (1 for o
n
> and 0 for off, just those two values) and a date column which is a timesta
mp
> containing a date and time. There are other columns in this table but the
y
> are unimportant in this discussion.
> A process inserts rows into this table about every second or so, depending
> on the state of an object.
> It has been requested of me that I prepare a report. Among other items,
> this report must show the total time an object is turned on. In other
> words, for every row that is set to 1, I need to add up the time and let t
he
> users know how long that item was set to on for a given period of time, sa
y
> 24 hours i.e. how long was that object in an on state during the last 24
> hours.
> I consider myself to be pretty handy at SQL but I must confess I'm at a lo
ss
> to come up with a query for this.
> Any ideas and examples would be greatly appreciated?
> Thanks in advance,
> Chris Smith
>
>|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of JOIN to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the immediate NEXT
time it entered the OFF state. Likewise, if it is the end time that is
stored, I need to find the immediate PREVIOUS time it left the OFF
state.
Another thing you will have to consider, though this has to do with
data integrity, how do u ensure no overlaps occur in the records (such
that your data says at a given point in time, the object was both ON
and OFF)? Do you allow time gaps in which you can't tell whether the
object was ON or OFF? if you don't how do u ensure there are no gaps?
If you do, how do you interpret the time where there are no records?
Please clarify...|||It might be helpful for you to provide sample data, especially about
this timestamp field. When a row is inserted with the on/off flag set
to ON, what value is in the timestamp column? The way I see it, there
are 3 options: -
1. The time the object entered the ON state only
2. The time the object left the ON state only
3. Both the time the objected entered the ON state + the time it left
or the duration it was in this state.
The 3rd option is easy because all you need to do is sum the duration
(or calculate it from the Left - Entered Time and sum) for each state
in a given time period.
The 1st and 2nd options are a bit tough since you have to form a sort
of join to find the related state from which the object toggled from or
to. e.g. if I store the time the object entered the ON state, to find
out how long it stayed in this state, I need to find the NEXT time it
entered the OFF state. Likewise, if it is the end time that is stored,
I need to find the PREVIOUS time it left the OFF state.
Another thing you will have to consider, though this has to do with
integrity, how do u ensure no overlaps occur in the records? Do you
allow time gaps in which you can't tell whether the object was ON or
OFF? if you don't how do u ensure there are no gaps? If you do, how do
you interpret the time where there are no records?
Please clarify...|||Hi Sienko,
I'll try to answer your questions as best as I can.
First of all, it should be noted this isn't a typical database. This is
actually a database accessed through MS SQL 2000 called InSQL. InSQL allows
you to store real time data more efficiently than SQL Server while allowing
for more transactions per second and storing the data in a manner that
allows for smaller files on the harddrive than what SQL Server itself would
allow. InSQL itself is actually made up of extension tables to SQL Server
2000. Along with this, InSQL provides other things such as providing a
discreete data type which allows you to store values that tells you if a
device is on or off where 1=on/open and 0=off/close. This probably isn't
important for you to know but it might be. For the most part, T-SQL is
still valid so any solution you can help me find should still work.
A typical row will include a column for a tagname, a discreete value column
(contains a value of 1 or 0, that is all as far as I know of), and a
timestamp which is just a datetime column (this column is equal to the time
the discreete value was retrieved from a device, not the time the row was
created). There are other columns but they aren't important to this
discussion as far as I know of. An example row might look like:
tagname_here -- discreete value -- datetime
There are rows inserted for each tag about every second or so. We'll say a
row is inserted each second to keep this simple. The rows will be the same,
just the fact the discreete value is different along with the time. For
example, a valve maybe open at the time a sensor takes a reading so this
data is retrieved (the state of the valve along with the datetime the
reading was taken) and inserted into a table (discreetehistory is the name
of the table I think, we'll use that for the sake of this conversation). As
long as the valve is open, a row will be inserted into this table where that
row will have a 1 in the discreete column and the datetime that value was
taken. When the valve is closed, rows will be inserted into the table where
the discreete value is now 0 and it has a datetime with it. Again, as long
as the valve is closed, rows will be inserted into the table with a
discreete value of 0 along with it's datetime. In other words, we have a
process that is looking at equipment 24/7/365. This process determines the
state of devices and inserts rows into a table every second or so.
Everytime a row is written, that row represents the state of the device and
a datetime is stored in that row to let us know when the sensor reading was
taken.
I don't think the table will allow null values nor do I believe a null value
will ever occur but am not certain. There is a transition period between
opening and closing valves. After all, a valve doesn't instantly open or
close. Depending on the size of size valve, it might take 1 to 5 seconds to
switch states. But, I don't believe this transition state is being
recorded. I wish I was at work answering this. I could tell you for sure
then.
Again, the problem is I need a query where I can report how long a valve was
left open in the last two hours. In other words, for a time from 12:00PM to
2:00 PM, the valve might be open at first but close at 12:30. Then, the
valve might open at 12:40. Then, the valve might be close at 1:00PM and
then open again at 1:10 PM. In other words, the valve was opened and
closeded multiple times during this two hour block. In total, the valve was
open 1hr and 40 minutes during this two hour block with the valve being
closed for 20 minutes. I need a query that will read this table and
everytime the valve is open which is represented with a discreete value of 1
(where 1 = open), then it will sum up all the times and report this to me.
I thought I was pretty good with T-SQL but I've never done anything like
this before when it comes to time. I must admit I don't have the slightest
idea how to proceed with this one.
Any thoughts would be greatly appreciated. If you need more information,
please let me know.
Thanks for trying to help me out. I apologize for taking a while to respond
to you but I was busy and simply forgot about this.
Thanks again!
Chris Smith
"sienko" <sienko@.gmail.com> wrote in message
news:1112174062.024672.131620@.g14g2000cwa.googlegroups.com...
> It might be helpful for you to provide sample data, especially about
> this timestamp field. When a row is inserted with the on/off flag set
> to ON, what value is in the timestamp column? The way I see it, there
> are 3 options: -
> 1. The time the object entered the ON state only
> 2. The time the object left the ON state only
> 3. Both the time the objected entered the ON state + the time it left
> or the duration it was in this state.
> The 3rd option is easy because all you need to do is sum the duration
> (or calculate it from the Left - Entered Time and sum) for each state
> in a given time period.
> The 1st and 2nd options are a bit tough since you have to form a sort
> of JOIN to find the related state from which the object toggled from or
> to. e.g. if I store the time the object entered the ON state, to find
> out how long it stayed in this state, I need to find the immediate NEXT
> time it entered the OFF state. Likewise, if it is the end time that is
> stored, I need to find the immediate PREVIOUS time it left the OFF
> state.
> Another thing you will have to consider, though this has to do with
> data integrity, how do u ensure no overlaps occur in the records (such
> that your data says at a given point in time, the object was both ON
> and OFF)? Do you allow time gaps in which you can't tell whether the
> object was ON or OFF? if you don't how do u ensure there are no gaps?
> If you do, how do you interpret the time where there are no records?
> Please clarify...
>|||You usually model time as durations, so you would have
CREATE TABLE Events
(event_id CHAR(10) NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_time DATETIME, -- null means current
. );|||On Sat, 2 Apr 2005 12:49:01 -0500, someone wrote:
-- Crosspost to non-existant group removed --
(snip description)
>I thought I was pretty good with T-SQL but I've never done anything like
>this before when it comes to time. I must admit I don't have the slightest
>idea how to proceed with this one.
>Any thoughts would be greatly appreciated. If you need more information,
>please let me know.
Hi Chris,
I think the best way to approach this problem is to start with a view to
reduce the load of imported data to just the relevant things: periods
without status change, with beginning and ending datetime.
CREATE VIEW Periods
AS
SELECT start.Status,
start.TheDatetime AS StartDT,
MAX(sameperiod.TheDatetime) AS EndDT
FROM RawData AS start
INNER JOIN RawData AS sameperiod
ON sameperiod.Status = start.Status
AND sameperiod.TheDatetime >= start.TheDatetime
AND NOT EXISTS
(SELECT *
FROM RawDate AS beetween -- deliberate misspelling: reserved word
WHERE beetween.TheDatetime > start.TheDatetime
AND beetween.TheDatetime < sameperiod.TheDatetime
AND beetween.Status <> start.Status)
WHERE NOT EXISTS
(SELECT *
FROM RawDate AS previous
WHERE previous.TheDatetime =
(SELECT MAX(TheDatetime)
FROM RawData
WHERE TheDatetime < start.TheDatetime)
AND previous.Status = start.Status)
GROUP BY start.Status, start.TheDatetime
With this query, your report becomes easy:
DECLARE @.StartReport smalldatetime
DECLARE @.EndReport smalldatetime
SET @.StartReport = '2005-04-02T12:00:00'
SET @.EndReport = '2005-04-02T14:00:00'
SELECT SUM(DATEDIFF(minute,
CASE WHEN StartDT < @.StartReport
THEN @.StartReport ELSE StartDT END,
CASE WHEN EndDT < @.EndReport
THEN @.EndReport ELSE EndDT END))
FROM Periods
WHERE StartDT < @.EndReport
END EndDT > @.BeginReport
I was not able to test the query and view above, since you have not
posted the CREATE TABLE and INSERT statements needed to create a test
database on my server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The thing is I'm not creating a table with multiple columns for dates. A
table is already present with one datecolumn only. I need to find all rows
within a timespan that have a value equal to 1 in the discreete value
column. Once I find all the rows, I need to add them all somehow such that
it will tell me the total time all the rows were set to a 1 in this column.
Any ideas on that?
Thanks!
Chris Smith
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112464694.412138.243320@.o13g2000cwo.googlegroups.com...
> You usually model time as durations, so you would have
> CREATE TABLE Events
> (event_id CHAR(10) NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_time DATETIME, -- null means current
> .. );
>

I need to create index on a column containing sequential numbers with at least s

I need to create index on a column containing sequential numbers with at least seven significant digits. Which index would be best suited for this task?Not enough info:

What are your inserts versus queries?

Do you have a PK?

Is there already a clustered index on the table?

how will the column be updated, inserted and also deleted by?

I need identity on updatable partitioned view

Ok, already read that is not allowed to have identity column in any member table of a updateable partitioned view.

But I need an identity column. Is there any other way or ide how to solve this problem?

This is one of the situation where using a column with a uniqueidentifier datatype, set to a default of newid() is the 'right' solution.|||It does not work...|||

I have to disagree with you on that!

What are you attempting to accomplish, and what steps have you taken so far.

Please include the TABLE and VIEW DDL.

|||

create table bileta_te_lozura_2007_01

(id int not null,

databiletes datetime not null check (databiletes>=cast('20070101' as datetime) and databiletes<cast('20070201' as datetime)),

constraint [pkjan2007] primary key clustered ([id],[databiletes]) with fillfactor=50 on [primary]

)

create table bileta_te_lozura_2007_02

(id int not null,

databiletes datetime not null check (databiletes>=cast('20070201' as datetime) and databiletes<cast('20070301' as datetime)),

constraint [pkshk2007] primary key clustered ([id],[databiletes]) with fillfactor=50 on [primary]

)

create view bileta_te_lozura

as

select * from bileta_te_lozura_2007_01

union all

select * from bileta_te_lozura_2007_02

I need ID to be an unique ID

For the whole view not only for one table!

|||

Try this:

CREATE TABLE bileta_te_lozura_2007_01
( ID uniqueidentifier DEFAULT newid() NOT NULL,
DataBiletes datetime NOT NULL CHECK ( DataBiletes >= cast( '20070101' AS datetime ) AND DataBiletes < cast( '20070201' as datetime )),
CONSTRAINT [pkjan2007] PRIMARY KEY CLUSTERED ([ID],[DataBiletes]) WITH FILLFACTOR = 50 ON [primary]
)

CREATE TABLE bileta_te_lozura_2007_02
( ID uniqueidentifier DEFAULT newid() NOT NULL,
DataBiletes datetime NOT NULL CHECK ( DataBiletes >= cast( '20070201' AS datetime ) AND DataBiletes < cast( '20070301' as datetime )),
CONSTRAINT [pkshk2007] PRIMARY KEY CLUSTERED ([ID],[DataBiletes]) WITH FILLFACTOR = 50 ON [primary]
)

CREATE VIEW dbo.bileta_te_lozura
AS
SELECT * FROM bileta_te_lozura_2007_01
UNION ALL
SELECT * FROM bileta_te_lozura_2007_02

|||

That will create an unique identifier column, not an integer column. Looking that all this is done because table can be be very large i'm affraid for performance issues using rowguid things...

I mean, if i query as follows:

select aa,bb,cc from bileta_te_lozura where id='233acf-sdffd-sdfsdfs-sdfsf-sdfssss'

have the same performance with:

select aa,bb,cc from bileta_te_lozura where id=1167876

Every table has some million rows.

Anyway... this also does not work. It report error during insert

Partitioned view 'renis.dbo.bileta_te_lozura' is not updatable because table '[renis].[dbo].[bileta_te_lozura_2007_01]' has a DEFAULT constraint.

|||

Ok Regis,

I'm starting to get a better idea about your situation. I think that if you do not have a primary key for the data, and that if you need to create a surrogate key, and since IDENTITY is not available in this situation, you may have to create a 'Table of Numbers' and get/increment the new value before each INSERT. One option would be having a Staging table, where the IDENTITY column does exist, and then immediately (after the IDENTITY value is assigned) move the data to your partitioned tables.

I also caution you that having a new table for every month will rapidly become burdensome. If you are using SQL 2005, you may wish to investigate table partitioning -and if you are not using SQL 2005, table partitioning may be the reason to upgrade. See Books Online, topic: [Partitioned Tables]

Distributed Partitioned Views (DPV) are best suited for multiple servers.

|||I tried and resolved this problem using partition tables... With partition views this was impossible, (not impossible, but just too long and complicated, which in generall means a lot of bugs)

I need help with Transactional Replication

I am just beginning with transactional replication, and I have a publisher
with a primary key on the identity column (UID), some data, then an adddate
(varchar 8) column iwth ccyymmdd data in it. I want to replicate this table
to a server based on the adddate value (all records pushed since a given
adddate). My replication attempts seem to only want to work off of the
primary key column. How do I get it to work off of the adddate column, or do
I even really want to?
Thank you for any help in advance
The publisher (and subscriber) tables look like this:
create table temp (
uid bigint,
data_1 varchar(20),
adddate varchar(8)
)
Carl,
TR will only replicate the changes to this table since the last
synchronization. If new records have been added, they'll automatically be
picked up. With this in mind you can probably forget about filtering the
dddate column, unless I am misunderstanding you.
rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||ok, perhaps I have a different problem, then. When I update the publisher by
inserting numerous new rows, I manually run the distributer and it claims
that there are no rows to replicate. How can I get the distributor to see the
new rows?
"Paul Ibison" wrote:

> Carl,
> TR will only replicate the changes to this table since the last
> synchronization. If new records have been added, they'll automatically be
> picked up. With this in mind you can probably forget about filtering the
> dddate column, unless I am misunderstanding you.
> rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Carl,
are you using a filter on the publication?
Is the log reader agent running?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There are no filters being used, and the log reader agent shows no errors.
"Paul Ibison" wrote:

> Carl,
> are you using a filter on the publication?
> Is the log reader agent running?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Carl,
make a change to a row then run sp_browsereplcmds in the
distribution database to see if the rows are reaching
there.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

I need help ASAP with SSRS Date Expression

I have a report which has a date like 01/05/2007 for a bill date in on Column I have todays date in another column I have a tird colum which need the total number of days from date of the billed date. I need to create and expression which will take the current date and subtract the number of days from todays date and give a total number of days. How do I get thew current date in the expression.

the billed date expression is =FormatDateTime(Today()) - FormatDateTime(Fields!BILLDATE.value,2) I can not get this to work

Any help would be appreciated

Hey,

you can try something like this:

privateint GetDaysBetweenDates(DateTime firstDate, DateTime secondDate)
{
return secondDate.Subtract(firstDate).Days;
}

Hope this helps.

i need Column names !

Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!Originally posted by superj
Hi, i need a query to have the columns names !!
If you have another solution, let me know !!

superj !!! I kind of like Bob, Carol, Ted, and Alice myself. What column names do you like?

-PatP|||I like them blue. Databases I like yellow.|||Originally posted by rdjabarov
I like them blue. Databases I like yellow. So, if you put those columns into a database, does the whole thing turn green?

-PatP|||Hey, that would be cool!|||SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'authors'
> ORDER BY ORDINAL_POSITION|||select colid, name from syscolumns where object_name(id) = 'authors' order by 1|||Originally posted by rdjabarov
I like them blue. Databases I like yellow.

Don't you read Dilbert?

Muave is the fastest db color|||whatever you do, dont get red
cops look for that color.

i myself , am currently configuring a Puce database with various pastel colored tables and some chince doilys.

yes i work for martha stewart living|||Why not :
EXEC sp_columns @.table_name = 'customers'|||okay i'll bite

exec SP_Help tablename

will give you everything about the table including all column names.|||But that gives whole lot of information where Originator requires only column information for a table.

I feel using INFORMATION SCHEMA VIEWS or SP_COLUMNS will suffice.
Originally posted by Ruprect
okay i'll bite

exec SP_Help tablename

will give you everything about the table including all column names.|||i was just jumpin' in
info_schema are just fine

or

how about

sp_columns

Returns column information for the specified tables or views that can be queried in the current environment.