Thursday, September 26, 2013

[T-SQL] How does Greater than operator works in Joins

[T-SQL] How does Greater than operator works in Joins

How does Greater than operator works in Joins

Posted: 25 Sep 2013 02:39 PM PDT

Hello,I have writing SQL Queries for sometime now and writing inner joins, but always used the equal to operator(=) for joining 2 tables. Just recently I came across a SQL written by my co worker and he is uisng greater than and equal to operator (>=) to join tables. How in the earth you can join 2 tables with this operator(>=). I always thought of joining 2 tables if they have a matching value in the columns and using the equal operator to do that.He is doing some kind of a rolling average and using this (>=) operator.can someone please explain, how exactly this (>=) operator works in joins.Thanks.

how to replace this cursor with set based solution?

Posted: 25 Sep 2013 08:05 AM PDT

I've got a cursor script that I want to replace with a set based solution because the cursor script takes a long time to run. All I've got is the cursor script, for which I've reverse engineered the DDL to solve this problem (without success). The cursor script is the actual script that is running in a production environment, and the DDL is something I've created (for myself) to work out a set based solution. I've posted it all below and I would so much appreciate if you took a look. The cursor creates an array variable to hold a list of names (LabC1, LabC2, etc) and then fetches the name one by one and compares its value to a value found in the 'name' column of the Machines table. I hope a copy of the script and DDL will make this explanation clearer. Here's the cursor script: [code="plain"]DECLARE @benches TABLE (BenchName varchar(5))INSERT INTO @benchesVALUES ('LabC1'), ('LabC2'), ('LabC3'), ('LabC4'), ('LabD1'), ('LabD2'), ('LabD3'), ('LabD4'), ('LabE1'), ('LabE2'), ('LabE3'), ('LabE4'), ('LabE5'), ('LabE6'), ('LabE7'), ('LabE8') DECLARE @runsByBench TABLE (BenchName varchar(5), NumberOfRunsOnBench int, LastRun datetime) -- for each value in the @benches array return the count of runs and the last createdate associated with that run. DECLARE toQuery CURSOR LOCAL FAST_FORWARD FOR SELECT b.BenchName FROM @benches b OPEN toQuery -- loop through all of the scripts DECLARE @bench varchar(5) FETCH NEXT FROM toQuery INTO @bench WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @runsByBench (BenchName , NumberOfRunsOnBench , LastRun ) SELECT @bench as BenchName, COUNT(*) as NumberOfRunsOnBench, MAX(CreateDate) as LastRun FROM (select r.runid, r.creatorid, r.createdate, r.name, r.enddate, count(*) as MachinesInRun from Runs r join runs_machines rm on rm.runid=r.runid join Machines m on m.machineid=rm.machineid-----HERE'S WHERE IT COMPARES THE VALUE M.NAME TO THE VALUE FETCHED BY THE CURSOR-------------- where m.name like (@bench +'[0-9][0-9][0-9]')---------------------------------------------------------------------------------------------------------- and r.createdate > dateadd(M,-3,getdate()) group by r.runid, r.creatorid, r.createdate, r.name, r.enddate ) as foo FETCH NEXT FROM toQuery INTO @bench END CLOSE toQuery DEALLOCATE toQuery SELECT * FROM @runsByBench[/code]and here's the DDL I created for the tables used by the SQL script to work on the problem:[code="plain"]create table Runs (RunID int,CreatorID int,CreateDate datetime,EndDate datetime,Name varchar(20),MachineID int);insert into Runsvalues(1, 201, DATEADD(mi,-3,getdate()-1), DATEADD(mi,+60,getdate()-1), 'LABC1521', 521),(2, 202, DATEADD(mi,-3,getdate()), DATEADD(mi,+75,getdate()), 'LABC2681', 681),(3, 203, DATEADD(mi,-3,getdate()), DATEADD(mi,+63,getdate()), 'LABC3123', 123),(4, 204, DATEADD(mi,-3,getdate()), DATEADD(mi,+65,getdate()), 'LABC4765', 765),(5, 205, DATEADD(mi,-1,getdate()), DATEADD(mi,+78,getdate()), 'LABC1521', 521);create table Machines (MachineID int,Name varchar(20));insert into Machinesvalues(521, 'LABC1521'),(681, 'LABC2681'),(123, 'LABC3123'),(765, 'LABC4765');create table Runs_Machines(Runid int,MachineID varchar(20));insert into Runs_Machinesvalues(1, 521),(2, 681),(3, 123),(4, 765),(5, 521);create table RunsByBench(Benchname varchar(20),NumberOfRunsOnBench int,LastRun datetime);[/code]the query inside the script will work against the upper DDL as long as the you hardcode the m.name value:select r.runid, r.creatorid, r.createdate, r.name, r.enddatefrom Runs rjoin runs_machines rm on rm.runid=r.runidjoin Machines m on m.machineid=rm.machineidwhere m.name like 'LabC1%'I would like to put the array into a #temp table and compare the results of the query to it. But I have failed. I tried a correlated subquery, but without a unique key, I discovered it isn't the solution. Can someone help me find the T-SQL equivalent for the above cursor script?Thanks!

Find a character in string

Posted: 25 Sep 2013 09:18 AM PDT

Hi,Is there a sql function that will look for a character within a string and return a 1 or 0 if this character is showing?For example if the below sql select statement has a chratcer of '@' i want it to show a result of 1?[code="other"]Select 'AGDJS@JDJD' --will show as 1Select 'AGDJSJDJD' --will show as 0[/code]I just want to know if there is a current sql function that does this or if anyone can think of a case statement to get round this.I don't want a User Defined Function if possible.Thanks

select truncates varchar(max) column(s)

Posted: 25 Sep 2013 06:13 AM PDT

Hi all,SqlServer 2008R2.Anyone of you ever dealt with an issue when you're trying to concatenate several columns (including varchar(max)), the result is truncated?I am going to provide an example of it. Sorry for the long text.[code="sql"]--drop table A;CREATE TABLE A( [col_1] [nvarchar](30) NOT NULL, [col_2] [varchar](256) NULL, [col_3] [varchar](max) NULL, [col_4] [varchar](max) NULL);insert into A (col_1, col_2, col_3, col_4)values('AAA-111' ,'Vendor' ,' The duties and responsibilities of a Database Administrator (DBA) make a long and dynamically changing list, ranging from offering query tuning advice, to cutting stored procedures, all the way through to system process design and implementation for high availability. A DBA''s tasks, from day to day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error or disaster. This means that if a database, for whatever reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA''s responsibility to restore that database to the state it was in before the problem occurred, or as close as is possible. Of course, this doesn''t mean that a DBA is required to restore a database each and every day, just that, if disaster does strike, the DBA must be prepared to deal with it, regardless of when or why it occurs. If a DBA isn''t prepared, and significant data is lost or databases become unavailable to end-users for long periods of time, then that DBA probably won''t be in their job for too long. This is why a good, and tested, SQL Server backup and restore plan must be at the top of every administrative DBA''s list of tasks. ' ,' Each of these factors will help decide the types of backup required, how often they need to be taken, how many days'' worth of backup files need to be stored locally, and so on. All of this should be clearly documented so that all parties, both the DBAs and application/ database owners, understand the level of service that is expected for each database, and what''s required in the plan to achieve it. At one end of the scale, for a non-frontline, infrequently-modified database, the backup and recovery scheme may be simplicity itself, involving a nightly full database backup, containing a complete copy of all data files, which can be restored if and when necessary. At the opposite end of the scale, a financial database with more or less zero tolerance to data loss will require a complex scheme consisting of regular (daily) full database backups, probably interspersed with differential database backups, capturing all changes since the last full database backup, as well as very regular transaction log backups, capturing the contents added in the database log file, since the last log backup. For very large databases (VLDBs), where it may not be possible to back up the entire database in one go, the backup and restore scheme may become more complex still, involving backup of individual data files, for filegroups, as well as transaction logs. All of these backups will need to be carefully planned and scheduled, the files stored securely, and then restored in the correct sequence, to allow the database to be restored to the exact state in which it existed at any point in time in its history, such as the point just before a disaster occurred. ');[/code]When I run the following, all columns contain the right data.[code]select * from A;[/code]However, when I try to concat the fields, the output is truncated[code]select col_1 ,'Alert: Blah, blah, blah ....' ,'Attention:' + REPLICATE(' ', (20 - LEN('Attention:'))) + CASE col_2 WHEN 'Vendor' THEN 'Not our fault' ELSE 'Our fault' END + CHAR(13)+CHAR(10) +'Col_1:' + REPLICATE(' ', (20 - LEN('Col_1:'))) + col_1 + CHAR(13)+CHAR(10) +'Date:' + REPLICATE(' ', (20 - LEN('Date:'))) + CONVERT(VARCHAR,GETDATE()) + CHAR(13)+CHAR(10) +'Out for:' + REPLICATE(' ', (20 - LEN('Out for:'))) + CONVERT(VARCHAR,DATEDIFF(MINUTE,GETDATE()-1,getdate()))+ ' minutes' + CHAR(13)+CHAR(10) +'Description:' + REPLICATE(' ', (20 - LEN('Description:'))) + 'Is this a bug???' + CHAR(13)+CHAR(10) +'Col_3:' + REPLICATE(' ', (20 - LEN('Col3'))) + col_2 + CHAR(13)+CHAR(10) +'Col_4:' + REPLICATE(' ', (20 - LEN('Col_4'))) + col_3 + CHAR(13)+CHAR(10) ,'' ,0 ,''from A;[/code]Anyone?Thanks,

Moving all Jobs on a server

Posted: 25 Sep 2013 06:01 AM PDT

Is there a way to get a SQL Script, from my old server, that has all of its jobs? So I can then run the SQL on the new server and not have to manually re-create all of my jobs?

Locks when using Service broker

Posted: 25 Sep 2013 04:38 AM PDT

I am using Service broker to execute a stored procedure. In the stored procedure 3 tables are frequently used to insert/update and delete. The problem I am facing is when I use service broker to run the procedure there are too many locks on these tables.Has anyone faced a similar issue ? How should I go about troubleshooting it ?

Top 50 based on Sum

Posted: 25 Sep 2013 01:41 AM PDT

Hi all based on my query below, how do I select just 50 based on a Sum of duration matching the criteria defined?SELECT Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Reason, ReasonReq, Section, Waittime, Weekend, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Operator_Name IS NOT NULL) AND (Operator_Name <> 'None') AND (Docket_Category IS NOT NULL) AND (Contract = 1) AND (Docket_Category NOT LIKE '%Out Of SLA%')Thanks

cursor question

Posted: 16 Sep 2013 06:14 AM PDT

Hi guys,Can i load a cursor from a procedure?Like DECLARE cursor_importedPatients CURSOR FOR EXEC procedureThanks!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog