I want to write an SQL statement which returns matching values but ignores the first 2 dig

I want to write a statement something like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)

But i want the search to ignore the first 2 digits so that it will return e.g

10/11/1234, 09/11/1234 so that it's only matching the last part

Any Help Would be greatly appreciated Thanks

try use Substring in SQL statement|||

I'm not sure if you want this to just a straight up query or something dynamic

If you want it to be passed into a query, you can do
string criteria = "11/11/1234";
"SELECT ... (File_No = " + criteria.Remove( 0, 2) + ");

Use Parameterized query and not the exact example above.

If you want just a straight up query use LIKE
SELECT... (File_No LIKE '%/11/1234')

|||

You can use something like this:

DECLARE

@.lcModifiedIDvarchar(10),

@.liMaxFiedlLength

asint,

@.liStartPoint

asint

SET

@.liMaxFiedlLength= 100

SET

@.liStartPoint= 4

SET

@.lcModifiedID=substring('11/11/1234/',@.liStartPoint,@.liMaxFiedlLength)

print

@.lcModifiedID

SELECT

Add_Date, File_NoFROM(SELECT'12/11/1234/' File_No,'aa'Add_date)aaWHEREsubstring(File_No,@.liStartPoint,@.liMaxFiedlLength)= @.lcModifiedID

Thanks

JPazgier

|||

I Have a textBox named TextFile which is where the user enters the file number which will be in the format of 11/11/1234 and then there is a button with an on click event to trigger my SQL query

I have an SqlDataAdapter with the first parameter set as @.FileNo

I want the query based entirely on the the value of textBox

I am currently doing it like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = @.File_No)

But this only returns exact matches and as i say i need to return values that ignore the first 2 digits contained in @.File_No

Thanks for such a quick response

|||

I am pretty sure you can change "File_No = @.File_No" to "File_No LIKE @.File_No"

Then when you declare the value of the parameter:
.Value = "%" + TextFile.Text.Remove( 0, 2);

|||

Yes, you are right maybe it will work but remember that LIKE structure is very slow and designed for another purposes.

Thanks

JPazgier

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 think simple but tough for me Help

Hello Genuis

friend i had three columns in my crsytal report that shows values as
a = 1
b = 0
c shows nothing not 0 or some value as in database value of c = null
now i want
a+b+c using a formula
but it is not showing the output as null is their
please help out how can i get the result

as i want a+b+c = 1 should be output

if bychance the value of a= 1
b =1
c=0
then output s coming for sure
i.e 2

Hope u got my problem

How to add all the columns

With Best Regards
Rubyhi,
use the formula and palce ur fields in place of a,b and c. u will find the solution.
(IF ISNULL(a) THEN 0 ELSE a) + (IF ISNULL (b) THEN 0 ELSE b) +( IF ISNULL (c) THEN 0 ELSE c)
remeber in pryers.
online available at habibhussain82@.gmail.com|||Test if C is null before trying to add it. e.g.
if isnull({table.C}) then a+b
else a+b+c

I see where you are going...

I've got a reasonably efficient query that gives me a count of the top 20 daily values in my database. Now I'd like to figure out the daily total of top 20 values. Then analyze this information to work out the average, min, max and standard deviation of the daily total of top 20 values.

My best effort is horribly slow - does anyone have a better idea how to do this?

Thanks!

The schema of the database it accesses:

create view eventView (timeStr, msec, host, process, dbName, point,
description, rtu, groupName, message, type,
sevInt, time)
as
select dateadd(second,time+60*offset,'01/01/70'), msec, host, process,
dbName, ptName, description, rtuName, groupName,
message, type, sevInt, time
from summary

