You could call a stored procedure from your app with the search keyword, the user and the IP address and store the values in the audit table.

An alternative would be...

The less complicated way to get the total number of MMR vaccines:

;

WITH cte AS

(

SELECT

PatientID,

SUM(CASE WHEN ImmunizationID IN(1,2,3,4) THEN 1 ELSE 0 END) AS MEASLES,

SUM(CASE WHEN ImmunizationID IN(1,2,4,6) THEN...

This solution depends on the StopName being ascending as shown in your sample data.

If that's not the case, you'll need to use another column that define whether a stop belongs...

I'm confident this can be done without a loop.

Please read the first article in my signature and post table def and sample data in a ready to use format so...

The problem is there's nothing in the data that would allow to define "the full combination of individual vaccines that makes up a completed MMR vaccine".

Can you please elaborate?

It's always great to get a reply like "it won't run"...

You might want to take the time and read the error message SQL Server is kind enough to provide.

@Lynn: columns...

I have no idea.

It would make sense to place it somewhere after...

The bottom-up might be simpler. But internally, it'll "climb up" one and two nodes for each "bottom" element. Depending on the xml size and structure this approach is less efficient...

Are you looking for something like this?

WITH cteRates AS

(

SELECT

o.EMP_ID,

(ROW_NUMBER() over(partition by o.EMP_ID order by x.r.value('@date_changed','DATETIME') desc)) as RowNum,

convert(VARCHAR(10), x.r.value('@date_changed','DATETIME'), 101) AS DATE_CHANGED,

...

It seems like you're dealing with a denormalized table. I'd probably use UNPIVOT to get values for amt_* into one column and then use a simple join.

GilaMonster (4/6/2012)

---

LutzM (4/6/2012)

---

Another option would be to buy SQL2008R2. The license terms were much more customer friendly (you'd need two processor licenses for the two sockets when using 2008 or...

Yes, I'll post a coded solution for 2000 if you take te time and post ready to...

You posted in the SQL 2005 forum and didn't mention the solution should work on SQL2000.

Neither the table variable nor the CTE will...

Do you really need a processor based license?

Depending on how the SQL Server is used, a UserCAL or DeviceCAL based license might be good enough.

Another option would be to buy...

As a side note:

Usually I vote against table variables used in a join due to the risk of bad performance caused by missing statistics. (SQL Server will assume there's only...