If you're using 2012 or later, you can use LAG.

SELECT ID

, FLAG

, LAG(DT, 1) OVER (PARTITION BY ID ORDER BY DT) AS EffectiveDate

, DT AS TermDate

FROM ##Test;

Note that I'm assuming...

I guess if you're forced to do something "clever" then you have to document your code inside the code... not in some "book" somewhere that nobody can find. (Yeah, I've...

What's wrong with the INSERT INTO without all the variables?

INSERT INTO DestTable(col1, col2, col3)

SELECT [DISTINCT]...

a tally or numbers table is perfect for this. Jeff Moden has a great article on it here[/url].

Basically, you join your table that has the quantity you want to...

Maybe he wants to see how painfully slow they are?

Full address is in a single field? That might cause you problems.

If you had zip code/postal code in its own field, you could add a trigger on the table's zip...

Could you please read this article[/url] and follow instructions for posting? It's just really hard to help without consumable data. (Don't worry, it's all explained in the article...

I'm NOT an SSRS expert, and I wanted to read up on this because I was curious.... That said, I found some articles that you might find interesting/helpful:

SQLChick on Querying...

Do you understand how windowing functions work? Read up on the LAG function. It will explain it all.

Like this is one way:

-- show number of days between successive visits

SELECT PatientID

,VisitID

,VisitNo

,VisitDate

,DATEDIFF(day,LAG(VisitDate,1) OVER (PARTITION BY PatientID ORDER BY VisitDate),VisitDate) AS Elapsed

FROM

(

SELECT 1 AS VisitID,1 AS PatientID,1 AS VisitNo,'12-Jan-2005' AS...

Here's a quick (and very ugly) example... hope it helps! Here's the structure of my source table:

CREATE TABLE [dbo].[Enroll](

[enrollmentID] [int] IDENTITY(10000,1) NOT NULL,

[e_PatientID] [int] NOT NULL,

[e_ProtocolNo] [varchar](30) NOT NULL,

[enrollDate]...

But if you add more states (so more tables), you would only have to fix one thing, and everything would continue to work.

If it comes across as a text field, then you could create a calculated field in your dataset and convert it to whatever datatype you need, using something like CLNG()...

like this?

Note, I changed the datatype of FirstBilledDate to DATE.

CREATE TABLE #T (

serviceid int NOT NULL,

ProgramId int NOT NULL,

Firstbilleddate date NULL,

CoveragePlanName varchar(100) NOT NULL,

ChargeAmount money NULL,

AdjustmentAmount money NULL,

PaymentAmount money...

Welcome to SSC...

Thanks for the data... helps a LOT. Here's my solution... I'm sure there are better ways, but this one works... Note I created all the tables needed...