CREATE TABLE [summary] (
[msrepl_tran_version] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_Summary_GUID] DEFAULT (newid()),
[time] [GMTtime] NOT NULL ,
[msec] [int] NULL ,
[offset] [GMToffset] NOT NULL ,
[type] [nameType] NULL ,
[host] [nameType] NULL ,
[process] [nameType] NULL ,
[dbName] [nameType] NULL ,
[ptName] [nameType] NULL ,
[description] [descType] NULL ,
[rtuName] [nameType] NULL ,
[groupName] [nameType] NULL ,
[message] [msgType] NOT NULL ,
[fgInt] [tinyint] NULL ,
[sevInt] [tinyint] NULL ,
[key1] [int] NULL ,
[key2] [int] NULL ,
[spooler] [tinyint] NULL
) ON [PRIMARY]
GO

My Top 20 query:

SELECT TOP 20 count (*) as "Number of Alarms", [point], [description]

FROM [event].[dbo].[eventView]
WHERE ([timestr] < left(getdate(),11) and [timestr] >= left(getdate() - 1,11))
GROUP BY point, description HAVING count(*) > 1
ORDER BY "Number of Alarms" desc

And the messy, slow meta query:

declare @.myDay datetime
declare @.begDay datetime

declare @.myTable
table(Alarms int, Point varchar(250), Description varchar(250), Before datetime, After datetime)

declare @.myDaily
table(Date datetime, Alarms int)

select @.myDay = left(getdate(),11)

select @.begDay = left (convert(datetime, '12/01/2006'), 11)

while @.begDay <= @.myDay
begin

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description],@.begDay
FROM [event].[dbo].[eventView]
where ([timestr] < @.begDay and [timestr] >= dateadd(day,-1,@.begDay))
group by point, description
having count(*) > 1
order by "Number of Alarms" desc

select @.begDay = dateadd(day,1,@.begDay)

end

--

insert into @.myDaily
select After as "Date", sum(Alarms) as "Alarms"
from @.myTable
group by After
--

select count(Alarms) as "Count", avg(Alarms) as "Average", max(Alarms) as "Maximum", min(Alarms) as "Minimum", stdev (Alarms) as "Standard Deviation"
from @.myDaily

As I know you can improve performance if you make some change in these places.
1. avoid using function in your where clause.
2. create index in summary table.
3. I am not sure why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView? You can create another column which store time as your local timezone.


|||

I'm still learning SQL, so I don't understand all of your suggestions:

1. Avoid using function in where clause

Are you referring to the dateadd? How else can I limit the data to daily information?

2. create index in summary table.

Sorry, can't do that. I don't have control over the summary table - it's provided to my company by the owner of the software.

3. why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView

Because the software mfg stores data in the summary table in the format of "UTC seconds ". Once again, I cannot change the summary table.

|||If I were you, I won't use dateadd(....) in eventView. That is we still use UTC timestamp in eventView.

you can create @.begDay_UTC and @.PreviousDay_UTC and use them in this part of code in your where clause.
([timestr] < @.begDay_UTC and [timestr] >= @.PreviousDay_UTC)

If you can find out index Summary table used, that will help us find out how to improve performance.

Also, the estimated executions in SQL Server Management Studio will help us find out which part of code is the most expensive.|||

You can replace that while loop with a single query that will greatly improve the performance, here it is (I think, haven't tested but should be very close)

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description], [timestr], dateadd(day, 1, [timestr])
FROM [event].[dbo].[eventView]
where [message] not like '%NORMAL state%' and
[message] not like '%restored - normal%' and
[message] not like '%communication%restored%' and
[message] not like '%PLM - NORMAL%' and
[type] = 'alarm' and
[timestr] between @.begDay and @.myDay
group by [timestr], dateadd(day, 1, [timestr]), point, description, dbName
having count(*) > 1
order by "Number of Alarms" desc

|||

Agree this makes more sense and is easier to read. I've tested this and it does not make much difference in the performance.

I'm going to use your suggestion - much cleaner and easier to understand! Thanks...

|||

Think you are trying to grab the daily information using the GROUP BY.

This doesn't work for me because I'm trying to get a set of n days ( = 265 days on my system) daily top 20 values; this query only returns 20 values. I want 265 x 20 values.

|||

Which version of SQL Server are you using? You can simplify the WHILE loop in SQL Server 2005 using the APPLY operator. In SQL Server 2000, there is no easy way to write a single query - you have to do some sort of procedural loop which might be the fastest way. See below for an example in SQL Server 2005:

-- Top 2 order details based on quantity for each order:

select *
from Orders as o
cross apply (
select top 2 *
from "Order Details" as od
where od.OrderID = o.OrderID
order by od.Quantity
) as o2

|||

Our system runs MS SQL Server 2000. And since its the back end providing data archiving for our turnkey system, we will be using this for years to come.

When you say "procedural loop" that makes me think the BEGIN loop is the only way to do this job. Too bad 8-(

I Need Your Help,About SQL2000 DataInsert

I want to insert some data into my SQLServer,For example：insert into xcjl(zch,xcsj) values('027741',getdate())

but ,the "zch" maybe have a few data,I want to insert these step by step,how can I do?

Can you explain in more detail what you are trying to accomplish?

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 compare two string values and give confidence score on how much they are similar.How d

I need to compare two strings and get a confidence score on how similar they are. I have around half a million of such strings to compare.While Fuzzy look up sample codes I used compare a string against all the records in the reference table,my comparison requirement is limited to two given strings.
Can some body please help me on how to do this using fuzzy look up?
Thank you very much!Do you want to compare all the source strings with one string in the

lookup table? If that is the case then can you not use a view of the

one reference column as the lookup and not the table object? If I have

misinterpreted the requirement then please let me know.

Allan

"Ashraj@.discussions.microsoft.com"

wrote in message

news:046a46c8-8fa8-4399-9417-24493b01d9d0@.discussions.microsoft.com:

> I need to compare two strings and get a confidence score on how similar

> they are. I have around half a million of such strings to compare.While

> Fuzzy look up sample codes I used compare a string against all the

> records in the reference table,my comparison requirement is limited to

> two given strings.

>

>

> Can some body please help me on how to do this using fuzzy look up?

> Thank you very much!|||Hi,
Thanks much for your input.Sorry I wasn't clear.

Actually, I have two files . FILEA and FILEB. They have have similar structure with two fields Accountnumber(unique key) and Address(String value) . FileB is the reference file.FileA is input file.Both have same number of records with matching key fields.

For each account number in FILEA,the Address string has to be compared to address string in FILEB with the same account number.

For example Address in first row "45 Sunset blvd" with acct number 111 ,needs to be compared with address of account 111 in FILEB which is "456 Sunset Blvd " and a confidance and similarity score needs to be registered .

The Key field account number is only for linking purpose which may not be required if I have a way to pass two strings and get the comparison results.

FileA

Acct

Address

111

45 Sunset blvd

555

236 technical str

999

448 madera

FileB

Acct

Address

111

456 Sunset Blvd

555

236 Technical st.

999

449 Madera

Looking forward to any help that can be provided.Please feel free to ask for any clarifications.Thanks much|||Ok So I have this

CREATE TABLE AccountsInput(AcctNUm int, Street varchar(50))

CREATE TABLE AccountsLookup(AcctNUm int, Street varchar(50))

Insert AccountsInput VALUES(111,'45 Sunset Boulevard')

Insert AccountsInput VALUES(555,'236 technical str')

Insert AccountsInput VALUES(999,'448 Madeira')

Insert AccountsLookup VALUES(111,'45 Sunset BLVD')

Insert AccountsLookup VALUES(555,'236 technical st.')

Insert AccountsLookup VALUES(999,'448 Madera')

I want to compare both the acct# and the address to get matches or not.

I configure the fuzzy lookup to match on both the attributes and I get

this.

AcctNUmStreetLooked Up account NumLooked Up Street

Name_Similarity_Confidence

11145 Sunset Boulevard11145 Sunset BLVD0.74537040.9875

555236 technical str555236 technical st.0.88491890.9875

999448 Madeira999448 Madera0.92811570.9875

Is this not what you want ?

Allan

"Ashraj@.discussions.microsoft.com"

wrote in message

news:74b34ed7-c2df-4569-b8bd-1d5e4ed19b4f@.discussions.microsoft.com:

> Hi,

> Thanks much for your input.Sorry I wasn't clear.

>

> Actually, I have two files . FILEA and FILEB. They have have similar

> structure with two fields Accountnumber(unique key) and Address(String

> value) . FileB is the reference file.FileA is input file.Both have same

> number of records with matching key fields.

>

> For each account number in FILEA,the Address string has to be compared

> to address string in FILEB with the same account number.

>

>

> For example Address in first row "45 Sunset blvd" with acct number 111

> ,needs to be compared with address of account 111 in FILEB which is

> "456 Sunset Blvd " and a confidance and similarity score needs to be

> registered .

>

> The Key field account number is only for linking purpose which may not

> be required if I have a way to pass two strings and get the comparison

> results.

>

>

>

> FileA

>

>

>

> Acct

>

> Address

>

> 111

>

> 45 Sunset blvd

>

> 555

>

> 236 technical str

>

> 999

>

> 448 madera

>

>

>

>

>

> FileB

>

>

>

> Acct

>

> Address

>

> 111

>

> 456 Sunset Blvd

>

> 555

>

> 236 Technical st.

>

> 999

>

> 449 Madera

>

>

>

> Looking forward to any help that can be provided.Please feel free to ask

> for any clarifications.Thanks much|||Thanks Alan. It looks almost perfect .
I just wanted to confirm one more thing before I say this is what I exactly want.

For our comparison, I have the following basic assumption that
a) account numbers are not corrupt and all account numbers in FILEA are in FILEB and viceversa.
b) FILEA and FILEB are unique keyed with account number

