Transact SQL :: How To Get All Tables In DB Effected In Last 15 Minutes

Sep 24, 2015

I need an SQL query to get all tables in DB effected in last 15 minutes. 

Effected in terms of insert/delete/Update

View 9 Replies


ADVERTISEMENT

Transact SQL :: Getting Time Difference In Hours And Minutes?

Jul 10, 2015

Currently my script is using the below mentioned query to find the time difference.

DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))

If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?

View 14 Replies View Related

Transact SQL :: SP With Parameter That Indicates Interval In Minutes That They Should Look Back

Jun 5, 2015

I have 3 requests to do and all 3 are below in SQL  , i am planning to keep them as job and run on regular intervals as per request ..

1.Poll the contract approval table for no prepared new contracts.  Check every 30 minutes from 9am to 10pm – 7 days a week.

IF NOT EXISTS (SELECT * FROM ContractApproval WITH (NOLOCK) WHERE ContractPrePared>DATEADD(MINUTE, -30, GETDATE()))
BEGIN 
SEND DBMAIL ---i have this part working already 
END 
 
2.Poll the contract approval table for no new signed contracts.  Check every 30 minutes from 9am to 10pm – 7 days a week.

  IF NOT EXISTS (SELECT * FROM ContractApproval  WITH (NOLOCK) WHERE DateSigned>DATEADD(MINUTE, -30, GETDATE())
BEGIN 
SEND DBMAIL ---i have this part working already 
END 

3.Poll the lead table for no new leads.  Check every 5 minutes.  24/7.

   IF NOT EXISTS (SELECT *      FROM   Lead WITH ( NOLOCK )      WHERE  DateCreated   > DATEADD(MINUTE, -5, GETDATE()))
BEGIN 
SEND DBMAIL ---i have this part working already 
END 

I am planning to keep 1,2,3, in seperate sp's and run them as JOBS....how can i achieve below a,b ?

a.The procedures should accept a parameter that indicates the interval, in minutes, that they should look back
b.Each step in 3 jobs should invoke the procedures, specifying the interval with which the job runs at

View 7 Replies View Related

Transact SQL :: Calculate Minutes In Stored Procedure Using Datediff

May 9, 2015

I needed to create a stored procedure to lock a user who makes 3 incorect entries of his password. I did it successfully. Now the problem what i have is that i want to lock the user  only if he makes the 3 incorrect entries within 30 minutes.

I created a field named "FirstEntryTime" of type datetime  that saves the date of the first incorrect entry.  I tried to make an if statement:

if (@timesOfEntry <=2 AND DATEDIFF(MINUTE, firstEntryTime,GETDATE()) <= 30)
Begin 
 Update myTable set ...
 End

View 6 Replies View Related

Transact SQL :: Convert Time In Hours And Minutes To Decimal

Jul 30, 2008

I am trying to convert hours and minutes to decimal and arrive at a sum of time taken. The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.

SELECT DATENAME(weekday, Started) AS Day,        C.Category,     ClientCode,Description,    dbo.FormatDateTime(Started, 'HH:MMS 12') as Started,    dbo.FormatDateTime(Ended, 'HH:MMS 12') as Ended, CONVERT(varchar,TimeTaken,108) AS TotalTimeSpentFROM dbo.Journal JLEFT OUTER JOIN dbo.Categories C ON J.CategoryID = C.CategoryIDWHERE--DATENAME(weekday, Started) =@Weekday  AND Started >= @StartDate ORDER BY Day, Category, Started

View 6 Replies View Related

Transact SQL :: How To Delete Newly Inserted Rows Automatically From Table After 30 Minutes

Sep 29, 2015

I would like to perform autodeletion operation from my table using stored procedure....

Here is my table structure:

Emp.id | emp_name | dept | desig | time_out | date | emp_sign |

if the user inserts new employee record that record should delete automatically from original after

30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...

View 12 Replies View Related

T-SQL (SS2K8) :: Display Row As 2 Days Ago / 1 Hours 34 Minutes Ago / 11 Minutes Ago

Apr 21, 2015

My table as data as follow,

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))

[code].....

View 6 Replies View Related

No. Of Rows Effected

Jun 12, 2007

Hi,

In a procedure I want to know the no. of rows effected.

That means. No of rows inserted, updated , and no of records retrieved by an SQl statement.

Is there any way to know.

Thanks

Sreenu

View 6 Replies View Related

Commad Works But 0 Rows Effected

Apr 26, 2006

What does it mean when you have a querey like this



Update tblWatchInstance SET [NewInvoiceGen] = '1' Where tblWatchInstance.WatchID = '%" & txtWatchID.Text & "%'"





But when you run in the Qanalyzer it says 0 rows effected...why is that??? could it be something i am missing.....

View 1 Replies View Related

Get Total Updated Rows Effected By A Statement

May 6, 2008

This was a usual day today in office and i was working on a requirement in which i was needed to fetch the total number of rows effected by an update query, so I asked my best code mate "Google" and to my surprised there was not enough correct answers at least the one i was looking for.There were suggestions that  you can use a select statement for the updated rows and make it like a select (count) which works fine, but just looking into the SQL server books online, it shows that there is even a better way to do it.After the update statement in my  stored procedure i used "@@ROWCOUNT" with a select statement and it works like a charm.so the little find for my first ever post on asp.net is that there is a better way to find the total updated rows by a query Example: DB: Northwind , Table Employeesupdate employees set extension='1234'select @@ROWCOUNT  This will return 9 (default rows in this table) as the rows effectedHope this helps 

View 2 Replies View Related

Power Pivot :: Can Measure Be Effected By Cross-filtered But Unselected Slicer Item?

Aug 17, 2015

My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.

I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:

Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))

And it ends up looking like this:

Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:

I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.

View 2 Replies View Related

Transact SQL :: Top 10 Columns In All The Tables

Oct 21, 2015

In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...

select top 10 * from ProductIdList

View 6 Replies View Related

Transact SQL :: How To Combine 4 Tables

Jul 6, 2015

I have 4 table 

TBLA
id 
1
2
3
----

TBLB
ID COUNT
1 2
2 2
3 2
----

TBLC
ID COUNT
1 2
2 2
----

TBLD
ID COUNT
1 3
2 3

NEED TO GET OUT PUT LIKE BELOW

ID aCOUNT bCOUNT cCOUNT dCOUNT
1 2
2 3
2 2
2 2
3 2

View 2 Replies View Related

Transact SQL :: Getting Correct Results From Two Tables?

Oct 5, 2015

We have two tables. 

Table1:
Servers | Numbers
------------
Server1 | 1
Server1 | 2
Server1 | 3
Server2 | 1
Server2 | 2
Server2 | 4
Server3 | 2
Server3 | 5
Server3 | 9
Server3 | 7
.....

Table2:
         | Numbers
-----------
NULL | 1
NULL | 2
NULL | 3

I need to select Server1, Server2, Server3 and other servers that does not have correct value in Table2. Results should return server name and number that server does not have like:

Server2 | 3
Server3 | 1
Server3 | 3

Table1 is updated time to time, Table2 - static table. The best would be to avoid loop or cursor. Is that possible to get these results in one query?

View 4 Replies View Related

Transact SQL :: Missing Records In One Tables

Oct 11, 2015

I want to know one small query..

id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did 
10 to 

I deleted some records now my table have below mentioned rows..

id Name
1 hi
2 how
4 you
6 can
8 not
10 to 

I want to know  the missing records in my table.

OUTPUT IS. 3,7,9

how can  i do that using sql query.

View 11 Replies View Related

Transact SQL :: Global Temp Tables

Jun 3, 2015

I have two global temp tables in my stored procedure.Is it possible for me to make the execution of a stored procedure dynamic. Based on a flag parameter value I should get the result set from first temp table  and viceversa ex: If my flag is 0.I should be able to get the result set from ##temp1 (select * from ##temp1).If my flag is 1.I should be able to get the result set from ##temp2 (select * from ##temp2).

View 4 Replies View Related

Transact SQL :: How To Retrieve All Tables From A View

Jun 29, 2015

We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?

View 6 Replies View Related

Transact SQL :: Query 2 Tables Using Where Exist

Jun 2, 2015

I need to show maxInspectionDate in my result but I can't figure out how to do it.

SELECT E.equipmentID, E.assetNumber, E.T5Code , E.InspectionDuration, E.Description,
E.AssetType, E.WorkingLimits
FROM Equipment E
WHERE EXISTS
(SELECT t.equipmentID, r.maxInspectionDate
FROM (
SELECT equipmentID, MAX(nextInspectionDate) as maxInspectionDate
FROM equipmentInspection
GROUP BY equipmentID
) r
INNER JOIN equipmentInspection t
ON t.equipmentID = r.equipmentID AND t.nextInspectionDate = r.maxInspectionDate)

View 9 Replies View Related

Transact SQL :: Merge Two Columns From Different Tables?

Oct 26, 2015

I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale. 

declare
@start datetime,
@end datetime,
@campaignId uniqueidentifier,
@campaignName varchar(250),
@segment varchar(50)

set @start = '2015-10-07'
set @end = '2015-10-20'

[code]....

how to merge these two columns from #sales and #hours tables without duplication.

View 6 Replies View Related

Transact SQL :: DDL Trigger For Specific Tables

Oct 14, 2015

How to set ddl triggers for specific tables, instead of on entire database.

View 2 Replies View Related

Transact SQL :: Sum Values By Grouping Name From Two Tables

Nov 6, 2015

I have two tbles that have ItemName and their bill amount

