Yuri55 (10/12/2016)

---

You mean it can be (let say, potentially) bad query itself? I know it's not the best and we have some ideas how to improve it but even did...

mcampoam (10/12/2016)

---

Hi,

Any difference in results or performance between the 2 examples? Names of tables/columns are generic below:

#1

update customer t1

set column_1 = x.value

FROM

(select id

, case

when column_2 is null ...

Can you post the actual execution plan of the update run in dev as a .sqlplan attachment please? As Jeff has pointed out, there may be tuning opportunities.

wendy elizabeth (10/11/2016)

---

In a t-sql 2012, I want to place the new lockid values obtain from the first insert statement (for the lock table) indiviudally into the second...

Grant Fritchey (10/11/2016)

---

DesNorton (10/11/2016)

---

Grant Fritchey (10/11/2016)

---

What everyone else has said, plus.

If you're going to attempt to tune the query, you absolutely have to have the execution plans. There is no...

It shouldn't be too hard to replace this awful mess with something sensible and streamlined.

Here's an attempt to rewrite cte2 along more conventional lines. It's about 10 minutes worth

of effort...

This query has more repeats than the Disney channel. It's pointless attempting to tune it as it is. Pass it on to a professional TSQL dev to rewrite from scratch....

Jeff Moden (10/8/2016)

---

We all know the reason for the given answer... she simply doesn't know any better method even after she's been shown how to...

I guess it could be, if there's a...

I didn't say that. I said that if you query for any school year, you will...

Eirikur Eiriksson (10/7/2016)

---

Quick suggestion

😎

USE TEEST;

GO

IF OBJECT_ID(N'dbo.temp') IS NOT NULL DROP TABLE dbo.temp;

create table dbo.temp(Companyid varchar(10) not null,companyname varchar(100) not null,DateFrom datetime not null, DateTo datetime null);

INSERT INTO temp values('A1','A...

Your request is almost certainly possible, however your own difficulty with it hints that there may be a simpler option. What about this?

SELECT t.*

FROM #temp2 t

WHERE t.DateTo IS NULL...

Super_Grover (10/7/2016)

---

Okay, so I created and (partly) filled the three lookup tables and took a go on Scott's approach.

Turned out I was thinking waaay too complex. I ended up with...

wendy elizabeth (10/6/2016)

---

The school year is used. These 3 tables are joined to the following tables to determine what the school year is.

Note: I did not list the following...

This becomes a little more interesting if you increase the complexity of the query:

CREATE PROCEDURE [dbo].[test04]

(@NUM INT)

AS

SET NOCOUNT ON;

IF (@NUM > 0)

BEGIN

SELECT p.n --

FROM dbo.Numbers NM

INNER...