As you had detailed, I want address of account number 111 in FILEA to be compared to address of account number 111 ONLY in FILEB.

The address comparison should be made ONLY between the records that have same account number in FILEA and FILEB .Comparison and statistics should not be made between address that don't have common account number.
For example, Address of account number 111 SHOULD NOT be compared with address of account number 555 and 999.

In our example for first record it SHOULD NOT execute 3 searches and give the following.
111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875
--
111 45 Sunset Boulevard 555 236 technical st. 0.1xxxxxx 0.xxxxx

111 45 Sunset Boulevard 999 448 Madera 0.9281157 0.9875

It should do ONLY do comparison one time and give only the following:-

111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875

Since account numbers are assumed to be clean and same in both files,if we don't include the account number match similarity and confidence and have the confidence and similarity for the address alone that would be perfect.

So the idea is basically to compare the two addresses .Account number only helps to link the input file address with the reference file address.Instead of account number,if we can compare the address sequentially (like first record of input file to be compared with first record of reference only) then that is fine too.

This way,each address of FILEA need NOT be compared with each address of reference file which will be huge system consuming and is also not required.
Thanks for your patience and please do feel free to make me clarify further.

|||The CROSS PRODUCT matching you describe will not occur or at least it

will not be visible to you I think. Thinking about this I do not know

if the engine would consider matching (It is fuzzy after all) but

because of the so way out probability and confidence scores it would not

get further than a distant thought.

To that end I changed the input data slightly

CREATE TABLE AccountsInput(AcctNUm int, Street varchar(50))

CREATE TABLE AccountsLookup(AcctNUm int, Street varchar(50))

Insert AccountsInput VALUES(111,'45 Sunset Boulevard')

Insert AccountsInput VALUES(555,'236 technical str')

Insert AccountsInput VALUES(999,'448 Madeira')

Insert AccountsLookup VALUES(111,'45 Sunset BLVD')

Insert AccountsLookup VALUES(555,'236 technical st.')

Insert AccountsLookup VALUES(999,'448 Madera')

Insert AccountsLookup VALUES(996,'442 Madeira')--added very close to 999

I still only matched on the same rows.

Allam

"Ashraj@.discussions.microsoft.com"

wrote in message

news:5b707de0-1b00-40d3-9a4b-4868791a245f@.discussions.microsoft.com:

> Thanks Alan. It looks almost perfect .

