Help on SQL Query

Hi all,

I need some help on sql query.

I had a table named "tableA" where by I had a "timestart" and "timeend" field.

I would like to query all the records between timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' and also

timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM'

Can I do this in a single sql statement instead of querying the table twice?

Please help.

Thanks

Yes you can

select *
from tableA
where (timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' )
and (timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM' )

but in your example you will receive empty result because it is not possible for date to be valid for this query so maybe you mind this

select *
from tableA
where (timestart>= '2/2/2007 2:00:00 AM' and timeend<= '2/2/2007 3:00:00 AM' )
or (timestart>= '2/3/2007 4:00:00 AM' and timeend<= '2/3/2007 5:00:00 AM' )

Thanks

help on partition

i have a table named table1 sitting on a a partittion scheme px.

table1 is partitioned on u_id by tens,

first partion u_id=0 to 9

second partion u_id=10 to 19

third partion u_id=20 to 29

ans so on and so forth

i have a table named table2 with

records having u_id = 13 to 16

obviously belonging to the second partion.

i want to load table2 to table1 as fast as i could with

the following requirements:

all contents of partition 2 of table1 must be deleted before loading

table2 ceases to exist after the operation

how will i do that.

thanks.

The most efficient way is to do the following:

1. Use ALTER TABLE SWITCH to switch out partition #2 into unpartitioned table (table3)

2. Use ALTER TABLE SWITCH to switch in table2 into partition #2

3. Drop tables table2, table3

The following BOL topic has details on the switching strategies:

http://msdn2.microsoft.com/en-us/library/ms191160.aspx

And please check out the following whitepaper too.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

Help needed on Sql query with Time functions

Hello everyone,
can somone help me out with my problem? I have a table (named TblSensorRTData) with fields (sensorId -> char (3), dataTimeStamp -> datatime, readValue -> float) in MSDE 2000 (desktop version of MS SQL Server 2000).

There are 60 sensors at present and all of them are scanned exactly once every second. So, for every 1 second, a new set of 60 rows (corresponding to the 60 sensors) are populated into the table by the Acquisition program.

Phase II is that I have to display the data to the user at an interval he chooses (in another program).

For eg: if the user chooses the refresh rate as 10 seconds, I have to display information like this:
<pre>

Time Value1 Value2 Value3 Value4 Value5

10:30:00 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:10 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:20 xx.xx xx.xx xx.xx xx.xx xx.xx
10:30:30 xx.xx xx.xx xx.xx xx.xx xx.xx
.... .... ... ... ... ...
.... .... ... ... ... ...
.... .... ... ... ... ...

</pre>

The values correspond to the values in the table at the time specified by the column Time. Also, the user selects which, out of the 60 sensors, are to be displayed.

Hence I need to select the values of only those sensors (which are selected) and display their values at exactly those time (as specified in the interval). I have no clue how to do this. Hence I request you to help me out.

Regards,
Sriharsha.

PS: Please do not mistake my intensions. I am basically an Embedded Systems developer and I left Database programming 6-7 years ago. So, I can do it provided I have some time, but my schedule demands the results fast enough and hence I posted this query.Select columns
from table
where DATEPART(ss, date_column)%10 = 0

Should you wish to test the output,

create table test
(
date_field DATETIME,
pos INTEGER
)

Declare @.pos As Integer
Set @.pos = 0
While @.pos < 250000
Begin
insert into test values (GETDATE(), @.pos)
Set @.pos = @.pos + 1
End|||Dear Sir,

perhaps my question lacked the clarity it should have... Well, I have to display the updated fields every 'n' seconds (where n is the number chosen by the user). After the first n seconds, the values with time (seconds) n are to be selected and displayed. Also, a maximum of only 10 rows are to be displayed at any time. So after the 11th 'n' seconds, the screen has to scroll (im doing it in VB using the MS Flex Grid control) by one. i.e. all the rows should be shifted up by 1 and the last row should contain the latest extract.

So, at any given time when i am executing the query, I have to take the following into consideration:

1. Get only the values (of the required sensor ids) for time that is exactly 'n' seconds after the previous extract.
NOT ALL Values beginning some time and 'n' seconds downwards.

Warm Regards,
Sriharsha.

Help me to restore this database!!!

Hi,
I have a database named 'ePos' and i want to make
a copy from it by backup this database then restore
it as a new one with another name.
I use this commands:

sp_addumpdevice 'disk',
'ePosTest1',
'c:\test1.bak'

BACKUP DATABASE ePos TO ePosTest1 WITH INIT
GO

RESTORE DATABASE ePos_new FROM ePosTest1
GO

I have this error when restoring :
Server: Msg 1834, Level 16, State 1, Line 7
The file 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ePos2006.mdf' cannot be overwritten. It is being used by database 'ePos2006'.

How can i restore the database with new filesys? how i can fix this problem?

PLZ help.

Regards.

For best results, try posting this in a different forum more related to your issue. At the top of the screen, you will see the forum hierarchy. (MSDN Forums >> SQL Server >> SQL Server Integration Services...) Click on the SQL Server part of that tree to see the full list of forums related to SQL Server.

I'd suggest trying the disaster recovery forums.|||This thread was originally posted in SSIS forum...moved to Disaster Recovery and Availability|||

By default, SQL backups are restored to the same path as the original database.

In your case, you want to have the same content in different files, so you'll need to use the WITH MOVE clause:

RESTORE DATABASE ePosNew FROM ePosTest1

WITH MOVE 'ePos2006' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ePosNew.mdf' ,

MOVE 'ePos2006_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ePosNew_log.ldf'

go

You'll have to verify the logical names of your data and log files, but this is the technique.