hi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Mnay thanks for your help!
av
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
Hello there
I'm having data store in database at interval of 2sec.
hence if i read data for 1 week(7days) it returns me huge amount of data
so what i want to do is to read data at some varying intervals e.g 30 sec or 50 sec for perticular datetime range
Please tell me suitable query to have such selected data base
Thanks in advance
AVD
I have  two tables like hyd,hyd1 but hyd has data ,now I want to delete all rows from hyd at the same time insert deleted rows into hyd1.
View 7 Replies View Relatedhi
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
Hi
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId
Returns
TeamMemberId MonthId
1 14
6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName
Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO
The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.
Regards,
Stefan
I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.
There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.
If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.
So, I just want to recreate a situation by voluntarily locking the table.
Any help in this regard will be appreciated.
I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .
Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.
So I have a job that shows active processes
INSERT INTO Query_results(login_name, total_elapsed_time, total_elapsed_time)
SELECT login_name, total_elapsed_time, total_elapsed_time FROM
sys.dm_exec_sessions
I need to then kill all sessions at 11:59pm then log all those that are killed. This is so I can schedule a job at that time, I have sessions that are blocking my job.
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?
View 2 Replies View RelatedHello ,I have a stored procedure that group a series of event by day. My table Tevent has a timestamp column for every event.Tevent structure:idevent| event| DatetimeEvent
I just found out that I need to group the result by production shift of the day , therefore I have to group the result for everyday from 8 AM until 2 AM the next day.Any idea of how to group by time interval?Thanks
Hello,
I'm trying to create a group by a set (but can change it later on) time interval.
What I mean by this is the following. I would like to group my data by 5 second intervals, and perhaps have a count for it as well - but that's not required.
For example I have the following data:
PRODUCT, DOOR, TIME_LOCATED
-------------------------------------
chips, 1, 3/5/2008 12:33:30 PM
harddisk, 1, 3/5/2008 12:33:30 PM
tea, 1, 3/5/2008 12:33:31 PM
software, 1, 3/5/2008 12:33:31 PM
chips, 1, 3/5/2008 12:33:32 PM
chips, 1, 3/5/2008 12:33:33 PM
chips, 2, 3/5/2008 12:33:34 PM
software, 1, 3/5/2008 12:33:40 PM
tea, 1, 3/5/2008 12:33:40 PM
software, 1, 3/5/2008 12:33:41 PM
Result in 5 second intervals (so group by values between 3/5/2008 12:33:30 and 3/5/2008 12:33:35)
Something like this?, or does have somebody a better idea?
PRODUCT, DOOR, TIME_LOCATED, COUNT
-------------------------------------------
harddisk, 1, 3/5/2008 12:33:30 PM 1
tea, 1, 3/5/2008 12:33:31 PM 1
software, 1, 3/5/2008 12:33:31 PM 1
chips, 1, 3/5/2008 12:33:33 PM 3
chips, 2, 3/5/2008 12:33:34 PM 1
software, 1, 3/5/2008 12:33:40 PM 2
tea, 1, 3/5/2008 12:33:40 PM 1
THANKS!
I have a table(work_order) with time as varchar(5).The values in table looks like thiswork_order_id rtim1 08:152 08:453 10:134 14:56and so on...I want to count how many work orders for every half an hour.The result should look like thisHours Count8 108:30 159 349:30 03and so on....really 8 hours means the work_orders issued (rtim)between 8:00 AND 8:30.Any Help is Appreciated.Thankyou.Jaidev Paruchuri
View 4 Replies View Relatedhi,
i need SP that receive 2 integers ,@NUM_ROWS and @PAGE_NUMBER,
and return the rows in that page.
for example:
SP(4,2) will return 4 rows in page number 2 .
So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.
i have to assume that rows can be deleted form that table.
thanks
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
I need to add the Job Interval and Time to the following script:
SELECT DISTINCT 'CYP_BI' AS Server,Jobs.Name AS Job_Name, Jobs.description AS Alias,
'Enabled' = CASE (Jobs.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
[Code] .....
This query contains the job interval and time but when I add it to the first script I can't get my syntax right:
SELECT DISTINCT
'CYP_BI' AS Server,
S.name AS JobName,
S.description AS Alias,
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
[Code] .....
Time Interval for hour and 30 minutes
--------------------------------------------------------------------------------
Hi Everyone
I Have column in sql server databas as "HHMMSS" and data as and i am doing a substring to get values for hours and minutes. since my calculations based on hour interval and 30 minutes interval
for ex: Now i want to show all the transaction done b/w 6 to 7 am or pn.
and for 30 minutes interval i have get the calculation as transactions done b/w 6:00 to 6:30 and 6:30 to 7:00 either it's am or pm. now how i can write my sql statements that calculates hour and 30 minutes intervals
HHMM
-------------
06:43
09:26
09:26
11:58
12:25
18:17
20:45
00:43
00:53
16:47
Thanks
Phani
I have a problem that I can't quite get started on solving.I have a table of asset statuses. Each time the asset status changes, anew row is inserted into the table.CREATE TABLE dbo.Tbl_EMStatusHistory (EMStatusID int IDENTITY (1, 1) NOT NULL ,EMSessionID int NULL ,AssetID int NOT NULL ,AssetStatus int NOT NULL ,StatusComment varchar (250) NULL ,StatusDate smalldatetime NOT NULL ,InsertUser sysname NOT NULL ,InsertDate smalldatetime NULL ,CONSTRAINT PK_EMStatusHistory PRIMARY KEY NONCLUSTERED(EMStatusID))Here is some sample data:EMStatusID AssetID AssetStatus StatusDate DeviceType4772622624OUT2003-10-05Monitor3810022624IN2003-10-16Monitor4726122624OUT2003-10-25 Monitor3819322624IN2003-11-02 Monitor3917122624RV2004-05-02 MonitorFor asset 22624, the current (most recent) status = RV. Before that, itlastchanged status on 2003-11-02, when it was IN. So it was IN from2003-11-02 to2004-05-02.I need to produce a report that counts the devices by type that were INon a given report date,like so:ReportDateMonitorsTransmitters05/01/2004342605/02/2004373005/03/2004393205/04/20043531The end user supplies the date range.I am unsure how to approach this. I came up with oneEinsteinian-Newtonian method that involved three temp tables, a gardenhose, and a duck, and it was getting uglier by the minute. Has anyonehad experience with a requirement like this? Thanks, Dave"Are you thinking what I'm thinking, Pinky?"*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:14:00.000
EVERY THING ABOVE HERE IS IN GROUP 1
2008-01-01 00:35:00.000
EVERY THING ABOVE HERE IS IN GROUP 2
2008-01-01 01:01:00.000
2008-01-01 01:03:00.000
2008-01-01 01:03:00.000
2008-01-01 01:04:00.000
EVERY THING ABOVE HERE IS IN GROUP 3
2008-01-01 01:29:00.000
EVERY THING ABOVE HERE IS IN GROUP 4
2008-01-01 01:41:00.000
EVERY THING ABOVE HERE IS IN GROUP 5
2008-01-01 02:25:00.000
2008-01-01 02:28:00.000
2008-01-01 02:31:00.000
2008-01-01 02:33:00.000
EVERY THING ABOVE HERE IS IN GROUP 6
Hope this is enough Info
I want to check a record in my table which has time ins and time outs feilds. I want to check on insertionof any new record that a new record should not be inserted on same timings and before 45 mins of timein and after 45 mins of timeout. How do i do it sql query ?
View 1 Replies View RelatedI have a simple table as shown:

I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
entry(EventID = 2) Â - exit(EventID = 1), and so on
how to retrive image from database change automatically into time interval
View 2 Replies View RelatedIs it possible to change the default detection interval time to reduce to less than 5 seconds.
We have latency in trouble shooting the deadlocks and causing blockings more on our critical Production server.
I'm trying to create a report which will give me a break down of how many unique vehicles have been seen between two dates via a 15 minute interval and what Lane they were seen. My current script looks like this
SELECT l.Name [Name], count(l.Name) Total, p.Created
FROM PlateReads p
inner join Lanes l on p.Lane_ID = l.ID
where LicencePlate in (Select Plate from LPRnet_MelAir_C.dbo.TempVehiclePlates)
group by Name
Name being the Lane they were in and the Total being the amount of times a unique vehicle has been seen and p.Created being the date they were seen (thats what I need the interval powered off)
Ideally the output would look like this
16/03/201408:00 to 08:15Bus Lane 15
16/03/201408:00 to 08:15Elevated Road150
16/03/201408:00 to 08:15Public Pickup75
16/03/201408:15 to 08:30Bus Lane 13
16/03/201408:15 to 08:30Elevated Road120
16/03/201408:15 to 08:30Public Pickup55
All the way to 12/04/2014
I’ve got it so it says Lane and Count just can’t get the interval part
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
There is a stored procedure that inserts a row into 'Vendors' table. Is it possible that two different calls to this sp happen at the same time and as a result, each sp inserts into the table its row at exactly the same time?
View 1 Replies View Relatedhi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this
One can never consent to creep,when one feels an impulse to soar
RAMMOHAN
Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?