> I just wanted to confirm one more thing before I say this is what I

> exactly want.

>

> For our comparison, I have the following basic assumption that

> a) account numbers are not corrupt and all account numbers in FILEA are

> in FILEB and viceversa.

> b) FILEA and FILEB are unique keyed with account number

>

> As you had detailed, I want address of account number 111 in FILEA to

> be compared to address of account number 111 ONLY in FILEB.

>

> The address comparison should be made ONLY between the records that have

> same account number in FILEA and FILEB .Comparison and statistics should

> not be made between address that don't have common account number.

>

>

> For example, Address of account number 111 SHOULD NOT be compared with

> address of account number 555 and 999.

>

> In our example for first record it SHOULD NOT execute 3 searches and

> give the following.

> 111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704

> 0.9875

>

> --

> 111 45 Sunset Boulevard 555 236 technical st. 0.1xxxxxx

> 0.xxxxx

>

>

> 111 45 Sunset Boulevard 999 448 Madera 0.9281157

> 0.9875

>

> It should do ONLY do comparison one time and give only the following:-

>

> 111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875

>

>

>

>

>

>

> Since account numbers are assumed to be clean and same in both files,if

> we don't include the account number match similarity and confidence and

> have the confidence and similarity for the address alone that would be

> perfect.

>

> So the idea is basically to compare the two addresses .Account number

> only helps to link the input file address with the reference file

> address.Instead of account number,if we can compare the address

> sequentially (like first record of input file to be compared with first

> record of reference only) then that is fine too.

>

> This way,each address of FILEA need NOT be compared with each address of

> reference file which will be huge system consuming and is also not

> required.

>

>

> Thanks for your patience and please do feel free to make me clarify

> further.|||Thank you much. Okay.let me put it in a slightly different way to address the issue,since assuming SSIS internally does the different combinations but displays only one ,would mean still again half million look ups for each of the records in FILEA.

Let us have both Mailing address and property address in one file as below.
This will be the only input data file .

Acct

Mail Address

Prop Address

111

45 Sunset blvd

45 Sunst Boulvrd

555

236 technical str

23 Technic street

999

448 madera

448 Made a

We want to do is Mailing address and prop address strings for each row and give the output as ACCT#,Mail Address,Prop Address ,Similarity,Confidance
as below.

Mail Address

Prop Address

Similarity

Confidence

45 Sunset blvd

45 Sunst Boulvrd

0.xxxxxxx

o.xxxxxxxx

236 technical str

23 Technic street

0.xxxxxxxx

0.xxxxxxxx

448 madera

448 Made a

0.xxxxxxx

0.xxxxxxx

Just one file that has both the reference field and look up field.

i need to ask a question.

how do i enter a new question?
i click new and nothing happens
--
thank you
ms. soto
"SouRa" wrote:

> Hi all,
> By default all the values in the result set are left aligned. But
> I want to align a column in a result set of a query to right side...
> Is it possible in SQL?
> Example:
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Result should be
> Charges
> 11145.00
> 171.00
> 26.00
> 6.00
> Please advise
> Thanks,
> SouraHi
That will depend on how you are using the newsgroup, for instance if you are
using the technet communities
http://www.microsoft.com/technet/co...server/sql.mspx you
will need to create a profile and use a passport to sign in before adding ne
w
messages or replying to existing messages. As you can reply then it looks
like you have this, in which case you would need to select the new button
which is just beneath the "search for:" area. If you are using Outlook
Express choose the New Post button or File/New/News Message from the menu.
HTH
John
"ms. soto" wrote:
[vbcol=seagreen]
> how do i enter a new question?
> i click new and nothing happens
> --
> thank you
> ms. soto
>
> "SouRa" wrote:
>

I need SQL Query Help

Sir,

We are try to insert query like this

