Pulling My hair Out - 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)

Pulling My hair Out

Author

Topic

atreau2k
Starting Member

3 Posts

Posted - 2007-02-17 : 18:01:23

I am loosing my mind over something that should be simple....
Please help if you can.

4 columns
UserID(int)
ReadyStatus(int)
DateCreated(datetime)
UserState(varchar)

1) I need to pull the Most recent Record for each unique USERID based on DATECREATED (only showing today) presorted by UserState

2) I then need to find the count for each of the Unique READYSTATUS (0-6)

can this be done in 1 SQL call, rather than looping through the records in code manually?

I cannot use stored procedures... has to be written out in code.

any help would be sooooo appreciated.

atreau2k
Starting Member

3 Posts

Posted - 2007-02-17 : 18:17:42

OOps... forgot to mention...

Think of this table as a log file. Each time the READYSTATUS changes, rather than an update to the, a new record gets inserted. So there are multiple entries for USERID's and READYSTATUS's

example:
UserID ReadyStatus
1 0
1 1
1 2
1 3
2 0
2 1
2 2
2 3

each time I run a query for the different status's the same user is counted more than once. Thats why this is sooo difficult.

I just need a count of the MOST RECENT STATUS grouped by USER...

Please help...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-17 : 19:25:35

this can be achieved very easily, and there is no need for looping through the data, I just am not understanding what you are looking to ultimatly get. Can you please Provide a sample data set that illustates your records, and then illustrate what you are looking to end up with.

Thanks.

atreau2k
Starting Member

3 Posts

Posted - 2007-02-17 : 19:48:20

Maybe this picture can help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-17 : 20:40:56

quote:
1) I need to pull the Most recent Record for each unique USERID based on DATECREATED (only showing today) presorted by UserState


declare @table table
(
	UserID			int,
	ReadyStatus		int,
	UserState		varchar(10),
	LastActivityDate	datetime,
	DateCreated		datetime
)

insert into @table
select	1, 1,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 19:40:56'	union all
select	2, 0,  'Nevada', NULL,			'2007-02-17 17:25:06'	union all
select	2, 1,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:24:59'	union all
select	6, 0,  'Nevada', NULL,			'2007-02-17 17:24:31'	union all
select	6, 1,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:16:55'	union all
select	1, 0,  'Nevada', NULL,			'2007-02-17 17:12:51'	union all
select	1, 1,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:08:19'	union all
select	1, 0,  'Nevada', NULL,			'2007-02-17 17:08:14'	union all
select	1, 3,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:43'	union all
select	1, 2,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:42'	union all
select	1, 1,  'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:35'	union all
select	1, 0,  'Nevada', NULL,			'2007-02-17 16:59:23'

select	*
from	@table t
where	DateCreated	= (select max(DateCreated) from @table x where x.UserID = t.UserID)

quote:
2) I then need to find the count for each of the Unique READYSTATUS (0-6)

Don't quite understand this. You want the count for unique READYSTATYS for each of the UserID ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-17 : 20:43:16

[code]
declare @table table
(
UserID int,
ReadyStatus int,
UserState varchar(10),
LastActivityDate datetime,
DateCreated datetime
)

insert into @table
select 1, 1, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 19:40:56' union all
select 2, 0, 'Nevada', NULL, '2007-02-17 17:25:06' union all
select 2, 1, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:24:59' union all
select 6, 0, 'Nevada', NULL, '2007-02-17 17:24:31' union all
select 6, 1, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:16:55' union all
select 1, 0, 'Nevada', NULL, '2007-02-17 17:12:51' union all
select 1, 1, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 17:08:19' union all
select 1, 0, 'Nevada', NULL, '2007-02-17 17:08:14' union all
select 1, 3, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:43' union all
select 1, 2, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:42' union all
select 1, 1, 'Nevada', '2007-02-17 19:41:02', '2007-02-17 16:59:35' union all
select 1, 0, 'Nevada', NULL, '2007-02-17 16:59:23'

select t.*, c.ReadyCount
from @table t inner join
(
select UserID, ReadyCount = count(distinct ReadyStatus)
from @table
group by UserID
) c
on t.UserID = c.UserID
where DateCreated = (select max(DateCreated) from @table x where x.UserID = t.UserID)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources