Looks like a gaps and islands problem.

DECLARE @t TABLE(PERSON CHAR(6), DT DATE, SHIFTTYPE VARCHAR(8))

INSERT INTO @t(PERSON, DT, SHIFTTYPE)

VALUES

('000001','20121001','WORKING'),

('000001','20121002','SICK'),

('000001','20121003','SICK'),

('000001','20121004','DAYOFF'),

('000001','20121005','SICK'),

('000001','20121006','WORKING'),

('000001','20121007','WORKING'),

('000001','20121008','SICK'),

('000001','20121009','SICK');

WITH CTE AS (

SELECT PERSON, DT, SHIFTTYPE,

...

SQL Server 2012 only...

LEAD(FN) OVER(ORDER BY ID),LN

Melanie Peterson (11/14/2012)

---

Ten Centuries - thanks, but this is still just comparing COUNTs. I need to verify for DID = 15 not just that there are 2 rows where AID's...

Try this

DECLARE @Table1 TABLE(AID INT, IsStaffTask INT)

INSERT INTO @Table1(AID,IsStaffTask)

SELECT 1, 0 UNION ALL

SELECT 2, 1 UNION ALL

SELECT 3, 1 UNION ALL

SELECT 4, 0;

DECLARE @Table2 TABLE(DID INT, AID INT)

INSERT INTO @Table2(DID,AID)

SELECT...

You can use INSERT INTO thetable DEFAULT VALUES;

Good to see a question covering some of the new 2012 features. Thanks.

Try this

DECLARE @myTable TABLE([Option] VARCHAR(10), Start TIME, Finish TIME)

INSERT INTO @myTable([Option], Start, Finish)

VALUES('Option1', '07:30:00' ,'07:35:00'),

('Option2', '07:35:00' ,'07:50:00'),

('Option3', '08:10:00' ,'08:20:00'),

('Option1', '08:30:00' ,'08:35:00'),

('Option3', '08:35:00' ,'09:00:00');

WITH CTE AS (

SELECT [Option], Start, Finish,

...

Use CROSS APPLY

DECLARE @xml XML =

'<ROOT>

<SPECIALNEEDS>

INCLUDE

<NEED>

BLIND

</NEED>

<NEED>

...

DECLARE @Category TABLE(CategoryID int,ParentCategoryID int, Name nvarchar(1000))

INSERT INTO @Category(CategoryID,ParentCategoryID,Name)

VALUES

(1 , NULL, 'A' ),

(2 , ...

Your end tag is wrong

'/<Data Extract>'

should be

'</Data Extract>'

Can you post some DDL and sample data?

Jeff Moden (11/5/2012)

---

Mark-101232 (11/5/2012)

---

You don't need to use recursion to find the ranges of 1753s starts and their lengths. Try this instead

DECLARE @CutOffDate DATETIME = '2009-01-01';

WITH CTE AS (

SELECT ID,...

You don't need to use recursion to find the ranges of 1753s starts and their lengths. Try this instead

DECLARE @CutOffDate DATETIME = '2009-01-01';

WITH CTE AS (

SELECT ID, VisitDate,

...

Try swapping Table1.SONumber and Table2.SONumber

Table2.SONumber like '%' + Table1.SONumber + '%'

DECLARE @t TABLE(AssetId INT,FailureId INT,RootCauseId INT)

INSERT INTO @t(AssetId,FailureId,RootCauseId)

VALUES

(1122, 1014, 1016),

(1122, 1019, NULL),

(1122, 1029, 1016),

(1122, 1031, NULL);

SELECT AssetId,FailureId,RootCauseId,

CASE WHEN SUM(CASE WHEN RootCauseId IS NULL...