Insert into Table_1 (smDate,Description) Values ('Thu, Feb 15, 2007 03:00 PM', ' Hai, It's Just for Testing')

This is Exact Query about this...

We know about MS ACCESS it has Built in function like Format('Thu, Feb 15, 2007 03:00 PM','mm/dd/yyyy HH:MM')

But I am new to SQL. I dont know the The Date Format built in function in SQL Server.

How can i Insert this Quesry to SQL Table

and also How can insert "it's " this type of words into SQL..

Can you help me

this is very helpful for my project

Thanks With Regards

S.Senthil Nathan

The quickest way i've used and seen being used to put an apostrophe into a SQL statment, is to do a replace on the string with a double quote, then you will need to do a reverse replace on the double quote back to an apostrophe, not the best way but one solution, the other is to escape the apostrophe with another so it would beit''sbut this would still require some parsing of the string.

Have a look at this artilce for working with formatting the date/time in SQL :http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||

For your date format issue, if you can remove the "Day, " from the date, you can insert the string you have. I.e. 'Feb 15, 2007 3:00 PM' is a valid date string and will be formatted automatically by SQL server according to how SQL server is set up. For example, if using the defaults on a US-based setup, SQL server formats the date like you requested.

if you need to insert something with single quotes, you have two options. First, you can replace it with two single quotes, i.e. 'it''s' would putit's in the database.

Second, you can use parameters. Your sql statement would be like "INSERT INTO dbo.MyTable (StringValue) Values (@.StringValue)", and then add a sql parameter to your sqlcommand as if you were using a stored procedure. If you had a Data.SqlClient.SqlCommand named command:

command.CommandType = Data.CommandType.Textcommand.CommandText = strSQL' this is your insert statement with a parametercommand.Parameters.AddWithValue("@.StringValue", "it's")

I prefer the second choice becuase it's easier than remembering to replace quotes and other sql syntax.

I need SQL Query Help

Sir,

We are try to insert query like this

Insert into Table_1 (smDate,Description) Values ('Thu, Feb 15, 2007 03:00 PM', ' Hai, It's Just for Testing')

This is Exact Query about this...

We know about MS ACCESS it has Built in function like Format('Thu, Feb 15, 2007 03:00 PM','mm/dd/yyyy HH:MM')

But I am new to SQL. I dont know the The Date Format built in function in SQL Server.

How can i Insert this Quesry to SQL Table

and also How can insert "it's " this type of words into SQL..

Can you help me

this is very helpful for my project

Thanks With Regards

S.Senthil Nathan

Senthil:

look up CAST AND CONVERT under CONVERT in books online to get a look at date and time formats. The ISO format is format 112. This should get you moving in the right direction.

Dave

|||Hi,

convertion was already explained by another posted, the issue with the quotes can be solved using two single quotes instead of one.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

I need help...!

Hello!
I am using IN to determine if a given StateId values matches any value in a
query.
Previously, I used following query:
SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
but now I would like to pass dynamic values as a input parameter...
SELECT * FROM Cities WHERE StateId IN(@.StateId)
StateId is an integer.
Could anyone help me with this, I can't get it working. I am new to SQL
Server.
Thank you!
JamesJames
CREATE PROCEDURE mysp
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC mysp
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
GO
"James T." <gimenei@.hotmail.com> wrote in message
news:O7P%23D8dOFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I am using IN to determine if a given StateId values matches any value in
a
> query.
> Previously, I used following query:
> SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
> but now I would like to pass dynamic values as a input parameter...
> SELECT * FROM Cities WHERE StateId IN(@.StateId)
> StateId is an integer.
> Could anyone help me with this, I can't get it working. I am new to SQL
> Server.
> Thank you!
> James
>|||The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"James T." wrote:

> Hello!
> I am using IN to determine if a given StateId values matches any value in
a
> query.
> Previously, I used following query:
> SELECT * FROM Cities WHERE StateId IN (1, 2, 3)
> but now I would like to pass dynamic values as a input parameter...
> SELECT * FROM Cities WHERE StateId IN(@.StateId)
> StateId is an integer.
> Could anyone help me with this, I can't get it working. I am new to SQL
> Server.
> Thank you!
> James
>
>