Create Incremented Column using UNION statement

[thorntong]

Hi Everyone,
I am trying to figure out how I can increment a counting column while using a UNION statement. I have created a separate table to store the current value. Here is my query:

SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_BP
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Encount
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Eye
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Immune
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Problems
UNION
SELECT     TOP (100) PERCENT PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Vitals
ORDER BY PatID

Results look like this:

PatID    VID     RunDate                  Status
5610     13820   2010-05-13 00:00:00.000  NULL
6120     22820   2010-05-13 00:00:00.000  NULL
10310    13843   2010-05-13 00:00:00.000  NULL

And this is what I am hoping to get:
Results look like this:

PatID    VID     RunDate                  Status  Count
5610     13820   2010-05-13 00:00:00.000  NULL    1
6120     22820   2010-05-13 00:00:00.000  NULL    2
10310    13843   2010-05-13 00:00:00.000  NULL    3

The table that I created to store the last count value is called: VT_Blueprint_Counter and it column is: VTHIECount Right now the value is set to 0. After I run my query, I need it to update this table with the last count value so that I can use that as the starting point for the next time the query runs. I was using this to help me get the counter value:

DECLARE @counter int
SET @counter=(select VTHIECount FROM VT_Blueprint_Counter)

I feel as if I'm on the right track, but I cannot seem to get this to work with a UNION statement.

I am using SQL 2005 SP3 on a Windows 2003 server.

Thank you,
Glenn

 

[Crowley16]

not sure why this is necessary, especially with the new ranking functions in 05, but if you almost have it working this way already...

try setting the counter by:
declare @variable int
select @variable = columnName from TableName

--------------------
Procrastinate Now!

 

[bborissov]

SELECT *, ROW_NUMBER() OVER (ODRER BY PatId) AS Cnt
FROM (SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_BP
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Encount
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Eye
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Immune
UNION
SELECT     PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Problems
UNION
SELECT     TOP (100) PERCENT PatID, VID, RunDate, null AS 'Status'
FROM         dbo.VT_Blueprint_Vitals) Tbl1
ORDER BY PatID

NOT TESTED!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.