I need help coding a query in SQL? (filtering data

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

I need help coding a query in SQL? (filtering data

Author

Topic

sqlnewb
Starting Member

2 Posts

Posted - 2006-05-17 : 10:38:06

I have a big presentation tomorrow and I have a query I'm trying to
make a little more presentable. query text itself formatted (filtering data)

Tables:
--tpeople
--tsynclog

The query:

select Firstname+' '+Lastname as [FirstName LastName],synctime as
[Nestle synctime],sum(DurationInMinutes)
from tpeople
join tsynclog on people_id = id
where synctime > '2006-05-05'
group by
tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime
having sum(DurationInMinutes) > 100
order by synctime desc

As you can see I need some alias added and maybe the whole thing formatted a little
different. Can anyone help?
Thanks.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-17 : 11:08:56

Here's some aliases added and better formatting. Is that all you were after?

select
    p.Firstname + ' ' + p.Lastname as [FirstName LastName],
    s.synctime as [Nestle synctime],
    sum(s.DurationInMinutes) as [DurationInMinutes]
from
    tpeople p
    inner join tsynclog s on s.people_id = p.id
where
    s.synctime > '2006-05-05'
group by
    p.FirstName,
    s.people_id,
    p.LastName,
    s.SyncTime
having
    sum(s.DurationInMinutes) > 100
order by
    s.synctime desc

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

sqlnewb
Starting Member

2 Posts

Posted - 2006-05-17 : 14:42:54

This is what I came up with but it seems broken

Select Firstname+' '+Lastname As [Firstname Lastname]
,x.synctime As [Nestle Synctime]
,Sum(Durationinminutes) As [Duration In Minutes]
From Tpeople p
Join Tsynclog sl On sl.People_Id = p.Id
join (
select people_id, max(synctime) as synctime
from tsynclog
where Durationinminutes is null
group by people_id
) x on x.people_id = sl.people_id
where sl.Synctime > '2006-05-05'
Group By
p.Firstname
,sl.People_Id
,p.Lastname
,x.Synctime
Having Sum(Durationinminutes) > 100
Order By x.Synctime Desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources