To expand on Jeff's (and others) point... It would be helpful if you were to give some explanation as to what constitutes "consecutive" in this case.

Letting us know which of...

skilly2 (6/29/2015)

---

This solution worked as well. It works well with a small number of rows, but I don't think is easy as the above solution to adopt to a...

sratemo (6/29/2015)

---

Hi Jason,

I'm using version 2008. Did not really pay attention to it, and it doesn't come with LAG and LEAD functions. I've just re-posted this on the 2008...

pietlinden (6/29/2015)

---

Not really. It's only in TempDB because for him, it's not a permanent part of any database. you would put it in your database and not in TempDB.

The...

There is always the option of dumping the results of the execution into a temp table and joining to that.

Here's a rather trivial example but it will hopefully illustrate the...

Based on your desired output, I think the following is what you're looking for...

WITH AddRN AS (

SELECT

t.ID,

t.Date,

ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.Date) AS RN

FROM

#temp t

)

SELECT

a.ID,

SUM(CASE WHEN DATEDIFF(yy,...

DDL & consumable test data is the important thing. Something more akin to the following...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp (

ID INT NOT NULL,

[Date] DATE NOT...

Yea... That's simple enough we (you actually) just need to know where that information exists. Once you have that in scope, simply use that to replace the hard coded value.

So... You want any times that are between midnight and 8:09 to increment to the next day?

If that's correct, the following should work...

SELECT

CASE WHEN mt.MyTime >= '00:00:00' AND mt.MyTime...

This should get you what you are looking for...

-- test data --

IF OBJECT_ID('tempdb..#PatientProblem') IS NOT NULL

DROP TABLE #PatientProblem;

CREATE TABLE #PatientProblem

(

PatientID INT,

AdmissionDate DATETIME,

DischargeDate DATETIME,

Cost MONEY

)

GO

--Insert Data

INSERT INTO #PatientProblem(PatientID,AdmissionDate,DischargeDate,Cost)

VALUES

(1009,'2014-07-27','2014-07-31',1050.00),

(1009,'2014-08-01','2014-08-23',1070.00),

(1009,'2014-08-31','2014-08-31',1900.00),

(1009,'2014-09-01','2014-09-14',1260.00),

(1009,'2014-12-01','2014-12-31',2090.00),

(1024,'2014-06-07','2014-06-28',1900.00),

(1024,'2014-06-29','2014-07-31',2900.00),

(1024,'2014-08-01','2014-08-02',1800.00);

-- index to eliminate...

Looks like there have been some new posts since I started in on my version of the solution... I haven't had a chance to test any of them against what...

+1 on this being a VERY BAD design...

If for some reason you aren't able to rework the design, something like the following should work...

DECLARE @search VARCHAR(10);

SET @search = 'dog,COW';

WITH T(C)

AS

(

SELECT...

Carl Caputo (6/20/2015)

---

I have attribute history of items at week level and need to derive the range of start and end dates of each attribute change over time to insert...

Are you able you simply put the query in a stored proc and then simply execute the sp over the ODBC connection?

If you check Post #1695927, you'll see that the final...