abhas (9/18/2014)

---

Yes Erirukar,

There are more rows for CanNum as below: and in such case its failing. I want to compare only latest two.

I can Add one more column into table...

Use dynamic SQL. No static query can produce varying number of columns .

Please provide more details on your requiements to get more assistance. What is the proc input? Is it...

Overlap (x,y) == (x.Start <= y.End AND y.Start <= x.End)

Here overlap means intervals x and y have at least one common point. Intervals are closed, ie boundaries are included. Otherwise...

Luis Cazares (9/17/2014)

---

The simplest version I could think of.

EDIT: Unless someone proves me wrong, it cannot be made without functions or a CASE statement. At least not in SQL Server...

Cannt figure out why jobs 5 and 7 are to be in the result set while they overlap with job 2.

The query must ignore overlapping when ... what?

See above. Tried to edit and posted once again instead. 🙁

((1-Flag)*Qty*(CF2/CF1) + Flag*Qty) * nullif (1-abs(sign((1-Flag)*Flag)),0)

Qty*(CF2/CF1) when Flag==0,

Qty when Flag==1,

NULL otherwise.

Try

create table #test (Code varchar(1000))

insert into #test (Code)

select 'Q074;Q221;Y714;M459;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;' union all

select 'Q233;Y752;Z942;Q124;Q074;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;' union all

select 'Q233;Y959;Z943;Q084;Q241;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;' union all

select 'Q074;Q233;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;' union all

select ';Q221;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;' union all

select 'Q074;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'

select code

from #test

cross...

Amstrong numbers. OK, Will try to remember it. 🙂

Minor code improvement. SUBSTRING() : If start_expression is greater than the number of characters in the value expression, a zero-length expression is...

Discarded my post as reiterating previous findings.

TomThomson and Hugo Kornelis thank you for yours comments.

barry.mcconnell (9/8/2014)

---

I got it right but for a different reasoning. Since the HumanResources schema wasn't specified, the synonym would be created for the non-existant dbo.Employee table and the query would...

Exactly. Remember Oracle's ORDER BY ... NULLS FIRST.

ORDER BY must place...

Also note empty string may be rather lengthy 🙂

declare @user varchar(30)

set @user = char(0) + char(0) + char(0)

IF isnull (@user,'') != '' -- also try other discussed checks

BEGIN

...

Sean Lange (5/28/2014)

---

You could this without needing a join and windowing functions. Since you have two queries that are both just an aggregate a cross apply will make this a...

On logic error.

Having no PK and joining just by a single column to match the updated row may be considered as a logic error as well.