how to get minimum with equal to this date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

how to get minimum with equal to this date?

Author

Topic

royramos
Starting Member

9 Posts

Posted - 2011-02-27 : 19:41:35

Hi guys,

I have problem extracting needed data with the format that i need, can anybody please help me on this, i have 2 table please see below

usertable
id | name |
1 | user1 |
2 | user2 |
3 | user3 |
4 | user4 |

login table
id | date | time
1 | 02/28/11 | 830
1 | 02/28/11 | 900
1 | 02/28/11 | 1730
2 | 02/28/11 | 800
2 | 02/28/11 | 1730
3 | 02/28/11 | 830

maybe you guys can help me to extract data on this format

id | name | date | logintime(min)
1 | user1 |02/28/11 | 830
2 | user2 |02/28/11 | 800
3 | user3 |02/28/11 | 830
4 | user4 | null | null

thanks,
-roy

-roy

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-27 : 20:33:36

This isn't a formatting issue. You just need to use the MIN function and and inner join between the 2 tables.

Jim

Everyday I learn something that somebody else already knew

royramos
Starting Member

9 Posts

Posted - 2011-02-27 : 21:48:01

hi jim thanks for the reply, this is what i've done already

i create a separate view to extract logintime min(logintime_min)

select id, date, min(time) as mintime from login group by id, date

then i try to extract data using this

select usertable.id, usertable.name, logintime_min.mintime from usertable
inner join logintime_min on usertable.id=logintime_min.id where logintime_min.date='02/28/11'

problem here is other user like user4 that don't have entry in login table was not display

thanks,
-roy

-roy

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-27 : 23:32:12

Try this :

Create table #usertable
( Id int,
[name] varchar(50)
)

Create table #login
(
id int,
[date] datetime,
[time] int
)

Insert into #login
Select 1,'02/28/11', 830 union all
Select 1,'02/28/11', 900 union all
Select 1,'02/28/11', 1730 union all
Select 2,'02/28/11', 800 union all
Select 2,'02/28/11', 1730 union all
Select 3,'02/28/11', 830

Insert into #usertable
Select 1,'user1' union all
Select 2,'user2' union all
Select 3,'user3' union all
Select 4,'user4'

Select ut.id, ut.[name], min([time])
from #usertable ut
left outer join
#login ln
on ut.id = ln.id
and ln.[date] = '02/28/11'
group by ut.id, ut.[name]

You naming convention is improper. Read some document on SQL Standard so that you get better idea

royramos
Starting Member

9 Posts

Posted - 2011-02-28 : 19:50:16

Hi Pk bohra thanks it's working, i take your advised and gonna study somemore thanks

-roy

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-02-28 : 23:02:20

quote:
Originally posted by royramos

Hi Pk bohra thanks it's working, i take your advised and gonna study somemore thanks

-roy

You are welcome

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources