There's an unpleasant XML conversion that may work for you

WITH source AS (

SELECT Deptid,Dept_name,location,HASHBYTES('MD5',CAST(Deptid AS VARCHAR(10)) + Dept_name + location) AS col

FROM department)

SELECT Deptid,Dept_name,location,

...

Try this

SELECT *

FROM #Machine m

WHERE m.MachineTypeFk = 99

AND m.OnHand = 0

AND m.Enabled = 0

AND EXISTS(SELECT * FROM #MachineBranch mb WHERE mb.MachineFk = m.MachinePk)

...

Here's another version, this one using SQL Server 2012 LEAD

WITH Source AS (

SELECT *

FROM

(VALUES

(1, 'A',convert(datetime,'20150101'),convert(datetime,'20150331')),

(2, 'B','20150115','20150215'),

(3, 'C','20150215','20150315'),

(4, 'D','20150115','20150315'),

(5, 'E','20150401','20150415')

) AS TimeIntervals(OrderId, status, DateStart, DateEnd)

),

Starts AS (

SELECT...

The problem is due to two intervals having a one day gap between the end of one and the start of the next, I think these can simply be excluded.

Change

CROSS...

It was getting the wrong values for ordereid and status in some circumstances.

tomek tomek (2/11/2015)

First of all, thank you all for your input. It is invaluable.

@venoym

a) you solutions with numbers looks very interesting, but as Dwain suggested I had problems...

This works with your data, not very efficient though

WITH Source AS (

SELECT *

FROM

(VALUES

(1, 'A',convert(datetime,'20150101'),convert(datetime,'20150331')),

(2, 'B','20150115','20150215'),

(3, 'C','20150215','20150315'),

(4, 'D','20150115','20150315'),

(5, 'E','20150401','20150415')

) AS TimeIntervals(OrderId, status, DateStart, DateEnd)

),

Starts AS (

SELECT a.DateStart...

You can nest CASE expressions

,CASE WHEN com.completion_date IS NOT NULL AND dim.DayName <> 'Saturday'

THEN CASE WHEN DATEDIFF(d, com.current_task_target_date,com.completion_date) < non1.NoWorkDays THEN 0 ELSE DATEDIFF(d, com.current_task_target_date,com.completion_date) - non1.NoWorkDays END

WHEN com.completion_date...

Use a CASE expression

CASE WHEN MyColumn < 0 THEN 0 ELSE MyColumn END

Because there's a lot...

Seems an odd representation of a hierarchy but this gives you the correct results

WITH CTE AS (

SELECT la.label AS ancestor,

...

Have you tried running my last query?

This is a bit of a guess...

select t1.id,

stuff((select ','+x.r.value('./text()[1]','nvarchar(max)') as "text()"

from tblxmldata t2

cross apply t2.xmltext.nodes('/associatedText/value') as...

Try this

select stuff(

(select ','+x.r.value('./text()[1]','nvarchar(max)') as "text()"

from tblxmldata

cross apply xmltext.nodes('/associatedText/value') as x(r)

for xml path('')),1,1,'')

Assuming xmltext is of type XML

select x.r.value('./text()[1]','nvarchar(max)') as AssociatedText

from tblxmldata

cross apply xmltext.nodes('/associatedText/value') as x(r)