Try this

DECLARE @Products TABLE(ID VARCHAR(20) NOT NULL PRIMARY KEY)

INSERT INTO @Products(ID)

SELECT 'SCREWDRIVER' UNION ALL

SELECT '04738' UNION ALL

SELECT '04739' UNION ALL

SELECT 'DRILL400' UNION ALL

SELECT '256' UNION ALL

SELECT '257' UNION ALL

SELECT '258'...

Second rendition, performs better than the first.

WITH StartsAndEnds(StartEnd,Sdate,Edate,SRC,OrgNo) AS (

SELECT 'S' AS StartEnd,

Sdate,

DATEADD(day,-1,Sdate),

...

Jeff Moden (6/18/2011)

---

Actually, I was able to break the Mark's second rendition...

CREATE TABLE TEST( SRC varchar(6)not null,

OrgNo varchar(5)not null,

Sdate datetime Not nUll,

Edate datetime Not Null)

INSERT INTO TEST (SRC ,OrgNo ,Sdate...

Couple of possibilities

SELECT Customer,SUBJECT AS SubjectOfInterest,'' AS SubjectOfNOInterest

FROM @T

WHERE INTERESTED='True'

UNION ALL

SELECT Customer,'' AS SubjectOfInterest,SUBJECT AS SubjectOfNOInterest

FROM @T

WHERE INTERESTED='False'

ORDER BY Customer,SubjectOfInterest,SubjectOfNOInterest;

WITH SubjectOfInterest AS (

SELECT Customer,SUBJECT,ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY SUBJECT)...

Try this

DECLARE @T TABLE(ID INT,Customer INT,SUBJECT VARCHAR(10), INTERESTED VARCHAR(5))

INSERT INTO @T(ID,Customer,SUBJECT,INTERESTED)

SELECT 1, 1, 'SubjectA', 'True' UNION ALL

SELECT 2, 1, 'SubjectB', 'True' UNION ALL

SELECT 3, 1, 'SubjectC', 'False' UNION ALL

SELECT 4,...

WITH Grped AS (

SELECT ALLOTMENT_ROOM_ID,

ROOM_ID,

ROOM_DATE,

LIST_ROOM_STATUS,

...

DECLARE @T TABLE(RowID INT, Col1 CHAR(2), Col2 CHAR(2), Col3 CHAR(2), ErrorCD VARCHAR(10))

INSERT INTO @T(RowID,Col1,Col2,Col3,ErrorCD)

SELECT 1, 'A1', 'B1', 'C1', 'E1' UNION ALL

SELECT 2, 'A1', 'B1', 'C1', 'E2' UNION ALL

SELECT 3, 'A1',...

This should work for you, not particularly efficient though

WITH Sdates AS (

SELECT a.Sdate,a.SRC,a.OrgNo

FROM TEST a

WHERE NOT EXISTS(SELECT * FROM TEST b

...

Seems to work with your data, also '30-SEP-2012' '31-DEC-2012' is contract 1 and 2, not 2 and 3.

WITH StartsAndEnds AS (

SELECT MeterID,ContractID,Startdate AS theDate

FROM @MeterContract

UNION ALL

SELECT MeterID,ContractID,Enddate

FROM @MeterContract),

GapsFwd(MeterID,Startdate,Enddate) AS (

SELECT...

Try this

WITH StartAndEnds(id,Start_date_time,End_date_time) AS (

SELECT s.id,s.date_time,MIN(e.date_time)

FROM events s

INNER JOIN events e ON e.id=s.id AND e.date_time>=s.date_time AND e.ev_code = 2

WHERE s.ev_code = 1

GROUP BY s.id,s.date_time)

SELECT t.pk,t.id,t.date_time

FROM tbl_1 t

WHERE EXISTS (SELECT *...

ELSE @BoD END between

You can use the XML 'contains' operator

DECLARE @event XML

SET @event = '

<EVENT_INSTANCE>

<EventType>ALTER_PROCEDURE</EventType>

<PostTime>2011-06-06T00:00:00.000</PostTime>

<ServerName>SERVERNAME</ServerName>

<LoginName>DOMAIN\User</LoginName>

<UserName>dbo</UserName>

<DatabaseName>DBName</DatabaseName>

<SchemaName>dbo</SchemaName>

<ObjectName>procedure_name</ObjectName>

<ObjectType>PROCEDURE</ObjectType>

</EVENT_INSTANCE>

'

SELECT...

peterzeke (5/27/2011)

---

peterzeke (5/27/2011)

---

Mark-101232 (5/27/2011)

---

Unless I'm misunderstanding this, I'm don't think this is a groups and islands problem, more

a case of just joining to the insurance for the latest month

WITH CTE...

Unless I'm misunderstanding this, I'm don't think this is a groups and islands problem, more

a case of just joining to the insurance for the latest month

WITH CTE AS (

SELECT PATID,...

Few possibilities

1) Use a unique constraint on a schemabound view

CREATE VIEW dbo.myView

WITH SCHEMABINDING AS

SELECT col

FROM myTable

WHERE col IS NOT NULL

GO

CREATE UNIQUE CLUSTERED INDEX IX ON dbo.myView (col);

2) Have a look...