Hi guys,
This might be simplest thing, but I am newbie to databases.
I need to find out only rows modified within certain time period from a
database. As I undertand a way out could be adding an where clause for
the time period might be an option, I might be wrong here again.
But, wanted to know is there any other option. Can triggers or any
other things help me in this matter.
Regards,
Abhijeet
What is the easiest way to write a query statemet to find two or more instances that occur in a 15 minute period? Any help would be greatly appreciated. Thanks.
View 6 Replies View RelatedI have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this:
IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL
BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
Hi! all
How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below. With reference to Log_Time
CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL
) ON [PRIMARY]
GO
Thank you in Advance
Hi! all
How do I get student_ID's that attended prep between 3:00am and 4:00 am on daily basis from table below
CREATE TABLE [dbo].[PrepTime] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PrepNo] [int] NOT NULL ,
[Log_Time] datetime NOT NULL DEFAULT, CURRENT_TIMESTAMP,
[coming] [int] NULL ,
[going] [int] NULL ,
[Student_ID] [bigint] NULL ,
[Study_Type] [int] NULL ,
[State] [smallint] NULL
) ON [PRIMARY]
GO
Thank you in Advance
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View 5 Replies View RelatedFinding Time Zone:
Hi,
1. I have a patient record created in the database with a CreateDate
2. I need to find the time zone based on a state or city or zip in the record
3. And find corresponding EST time of CreateDate
Is this possible?
I have a start_datetime column and an end_datetime column that records when a process we run starts and stops. I am trying to write a query that can calculate the difference between the start time and the end time and display it as hh:mm:ss but cannot seem to come up with a solution. Any ideas?
View 2 Replies View RelatedHi Friends,
Is there any way to find the time when the last DDL was happened in a table?
For example: The time when the new column(s) were added into a table or changed the datatype.
Thanks for the help in advance.
--Raj
I have a table that contains the following
UserName - Which is unique to each user
TimeStart - Which is the time and date that the user logs in
TimeEnd - Which is the time and date that the user logs out
Using SQL, how do you find who has logged in using the same account within the same time period. In other words, Two people are using the same username and are logged in at the same time.
When I open a table and need to make a change within a particular record...
How do I find the particular row that I am looking for? I seem to have to scroll through 600 records to find the one I need. I've found a search and replace function but it doesn't seem to work.
Thanks I appreciate any advice you can give me.
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
Can this be done easilly through a stored proc?
View 1 Replies View RelatedI have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
CREATE TABLE [RS_A] ([ColA] [varchar] (10)[ColB] [int] NULL)CREATE TABLE [RS_B] ([ColA] [varchar] (10)[ColB] [int] NULL)INSERT INTO RS_AVALUES ('hemingway' , 1)INSERT INTO RS_AVALUES ('vidal' , 2)INSERT INTO RS_AVALUES ('dickens' , 3)INSERT INTO RS_AVALUES ('rushdie' , 4)INSERT INTO RS_BVALUES ('hemingway' , 1)INSERT INTO RS_BVALUES ('vidal' , 2)I need to find all the rows in A which do not exist in Bby matching on both ColA and ColBso the output should bedickens 3rushdie 4So if i write a query like this , I dont get the right result setSELECT A.ColA, A.ColBFROMRS_A AINNERJOIN RS_B BONA.ColA <B.ColAORB.ColB <B.ColBBut if i do the following, i do get the right result, but followingseems convoluted.SELECT A.ColA, A.ColBFROMRS_A AWHERE ColA + CAST(ColB AS VARCHAR)NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROMRS_B B)
View 6 Replies View RelatedI'm trying to send data from a table on SQL server to a DB2 table on the Main Frame and I get this error. Any ideas?
[IBM][CLI Driver][DB2]SQL0904N Unsuccessful execution caused by an unavailable resource. Reason code: "UPDATABLE WORKFILE", type of resource: "100", and resource name: "TEMP DATABASE". SQLSTATE=57011
Does anyone know how to add a hyperlink with a target frame. In the navigation tab of reporting services I am having difficulites adding
http://www.linkgoeshere.com target="framename"
Thanks
I want to frame a range of data based on particular group of columns
If OBJECT_ID('tempdb..#ResellerRange') IS NOT NULL
drop table #ResellerRange
create table #ResellerRange
( ResID varchar(10)
, amt decimal(18,2)
, serialno int)
insert into #ResellerRange ( ResID,amt, serialno )
values ('Raja',10,67),('raja',10,68),('raja',10,89),('Prabu',20,56)
I want below output
resid amt min max
----------------------------------
raja 10 67 68
raja 10 89 89
Prabu 20 56 56
I want to export file such as .csv, xml , word from ReportViewer in visual studio 2008 but when I run on ASP.NET .net framework 2.0, it can export only excel & pdf files. But in business intelligent project in Visual Studio 2005 I can export all files that I want.
How can I export file from reportviewer in visual studio 2008 run on web application?
Regards,
NPeng
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
Hi,
I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).
From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.
The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.
As an example, the table may contain records with the following data..
1234, 1, Fred
1234, 2, Bert
1234, 3, Bob
If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?
Is this a job for ADO.Net or T-SQL ?
Thanks in advance.
Steve.
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 RelatedHello,I have 4 tables having Customer, Customer_personal_info, Customer_Financial_info, Customer_Other_infoIn this Customer table had a primary key CustomerID , related with every other table with fkey.I want to insert data into four tables using one form having TABs .I created class and storedProcedures to insert row for each table.How to execute all four classes using beginTrans-commitTrans-Rollback-EndTrans. Thanking you,
View 1 Replies View RelatedHi all -
is there a way to process a file x records at a time?
We have a table that I need to append to an existing table. The date columns are currently in char but must be converted to datetime for the existing table. The problem is I have bad data. There are 3 million rows where the date field isn't valid for SQL's datetime format. Since this is the data I have, I have to work with it. I would like for SQL to just insert a null if it comes upon a bad date. Currently when it encounters a field that isn't valid, it stops the process with an error.
I have tried to go around it below, but there is still something "hanging" I would like to be able just to insert one million rows at a time and if it errors, then I can look at the next million, find the error, fix it and continue on.
Any suggestions? Or if you have a better idea all together I would love to see it.
SQL Server 7.0, SP2
,CASE
when
(substring(check_date,1,4) not between '1997' and '2000' or
substring(check_date,5,2) not between '01' and '12' or
substring(check_date,7,2) not between '01' and '31') THEN null
ELSE cast(check_date as datetime)
END AS check_date
Thanks,
Michelle
Hi all,
Can one of you help me with using a cursor that would insert only 100 rows at a time from source table 1 to target table 2. I am not able to loop beyond the first 100 rows.
Here is what I have till now:
CREATE procedure Insert100RowsAtaTime
AS
SET NOCOUNT ON
declare @Col1 int
declare @Col2 char(9)
DECLARE @RETURNVALUE int
DECLARE @ERRORMESSAGETXT varchar(510)
DECLARE @ERRORNUM int
DECLARE @LOCALROWCOUNT int
declare Insert_Cur cursor local fast_forward
FOR
SELECT top 100 Col1,Col2 from Table1
WHERE Col1 not in ( SELECT Col1 /* Col1 is PK. This statement is used to prevent the same rows from being inserted in Table 2*/
from Table2)
set @RETURNVALUE = 0
set @ERRORNUM = 0
BEGIN
open Insert_Cur
fetch NEXT from Insert_Cur into @Col1, @Col2
while (@@FETCH_STATUS = 0)
insert into Table2 (Col1,Col2) select @Col1,@Col2
SELECT @ERRORNUM = @@ERROR, @LOCALROWCOUNT = @@ROWCOUNT
IF @ERRORNUM = 0
BEGIN
IF @LOCALROWCOUNT >= 1
BEGIN
SELECT @RETURNVALUE = 0
END
ELSE
BEGIN
SELECT @RETURNVALUE = 1
RAISERROR ('INSERT FAILS',16, 1)
END
END
ELSE
BEGIN
SELECT @ERRORMESSAGETXT = description FROM [master].[dbo].[sysmessages]
WHERE error = @@ERROR
RAISERROR (@ERRORMESSAGETXT, 16, 1)
SELECT @RETURNVALUE = 1
END
fetch NEXT from Insert_Cur into @Col1, @Col2
end
close Insert_Cur
deallocate Insert_Cur
RETURN @RETURNVALUE
END
I have a table of machine data that captures fault codes, time machine stopped and time machine started. I can easily calculate the downtime, but how do i take the last start time and subtract it from the next stop time (1 row from the next row) to get an up time. I need it to group by short date and fault as I intend to use Excel as a reporting tool and pulling in all data will not fit. What is the most efficient way?
Thanks,
Lee
The stored procedure takes ~ 10 seconds every call. The execution plan is Index seek or clustered index scan almost everywhere . No table spools or lazy loads or key lookups. Cannot share the code or execution plan due to company rules and regulations.Issue is that i am dealing with result set of around 50,000 records . Out of these i have to return 20 records at a single time (which is also customizable i.e 40 / 60/ 150 records in a page). Application cannot handle all 50k records so i have to return 20 records for every stored procedure call.
The result set changes as per the start date and end date which i recieve as parameters. In application there are few Column filters namely- Country(around 50 countries), Outcome(around 6 to 10 values ) . These filters will values in drop down( as excel ) depending on the distinct values in that columns. These filters will be populated on every page, if no filter value is selected.Issue is if user does sorting or filtering any records , this stored procedure is called and every time i have to deal with ~50000 records.
Current Code :
Step 1 ) Get the required result set in temp table .
Step 2) Compute the results on some business rules . (Outcome and SharesAvailable calculation - see attachment)
Step 3) populate filter Columns (Country,Outcome) these values will be comma seperated.
Step 4) Dynamic query to get required result set i.e if user wants only 10 records in single page then TOP 10 . Sorting can be applied on any column mentioned in screenshot.
hi,
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
I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.
View 3 Replies View RelatedI have a table data like below
id type timestamp
1001 start1 10:34:23:545
1001 start2 10:34:24:545
1001 end2 10:34:24:845
1001 end1 10:34:25:545
1002 start1 10:34:25:645
1002 start2 10:34:25:745
1002 end2 10:34:25:945
1002 end1 10:34:25:965
I need the result as follows
id millisecond diff start1end1 millisecond diff start2end2
1001 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
1002 end1 timestamp-start1 timestamp end2 timestamp-start2 timestamp
SQL Server 2008 R2
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
I'm dealing with a problem.
The record information example
DateTimeStart , DateTimeEnd , action , duration (seconds)
2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600
what i want is for every half hour between start and end a record
10.30 action1
11.00 action1
11.30 action1
how can i create this, i'm a little stuck on this