Return the value of the first entry in a table per ID.

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 2000 Forums

Transact-SQL (2000)

Return the value of the first entry in a table per ID.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-15 : 08:23:46

Tony writes "Dear Sirs,

I have a table with the following fields:

ID integer (can contain duplicates)
Log_Date date/time
Log_Time date/time
Val contains values 'H', 'M', 'L'

I want to return a count of each possible Val (H, M, L) of the initial entry for each ID.

E.g. The table may contain

ID Log_Date Log_Time Val
100 12/12/2002 12:50 L
100 12/10/2003 11:30 H
100 12/12/2004 12:45 M
100 12/12/2005 10:45 L

The earliest date/time is 12/12/2002 12:50 and so L = L + 1 for this ID.

Want something like:

H M L
120 50 73

Where the numbers are the counts of each H, M, L values for the initial entry only for each ID (possibly group by ID????)

Any help would be greatly appreciated.

Many Thanks,

Tony Leeper"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-15 : 08:50:53

select id, val, count(*) from table1
where id = @idvariable
group by id, val

if you want this transformed from cols into rows... then search here for "pivot"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources