Try this

WITH CTE AS (

SELECT cust_Id,sDate,budget,

ROW_NUMBER() OVER(PARTITION BY cust_id ORDER BY sDate) -

ROW_NUMBER() OVER(PARTITION BY...

This should work for you

WITH CTE AS (

select *,

row_number()over (ORDER BY RECORD_ID) -

row_number()over (PARTITION BY EXTRAS...

Using Gianlucas setup

WITH Grouped AS (

SELECT EmpCode,[Time],InOut,

ROW_NUMBER() OVER(PARTITION BY EmpCode ORDER BY [Time]) -

ROW_NUMBER() OVER(PARTITION BY...

coalesceuk (3/12/2015)

---

dwain.c Thanks - I will have a read!

Mark Thanks for the fix, which works with my example data.

I've been testing both solutions with my real-world data. Both take...

coalesceuk (3/11/2015)

---

dwain.c, the cross apply does remove a lot of the table scans and works in 2005. I find it harder to understand/read what a cross apply is doing just...

SELECT

(SELECT CONVERT(CHAR(8),GETDATE(),112) AS "FileDate" FOR XML PATH('header'),TYPE),

(

... my previous query here...

) AS "body"

FOR XML PATH('Records'),TYPE;

As far as I know you have to add the encoding '<?xml version="1.0" encoding="UTF-8"?>' in manually...

coalesceuk (3/10/2015)

---

I think I have a solution, and by changing my example data to split one meeting into 2, I can break Mark Cowne's suggestion

IF OBJECT_ID('tempdb.dbo.#EmployeeProject') IS NOT NULL DROP...

See if this helps

SELECT t1.continent_id,

t1.continent_Name,

t1.continent_surface_area,

(SELECT t2.country_id,

...

This works with your sample data

WITH StartsAndEnds(StartEnd,ProjectId) AS (

-- Start points which overlap the same number of meetings as projects

SELECT s.MeetingStart,s.ProjectId

...

Great question, got it wrong but definitely learned something new.

Not clear what you're asking, but maybe this

select MRN,

max(case when Problem='Congestive Heart Failure' then 'x' else '' end) as 'CHF',

max(case when Problem='Chronic Obstructive Pulmonary Disease' then 'x' else '' end)...

Here's another (using Luis's setup)

WITH Results(SetId1,SetId2) AS (

SELECT a.SetId,b.SetId

FROM SetsItems a

INNER JOIN SetsItems b ON b.SetId > a.SetId

...

I suspect you'll need to use some sort of running total on the Duration column. Search for "quirky update" for a very fast method, there's plenty of good folks out...

Are you using SQL Server 2012? If so you can do this

select Item,

Sequence,

StartTime,

...

SELECT COALESCE(s1.Name,s2.Name,s3.Name) AS name,

s1.val1,

s2.val2,

s3.val3

FROM @sample1-2 s1

FULL OUTER...