failed to search using d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007'

hi all programmers.
i want to find the record that created on only 05/07/2007
the datetime in my database is 5/7/2007 4:09:00 PM
i used this (d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007' ) inside the where clause.
but it returns no result.
please help me on this issue. thanks for all helps.

Try this

d.DateCreated >='05/07/2007' AND d.DateCreated< '06/07/2007'

|||

Or, to avoid ambiguity caused by your connection's language settings, you might want to use the following date format:

yyyy-mm-dd hh:mi: ss.mmm

<ignore the space after mi: - excluding it from forum posts results in this: mi >

e.g.

d.DateCreated >= '2007-07-05 00:00:00.000' AND d.DateCreated < '2007-07-06 00:00:00.000'

or

d.DateCreated >= '2007-05-07 00:00:00.000' AND d.DateCreated < '2007-05-08 00:00:00.000'

(depending on the format of the date that you oiginally provided)

Chris

|||try this one

CONVERT(smalldatetime,CONVERT(varchar(10),d.DateCreated,101)) >=CONVERT(smalldatetime,'05/07/2007') AND
CONVERT(smalldatetime,CONVERT(varchar(10), d.DateCreated,101))<= CONVERT(smalldatetime,'05/07/2007')|||The where clause you specified is only looking for EXACTLY midnight, 5/07/2007 12:00am. If you want to search for anything on 5/7 use:

DateCreated >='5/7/2007' AND DateCreated < '5/8/2007'

Extremely slow median measures

Hi

I'm having a problem with extremely slow median measures.

I've created a named set of all record IDs and wrote the measure as Median( [All Records], [Measures].[Age] ). When I drop a dimension into one of the axes, it takes a very long time to calculate the median even at the top level of the hierarchy, and I suspect it's computing the median for all the members of that dimension, even before I've drilled down into them.

Anyone know a better method for this?

Your formula computes Median for all records always, regardless of the selection in Records dimension.|||

Sorry Mosha, I don't quite follow. I need this to be a generic measure that will return the median of any cell in the client browser. Since median cannot be preaggregated, I thought the only way to do this was to take the median of the set of all records in the current cell. The other option I had explored went something like this:

Median (

{ ( Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.All, [All Records].[ ID ].[ ID ] ) },

[Measures].[Age]

)

But this didn't work at all.

|||Sorry, this time I don't quite follow. What exactly do you mean by the following: "I need this to be a generic measure that will return the median of any cell in the client browser". Median of what ? Perha[s you could illustrate with couple of examples.|||

My mistake... Should have said median of a measure (e.g. age) within any cell in the spreadsheet. For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells. Other times, users would be interested in the median age for other combinations of factors, say Gender and Cancer, or Cancer and Socioeconomic Status, but they shouldn't have to select a different median measure for each combination. Sort of like a percentage/proportion against any dimension selected on the row/column axis, as discussed in the following post:

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

|||

If you are guaranteed to always have two axes, then something like that may work

Median(CrossJoin(Axis(0), Axis(1)), Measures.[Age])

|||

Thanks Mosha, but no luck with that one either. There's probably no other way around this; that is, other than using the Fact Table primary key (Record ID) to select the set of all individual age values.

|||

Then I again don't understand your requirements :( Based on what you wrote before:

> For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells.

The formula that I wrote computes then median of these 4 cells and places it into each one of these 4 cells. I have verified it with AdventureWorks which has Gender and Marital Status attributes...

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id
|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

extracting the specified record no of records from database table

hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

There is no such thing like row number in MS SQL. You will need to create an identity column in your table, or maintain the number column manually.|||

If you are using SQL Server 2005 then you can use the ROW_NUMBER() function instead:

select *

from (select *, row_number() over(order by CustomerId) as rownum

from customers

) as c

where c.rownum between 1000 and 10000;

If you are using SQL Server 2000 then the identity column approach using temporary table is the best way to go (I guess you are doing this now based on the information in your post).

Also, it seems like you are trying to batch some DML operation. If so you can use SET ROWCOUNT or TOP clause in DML (SQL Server 2005). See below for example:

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

set rowcount @.n

while(1=1)

begin

insert into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

set rowcount 0

-- using TOP

declare @.n int

set @.n = 1000 -- set number of rows you want to insert at a time

while(1=1)

begin

insert top(@.n) into MasterCustomers

select ...

from Customers as c1

where not exists(

select * from MasterCustomers as c2

where c2.CustomerName = c1.CustomerName)

if @.@.rowcount = 0 break

end

|||SQL 2000 does not have the limit keyord. You can use double TOP instead of, but you must have a primary key on that table
select * from
(select top 1000 * from (
select top 11000 *
from customers
order by customer_id asc
) as tmp1
order by cutomer_id desc
) as tmp2
order by ...

for extract customers betweeen 10000 and 11000 based on customer_id|||

if you use sql2005

you also can use the function ROW_NUMBER() to

select rownum between 1000 and 10000 like oracle

example:

with customers _temp as

(SELECT ROW_NUMBER() OVER (order by customer) as RowNumber,*
from customers)
select *
from customers _temp
where RowNumber between 1000 and 10000

extracting duplicate record on the same id

Hi everybody i need help on on a query on how i can extract this... with the following table below..

id pub
1 a
1 b
2 c
2 c

I need to extract only the id and pub where pub has more than one with the same id... in the case of the above the result would be

id pub

2 c
2 c

thanksHi Alex

Same question recently asked:
http://www.dbforums.com/showthread.php?t=1620094

If you are satisfied with only
2 c

returned rather than
2 c
2 c

(and the second makes no sense to me BTW) then the first answer by r937 is plenty. Otherwise go down to the bottom for the last answer.

HTH|||thanks got it..

Extracting data from each record in a column

I need to be pointed in the right direction on extracting data. I currently
have a clumn that contains a field that holds several different pieces of
data that I want to put into specific fields. Examples of the current data
in the Notes column is;
A&L-97-92 MOVED BY: Mr. Jenkins SECONDED BY: Mr. Holstock boundary changes
be received ****CARRIED.****
PW&P-181-92 MOVED BY: Mr. Gifford SECONDED BY: Mr. Moore scholarships be
awarded ****CARRIED.****
93-353 MOVED BY: Mr. Nelson SECONDED BY: Mr. Gifford ban the landfilling of
Old Corrugated Cardboard ****CARRIED.***
CW-20-01 MOVED BY: Mr. Cathcart SECONDED BY: Mr. Batten be received for
information. ****CARRIE
PW&P-144-96 MOVED BY: Mr. Mann SECONDED BY: Mr. Cathcart be received for
information. ****CARRIED.****
I would like to seperate this into the following fields;
MOTION MOVE SECOND COMMENTS
RESULT
-- -- --
-- --
A&L-97-92 Mr. Jenkins Mr. Holstock boundary changes be received
CARRIED.
PW&P-181-92 Mr. Gifford Mr. Moore scholarships be awarded
CARRIED.
93-353 Mr. Nelson Mr. Gifford ban the landfilling of Old
CARRIED.
Corrugated Cardboard
CW-20-01 Mr. Cathcart Mr. Batten be received for information.
CARRIED
PW&P-144-96 Mr. Mann Mr. Cathcart be received for information.
CARRIED.
Any help is appreciated.Assiming that all the columns are in the same table: test1,
Test1 (structure):
test
motion_result
move
second_by
comments
--
Assumptions:
Name in second_by column is always one word (Mr. xxx) - xxx as one word
There would always be 'MOVED BY:' and 'SECONDED BY:' strings in the source
update test1
set motion_result = ltrim(rtrim(substring(test, 1, (charindex('MOVED
BY',TEST) - 1) ))),
move = ltrim(rtrim(substring(test, (charindex('MOVED BY',TEST) + 9),
(charindex('SECONDED BY',TEST) - charindex('MOVED BY',TEST) -9 ) ))),
second_by = ltrim(rtrim(substring(test, (charindex('SECONDED BY',TEST) +
12), (charindex(' ', test, (charindex('SECONDED BY',TEST) + 17) ) -
charindex('SECONDED BY',TEST) - 12)))),
comments = replace(ltrim(rtrim(substring(test, charindex(' ', test
,charindex('SECONDED BY',TEST) + 18), 100))), '*','')
Check and modify according to your needs.....
T-Sql procedure would be easier, but could take time to run...
Hope it helps,
_Uday
"Christo" wrote:

> I need to be pointed in the right direction on extracting data. I current
ly
> have a clumn that contains a field that holds several different pieces of
> data that I want to put into specific fields. Examples of the current dat
a
> in the Notes column is;
> A&L-97-92 MOVED BY: Mr. Jenkins SECONDED BY: Mr. Holstock boundary changes
> be received ****CARRIED.****
> PW&P-181-92 MOVED BY: Mr. Gifford SECONDED BY: Mr. Moore scholarships be
> awarded ****CARRIED.****
> 93-353 MOVED BY: Mr. Nelson SECONDED BY: Mr. Gifford ban the landfilling o
f
> Old Corrugated Cardboard ****CARRIED.***
> CW-20-01 MOVED BY: Mr. Cathcart SECONDED BY: Mr. Batten be received for
> information. ****CARRIE
> PW&P-144-96 MOVED BY: Mr. Mann SECONDED BY: Mr. Cathcart be received for
> information. ****CARRIED.****
> I would like to seperate this into the following fields;
> MOTION MOVE SECOND COMMENTS
> RESULT
> -- -- --
> -- --
> A&L-97-92 Mr. Jenkins Mr. Holstock boundary changes be receiv
ed
> CARRIED.
> PW&P-181-92 Mr. Gifford Mr. Moore scholarships be awarded
> CARRIED.
> 93-353 Mr. Nelson Mr. Gifford ban the landfilling of Old
> CARRIED.
> Corrugated Cardboard
> CW-20-01 Mr. Cathcart Mr. Batten be received for informatio
n.
> CARRIED
> PW&P-144-96 Mr. Mann Mr. Cathcart be received for informatio
n.
> CARRIED.
> Any help is appreciated.|||Thanks for the help so far
I presummed all the records were complete but they are not, there are some
records that are missing Moved and Seconded, as well some missing seconded.
There are some records that are even blank. Is it simple enough to put in i
f
null statements? If so, where do I place them?
Thanks again for your help.
"Uday" wrote:
> Assiming that all the columns are in the same table: test1,
> --
> Test1 (structure):
> test
> motion_result
> move
> second_by
> comments
> --
> Assumptions:
> Name in second_by column is always one word (Mr. xxx) - xxx as one word
> There would always be 'MOVED BY:' and 'SECONDED BY:' strings in the source
> update test1
> set motion_result = ltrim(rtrim(substring(test, 1, (charindex('MOVED
> BY',TEST) - 1) ))),
> move = ltrim(rtrim(substring(test, (charindex('MOVED BY',TEST) + 9),
> (charindex('SECONDED BY',TEST) - charindex('MOVED BY',TEST) -9 ) ))),
> second_by = ltrim(rtrim(substring(test, (charindex('SECONDED BY',TEST) +
> 12), (charindex(' ', test, (charindex('SECONDED BY',TEST) + 17) ) -
> charindex('SECONDED BY',TEST) - 12)))),
> comments = replace(ltrim(rtrim(substring(test, charindex(' ', test
> ,charindex('SECONDED BY',TEST) + 18), 100))), '*','')
> Check and modify according to your needs.....
> T-Sql procedure would be easier, but could take time to run...
> Hope it helps,
> _Uday
> "Christo" wrote:
>|||Not sure If I understand your question correctly...
if you add where clause
where charindex('MOVED BY:', test) <> 0
and charindex('SECOND BY:', test) <> 0
to update just the good ones..
_Uday
"Christo" wrote:

> Thanks for the help so far
> I presummed all the records were complete but they are not, there are some
> records that are missing Moved and Seconded, as well some missing seconded
.
> There are some records that are even blank. Is it simple enough to put in
if
> null statements? If so, where do I place them?
> Thanks again for your help.
>|||I would basically want to put a 'null' value or blank data into the
corresponding new column if there was no data in the old column to pull.
"Uday" wrote:

> Not sure If I understand your question correctly...
> if you add where clause
> where charindex('MOVED BY:', test) <> 0
> and charindex('SECOND BY:', test) <> 0
> to update just the good ones..
> _Uday
> "Christo" wrote:
>
>|||On Thu, 8 Sep 2005 12:50:02 -0700, Christo wrote:

>I would basically want to put a 'null' value or blank data into the
>corresponding new column if there was no data in the old column to pull.
Hi Christo,
In that case, you'll have to use CASE in each of the assignments in the
SET clause, like this (for brevity, I won't repeat the complete string
manipulation expressions that were in a previous post in this thread)
UPDATE YourTable
SET motion_result = CASE
WHEN Notes LIKE '%MOVED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
move = CASE
WHEN Notes LIKE '%MOVED BY%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
second_by = CASE
WHEN Notes LIKE '%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END,
comments = CASE
WHEN Notes LIKE '%SECONDED BY%'
THEN -- complicated expression goes here
ELSE NULL
END
(untested - see www.aspfaq.com/5006 for the steps required to get tested
answers)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)