March 12, 2003 at 9:56 am
I need to create a query that grabs the first 2 digits of a persons last name for all people in a table. I then need to know the row number that the person is in based on their position in the query result set - according to the order by in the query.
So: select substring(PatientLastName,1,2)
from dbo.Staging_Master
order by substring(PatientLastName,1,2) asc
I need another column indicating query row number. I cannot alter the base table and I do not want the persons row number from the table (select substring(PatientLastName,1,2),
(select count(*) from dbo.staging_master as b where b.accountnumber <= dbo.Staging_Master.accountnumber) as table_row_number,
from dbo.Staging_Master
order by substring(PatientLastName,1,2) asc) - I can get this already. The background is: I need to create worklists for insurance reps to handle accounts. Each user will be responsible for the patients in their worklist AA - CZ, DA - FG ... To break the list of patients into X semi-equal groupings based on the first 2 digits of their last name.
Any ideas?
Thanks
March 12, 2003 at 12:27 pm
CREATE tmp_Table
(ID INT IDENTITY(1,1) NOT NULL,
PatientName VARCHAR(2))
GO
INSERT INTO tmp_Table
EXEC ('select substring(PatientLastName,1,2)
from dbo.Staging_Master
order by substring(PatientLastName,1,2) asc'
GO
SELECT * FROM tmp_Table
March 12, 2003 at 12:53 pm
The suggested solution is good but use #tmp_Table instead of tmp_Table.
Depending on the application design and needs, you could also return the entire resultset to the front end and break down the final report onto groups using the reporting tool. You would know the total count by that time. Front end processes recordset one row at a time anyway.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply