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...

You would have to check the input parameters for NULLs and then skip those. That might mean that you have a zillion different updates (one for each parameter), or...

Once you use the code in the link, you could use XQuery to extract the information from the report's XML that you need... just parse it and then insert that...

This might get you started:

http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/

sounds like you need a subreport

How many rows are in your table?

You could do something simple like

DELETE FROM MyDB.dbo.MyTable WHERE <filter>...

but if you are deleting a lot of rows, there may be better ways...