a) tblLunch

which shows records like below

Invoice   Item                          Amount  
--------------------------------------------
1             COFFEE                       1000.00 
2             TEA                          2000.00
3             ICE CREAM                1000.00

b) tblDinner

which shows records like below

Invoice   Item                      Amount  
------------------------------------------------------------
1             COFFEE                  1000.00 
2             TEA                        2000.00
3             PASTA         1000.00

I want to perform a query that should SUM Amount Columns by Grouping the Item from both the tables, so we could get the following result

Item                      Amount  
------------------------------
COFFEE                  2000.00 
TEA                        4000.00
ICE CREAM           1000.00
PASTA                    1000.00

View 3 Replies View Related

Transact SQL :: How To Join 2 Tables Have Different Foreign Key

Apr 23, 2015

i have two tables Table one have 2 columns id and value
     
id value

1 Dell

2 Hp

B2 Hp-mini
B3 Hp-lapTop

3 Acer

the second table have 3 clomuns id,name,idTable

1 TeaBou B
2 Mark B
3 Jack 1
4 Piere 2
5 Jean 2
6 Mark 3

i tried this query

select*from table1 t1 ,table2 t2
where t1.id=t2.idTable

i had some data but also i need to include the person who have the B product.

View 6 Replies View Related

Transact SQL :: Comparing Records Between Two Tables

Sep 29, 2015

I need to compare records between two tables. There is no ID in the tables to do a simple join between them. So, what I'm looking for is: get the first record from table1 and read all record from table2 and give me back the most similar record. The String Distance is a predefined function.

Select a.table1
,b.table2
from table1 a, table2 b
where StringDistance (''a.table1,'b.table2') >90

View 4 Replies View Related

Transact SQL :: SELECT From Multiple Tables

Jul 13, 2015

I have the following two tables....

tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date

I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.

View 11 Replies View Related

Transact SQL :: Joining 2 Similar Tables

Sep 28, 2015

What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.

CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,

[code]....

The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.

So here is a query that I attempted, but gave undesirable results:

SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI

Here is some sample data:

EnterVolume
TrialID             SBOI           OwnerBOI         TimeOfEvent
1                     A                 D                      5
1                     A                 D                      2000

[code]....

View 8 Replies View Related

Transact SQL :: How To Create A 0-Many Relationship Between Two Tables

May 25, 2015

I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.

View 6 Replies View Related

Transact SQL :: Match Items Between Two Tables

Jul 6, 2015

I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.

Then I have a table usergroups with two columns (groupid, user) with example:

1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott

I want to find groupid 2 and 4 because they exactly match the content of @tbl.

View 8 Replies View Related

Transact SQL :: Cross Reference 2 Tables

Jul 28, 2015

I have 2 tables, OBJECTS and LINKS both have common field(OBJECT_ID).

I need to update certain records in table OBJECTS only if they meet certain criteria in table LINKS.

How do I go about doing this..???

View 8 Replies View Related

Transact-sql Code To Display Preformatted Tables

Mar 3, 2006

altimebest1 writes "Please show sql codes to creating a table showing Value and quantity under a heading, the month the data was collected or the value and quantity in the 1st, 2nd, 3rd and 4th quarter values under a heading Year.
My source table is:
comCode char 7
monthCode char 2
year char 4
countryCode char 3
quantity int
value money
insurance money
freight money
i also have a lookup table for:


Commodities Countries
comCode char 7 countryCode char 3
comDescriptions varchar 255 countryName varchar 255


the format of the table i wish to create

Com Commodity Description Jan
Code Country of Destination ValueQuantity


Thank you for helping me and I been a constant visitor to your site and it's wonderful and gives great help to sql enthusiasts."

View 1 Replies View Related

Transact SQL :: How To Find A Non Matched Record In 2 Tables

Jul 15, 2015

I have 2 tables .Lets Say tableA and tableB.Both Have Columns ClaimNumber,Amount. Now, to get the matched records for these 2 tables, i wrote the following query Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount = B.Amount This query works perfectly fine and gives me only matching records, however if i want to have records which match with ClaimNumber and not with Amount i wrote something like this

Select * from tableA A Inner Join tableB B on A.ClaimNumber = B.ClaimNumber and A.Amount <> B.Amount.

And this query produces wrong results, its giving me match and also non match records.

how to write a query for my non match condition?

View 5 Replies View Related

Transact SQL :: Compare A Table Against Multiple Tables

Jul 16, 2015

I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)

Later for some other purpose, downloaded the same day's data into five small tables.

Now when I compare the total row count of  Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match. 

So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.

View 5 Replies View Related

Transact SQL :: Check No Inserts Occurring In Tables

Sep 14, 2015

I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?

Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D

View 9 Replies View Related

Transact SQL :: Search A String Across Multiple Tables

Jul 29, 2015

Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved