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

[512512512]

I have a big presentation tomorrow and I have a query I'm trying to
make a little more presentable. (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.

 

[LKBrwnDBA]

Try this:

Select Firstname+' '+Lastname As [Firstname Lastname]
      ,Synctime As [Nestle Synctime]
      ,Sum(Durationinminutes) As [Duration In Minutes]
  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

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[r937]

if this query is intended to be presented as sql, i.e. if the presentation is about the query and not about the data, then the first question i would raise (if i were in the audience) is how come you have People_Id in the GROUP BY but not the SELECT

i would also ask why not every column has a table qualifier (specifically, the two in the ON clause)

r937.com | rudy.ca

 

[LKBrwnDBA]

Ooops, missed that (I just copied, pasted, added an alias and beautified the sql).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[512512512]

Thats perfect! Thanks for your help

 

[512512512]

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

 

[r937]

i'm having a real hard time understanding what that query is supposed to be doing now

the derived table gets the latest synctime per person where the duration is NULL

by joining to this derived table, you are ensuring that only people who actually have at least one synclog row with a NULL duration (regardless of when the synctime was) are returned in the main query

then you turn around and filter out all synclog rows that are earlier than may 5th

then you add up the durations of the remaining synclog rows (those since may 5th, for people who had at least one NULL duration) and make sure the sum of durations is greater than 100

what in the world is the MAX(synctime) and NULL test really for?

oh, and what was your original question again?



r937.com | rudy.ca