[T-SQL] Complex SQL QUERY with DateDIFF |
- Complex SQL QUERY with DateDIFF
- How to create a query which kill CPU?
- Why do some of these procedure calls have syntax errors?
- Single Update Query - Required
- DateDiff function help
- query returns empty row
- Data Insertion into Access DB using T-SQL
- Creating Stored Procedure
| Complex SQL QUERY with DateDIFF Posted: 25 Feb 2013 12:16 AM PST Hi all,I need to script a query to discard access logs that not exceeding a predetermined margin (eg. 2 seconds). That is, if each record was made by a user on a especific date / time, I don't want to get those who its date / time does not exceed the range compared with the date / time of the previous record. I think an example is clearer.Example:LogIndex, UserID, Date / Time1. 01551, 20.02.2013 17:41:45.0002. 01551, 20.02.2013 17:41:45.900 *3. 01551, 20.02.2013 17:41:46.150 *4. 01551, 20.02.2013 20:41:47.0005. 01552, 02/20/2013 17:42:45.0006. 01552, 20.02.2013 17:42:46.000 *7. 01552, 02/20/2013 19:45:45.000*: Records to discard because its date / time does not exceed the margin of 2 seconds over the previous record. In the first case two records should be discarted because both not exceed this margin.Here's the code that creates the temporary table and adds the previous records to test:CREATE TABLE # TEMP (LogIndex int, UserID nvarchar (10), LogTime datetime)insert into # temp select 1, '01551 ', '20 / 02/2013 17:41:45.000'insert into # temp select 2, '01551 ', '20 / 02/2013 17:41:45.900'insert into # temp select 3, '01551 ', '20 / 02/2013 17:41:46.150'insert into # temp select 4, '01551 ', '20 / 02/2013 20:41:47.000'insert into # temp select 5, '01552 ', '20 / 02/2013 17:42:45.000'insert into # temp select 6, '01552 ', '20 / 02/2013 17:42:46.000'insert into # temp select 7, '01552 ', '20 / 02/2013 19:45:45.000'select * from # tempDROP TABLE # tempThanks in advance! |
| How to create a query which kill CPU? Posted: 12 Mar 2013 12:28 AM PDT HiFor some tests I need to create a query or set of queries which overloads my test machine which is quite powerful, two 6 cores CPUs and 50GB RAM. I was trying to create multiple joins, aggregates, UDFs but nothing is able to make the CPUs suffering. Also linked servers usage which influenced CPU greater degree than previous combinations haven't even loaded CPU till 50%. Do you think it's it possible to overload CPUs on such powerful machine? Do you know some tips to create really heavy query which can kill machine :-D ? |
| Why do some of these procedure calls have syntax errors? Posted: 11 Mar 2013 10:49 PM PDT I am using 2012 (but I think I have had similar issues with 2005 and 2008)Why is it that the last two procedure calls, in the commented section, have syntax errors?[code]begin try drop procedure dbo.usp_WMELog end try begin catch end catchgocreate Procedure dbo.usp_WMELog @event varchar(MAX)asinsert into tblWMELog (event) Values (@event)/*declare @t intdeclare @s varchar(10)set @t = 3exec usp_WMELog @tset @s = cast (@t as varchar(10))exec usp_WMELog @sexec usp_WMELog cast (@t as varchar(10))exec usp_WMELog convert(varchar(1), @t)select * from tblWMELog*/[/code]Thanks. |
| Single Update Query - Required Posted: 11 Mar 2013 10:29 PM PDT Table 1:[code="sql"]Sno SID Sname1 Null A2 Null B3 Null C4 Null D5 Null E[/code]Table 2:[code="sql"]ano aID aName1 55 AA2 32 BB3 53 CC4 10 DD5 10 EE[/code]Requirement: I need to update Table 1 - Column SID values with Table 2 - Column aID in a single update queryOutput data should be:[code="sql"] [b]Sno SID Sname1 55 A2 32 B3 53 C4 10 D5 10 E[/b][/code] |
| Posted: 11 Mar 2013 08:20 AM PDT Hi Friends,I'm new to TSQL and in my code I'm using -datediff(day,[WorkForce_JobPosting].[Job Posting End Date_JP] ,[WorkForce_WorkOrder].[Work Order End Date_WO]) > 0the above code if I use with single quotes round 0 still gives me the same result-ie,-datediff(day,[WorkForce_JobPosting].[Job Posting End Date_JP] ,[WorkForce_WorkOrder].[Work Order End Date_WO]) > '0'Hence wanted to know though the output of DateDiff is a integer number but using the integer within quotes also does not fail the result ?thanksDhananjay |
| Posted: 11 Mar 2013 08:31 PM PDT hi all, please find the following code[code="sql"]DECLARE @intid as varchar(100) ='''BSEC0002''' + ',' + '''BSEC0001''';PRINT @intid;DECLARE @qry as varchar(500) ='SELECT * FROM mstinstrument where instrumentid IN (' + @intid +')';print @qry;exec (@qry);SELECT * FROM mstinstrument where instrumentid IN (@intid);[/code]here exec @qry returns the value of two rowsbut the select statement which is exactly the same query mentioned in @qry variable is returning empty row, any clues. |
| Data Insertion into Access DB using T-SQL Posted: 11 Mar 2013 07:53 AM PDT Hi Guys,I am trying to insert data from SQLServer into an access database,using T-SQLI used OPENROWSET to Select the data from access into SQLServer, it worked fineQUERY:SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','"ServerName"';'admin';'',TableName);My table has columns with the changing month namesis there any way, i could do some sort of SELECT INTO an access DB using T-SQL ..?Note : This is an automated report, i cannot use Import Export Wizard, there is no SSIS installed on the machine |
| Posted: 11 Mar 2013 03:48 PM PDT Hi,I have below table:ID URL1 https://google.com2 https://facebook.com3 https://yahoo.com4 https://gmail.comI am trying to create a procedure where I take the input for URL column.I am defining 5 paramters for procedure, url1, url2, url3, url4, proc_msgI need to do below checks:If value is null, existing value should not change. (means table is not updated)- I was using NVL2 function for this.If value is provided, check if value starts with 'https://'. If it is valid update the table. Else display msg- invalid url.User may not i/p all values at one time. he may update only 1 url or may be more at a particular time. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment