Create a Group from Thin Air! - 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)

Create a Group from Thin Air!

Author

Topic

phillikc
Starting Member

6 Posts

Posted - 2011-08-12 : 14:41:34

I have a table that captures a Users' status (DIsabled or ENabled) as well as the date associated with that status. Dates are in sequential order. Trying to create the Group_Wanted field in SQL. Below is some sample data. The "groups" are distinguished by "flips" in Status from DI to EN and vice versa. Please help! I can't get me head around how to tackle this one.

RecID UserID Status ActionDate Group_Wanted
1 0715User1 DI 4/7/09 7:10:55 PM 1
2 0715User1 DI 9/23/09 2:04:12 PM 1
3 0715User1 DI 9/23/09 4:28:10 PM 1
4 0715User1 DI 9/25/09 4:38:28 PM 1
5 0715User1 DI 10/1/09 10:30:06 AM 1
6 0715User1 EN 10/1/09 10:30:07 AM 2
7 0715User1 EN 10/1/09 10:32:07 AM 2
8 0715User1 EN 10/1/09 10:34:33 AM 2
9 0715User1 DI 10/1/09 10:34:34 AM 3
10 0715User1 DI 10/8/09 9:23:45 AM 3
11 0715User1 EN 10/8/09 9:23:46 AM 4
12 0715User1 EN 10/9/09 10:26:50 AM 4
13 0715User1 EN 10/10/09 7:22:23 PM 4
14 0715User1 EN 10/12/09 6:00:46 PM 4

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2011-08-12 : 16:23:54

No clue what you are trying to do.
Basic rule of thumb: if you can't explain it, you can't code it.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-12 : 17:37:56

Take a look at the RANK() and DENSE_RANK() functions. One of those should get you what you are looking for - but, I cannot be sure without sample data to work with. If you want someone to work on this, please provide sample data in the form of CREATE TABLE and INSERT statements to setup the example.

Jeff

phillikc
Starting Member

6 Posts

Posted - 2011-08-12 : 17:53:01

The CREATE TABLE script:

CREATE TABLE [dbo].[END_USERS_STATUS]
([RecID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](30) NOT NULL,
[UserStatus] [char](2) AS NULL,
[ActionDate] [datetime] NULL,
[Group_Wanted] [bigint] NULL)

Trying to derive a Group_Wanted integer in order to facilitate the calculation of MIN and MAX ActionDates within each UserID and Group_Wanted. The only thing I can think of is to somehow derive the Group_Wanted integer based on the "flip" in Status from DI to EN (or vice versa) within each UserID with ActionDate in ascending order.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-08-24 : 14:41:27

Solutions in order of (my) preference:

1. Do it on application side.
2. Use cursor if the performance is not critical.
3. I think Hugo Cornelis does have a pretty fast solution for that type of problems. I believe you can find him on sql server central.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources