Need Ordering help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need Ordering help

Author

Topic

skc40
Starting Member

34 Posts

Posted - 2012-09-07 : 14:13:59

Hello, I would Like to get a solution for ordering the data on the table.

Table Structure is (RecID Int identity(1,1), EmpID, DepPID, Data vachar(max))
EmpID contains Employee Unique ID, DepPID is Dependent Unique ID, data contains Employe or Dependent Data.

The Data is created on the following order
RecID EmpID DepPID Data
1 100 0 EmpInfo
2 100 0 EmpReason
3 100 0 Emp Coverage
4 100 0 Emp Cov Amount
5 100 0 Emp Start Date
6 100 0 Emp Cov -- If there are mutiple coverages, this will create
7 100 0 Emp Cov -- For Multiple Coverage
8 100 1000 Dep Info
9 100 1000 Dep Coverage
10 100 1000 Dep Start Date
11 100 1000 Dep Coverage -- If there are multiple coverages
12 100 1000 Dep Coverage

--For Employee Maximum 4 Coverage Record will be created and 3 for Dependent.

The above records will be created based on following logic--

Let's' say Employee Is only covered then first 5 rows will be created and then extra row depending on Coverage.

If Employee has multiple dependents,
-- Employee Record goes first, then Spouse ( if Present) and then Eldest dependent first.

the DepPID assinged is random.

Let's' say an Employee 500 has 4 dependents with (5002,5003,5004,5005)
5004 is Spouse's' DepPID and 5003 eldest Child, 5005, 5002

The records will be created in following order

500 0
500 5004
500 5003
500 5005
500 5002

If I run the query to create the data where Employee 500 has two diffent coverage, the data will be created in following order

Select * From Table Order by RecID

RecID EmpID DepPID Data
1 500 0 EmpInfo
2 500 0 EmpReason
3 500 0 Emp Coverage
4 500 0 Emp Cov Amount
5 500 0 Emp Start Date
6 500 0 Emp Coverage
8 500 5004 Dep Info
9 500 5004 Dep Coverage
10 500 5004 Dep Start Date
11 500 5004 Dep Coverage
13 500 5003 Dep Info
14 500 5003 Dep Coverage
15 500 5003 Dep Start Date
16 500 5003 Dep Coverage
17 500 5005 Dep Info
18 500 5005 Dep Coverage
19 500 5005 Dep Start Date
20 500 5005 Dep Coverage
21 500 5002 Dep Info
22 500 5002 Dep Coverage
23 500 5002 Dep Start Date
24 500 5002 Dep Coverage

I want the records to be created in following order without altering the code that creates the record

EmpID DepPID Data
500 0 EmpInfo
500 0 EmpReason
500 0 Emp Coverage
500 0 Emp Coverage
500 0 Emp Cov Amount
500 0 Emp Start Date
500 5004 Dep Info
500 5004 Dep Coverage
500 5004 Dep Coverage
500 5004 Dep Start Date
500 5003 Dep Info
500 5003 Dep Coverage
500 5003 Dep Coverage
500 5003 Dep Start Date
500 5005 Dep Info
500 5005 Dep Coverage
500 5005 Dep Coverage
500 5005 Dep Start Date
500 5002 Dep Info
500 5002 Dep Coverage
500 5002 Dep Coverage
500 5002 Dep Start Date

I tried several ways but could not figure out a way to come up with a solution., but could not
Thank you in advance

skc40
Starting Member

34 Posts

Posted - 2012-09-07 : 15:36:18

I came up with following solution using Cursor Logic--

Declare @EmpID Int,
@DepPID Int
Declare @Table Table (RecID Int, EmpID Int, DepPID Int, Data Varchar(max))
Insert Into @Table Values (1, 500, 0,'EmpInfo')
Insert Into @Table Values (2, 500, 0, 'EmpReason')
Insert Into @Table Values (3, 500, 0, 'Emp Coverage')
Insert Into @Table Values (4, 500, 0, 'Emp Cov Amount')
Insert Into @Table Values (5, 500, 0, 'Emp Start Date')
Insert Into @Table Values (6, 500, 0, 'Emp Coverage')
Insert Into @Table Values (8, 500, 5004, 'Dep Info')
Insert Into @Table Values (9, 500, 5004, 'Dep Coverage')
Insert Into @Table Values (10, 500, 5004, 'Dep Start Date')
Insert Into @Table Values (11, 500, 5004, 'Dep Coverage')
Insert Into @Table Values (13, 500, 5003, 'Dep Info')
Insert Into @Table Values (14, 500, 5003, 'Dep Coverage')
Insert Into @Table Values (15, 500, 5003, 'Dep Start Date')
Insert Into @Table Values (16, 500, 5003, 'Dep Coverage')
Insert Into @Table Values (17, 500, 5005, 'Dep Info')
Insert Into @Table Values (18, 500, 5005, 'Dep Coverage')
Insert Into @Table Values (19, 500, 5005, 'Dep Start Date')
Insert Into @Table Values (20, 500, 5005, 'Dep Coverage')
Insert Into @Table Values (21, 500, 5002, 'Dep Info')
Insert Into @Table Values (22, 500, 5002, 'Dep Coverage')
Insert Into @Table Values (23, 500, 5002, 'Dep Start Date')
Insert Into @Table Values (24, 500, 5002, 'Dep Coverage')

select * from @Table
Order By RecID

Declare Data Cursor for
Select EmpID,DepPID From @Table
where Data Like 'Emp Coverage%'
Or Data Like 'Dep Coverage%'
Order by RecID
Open Data
Fetch Next from Data into @EmpID,@DepPID
While @@Fetch_Status=0
Begin
Update @Table Set RecID=Case When Data Like 'Emp Coverage%'
Then (Select Min(RecID) From @Table
Where EmpID=@EmpID
and DepPID=@DepPID
and Data Like 'Emp Coverage%')
Else
(Select Min(RecID) From @Table
Where EmpID=@EmpID
and DepPID=@DepPID
and Data Like 'Dep Coverage%')
End
Where EmpID=@EmpID
and DepPID=@DepPID
and (Data Like 'Emp Coverage%'
Or Data Like 'Dep Coverage%')
Fetch Next From Data Into @EmpID,@DepPID
End
Close Data
DeAllocate Data

Select * from @Table
Order by RecID

Can anyone help to find a solution without using a Cursor.

Thanks

bobmcclellan
Starting Member

46 Posts

Posted - 2012-09-07 : 20:43:32

Not sure why you are trying to pump the rows into the table
in the order that you want... looks like you just want to order
by RecID. Why not just append rows as needed then select using
the OrderBy to return exactly what you want...

Think Set Based Programming.

Not sure how you are determining the spouse and eldest dependent but..
add those params as columns in the temp table so that you can use them
as part of your order by....

hth,
..bob

skc40
Starting Member

34 Posts

Posted - 2012-09-10 : 11:31:09

bobmcclellan, sorry for the late reply.

I see what you're saying.

The Emp Coverage row and Dep Coverage row neeeded to be one after the other, so thought of dumping the data into table varaible and updaing recID so that the records are alligned accordingly.

Will figure out a different approach for ordering these records.

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 12:29:05

quote:
Originally posted by skc40

bobmcclellan, sorry for the late reply.

I see what you're saying.

The Emp Coverage row and Dep Coverage row neeeded to be one after the other, so thought of dumping the data into table varaible and updaing recID so that the records are alligned accordingly.

Will figure out a different approach for ordering these records.

Thanks a lot.

why not use a typeid to identify the type of coverage and give them values in such a way to make them continuos

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skc40
Starting Member

34 Posts

Posted - 2012-09-11 : 14:16:21

Hi Visakh/Bob,

came up with a solution by determinig the spouse and eldest dependent. The ordering works great.

Thanks for the response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:34:16

ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources