Query Help - 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)

Query Help

Author

Topic

Jster
Starting Member

21 Posts

Posted - 2008-05-06 : 13:52:59

OK right now I have the following sql statement


SELECT firstname, lastname, ReportLog.ID, ReportLog.Menu, COUNT(ReportLog.Report) AS Hits, MAX(ReportLog.Logtime) AS Lasttime 
FROM ReportLog, Users 
WHERE Users.ID = ReportLog.ID AND Users.PESVOnly = 0 AND RIGHT(RTRIM(ReportLog.ID),5) = '00000' 
GROUP BY ReportLog.ID, ReportLog.Menu, firstname, lastname 
ORDER BY ReportLog.ID, ReportLog.Menu

this nets the results below
firstname lastname id Menu Hits Lasttime
-------------------- -------------------- ------------------------------ --------------- ----------- -----------------------
Amy Birt ABirt00000 SecHits1 16 2008-04-04 11:43:21.000
Amy Birt ABirt00000 SecHits2 1 2008-04-04 10:40:22.000
Amy Birt ABirt00000 SecHits3 17 2008-04-04 11:43:50.000

What I want is the ability to have one record that displays the Firstname, lastname, ID, SecHits1, SecHits1Date, SecHits2, SecHits2Date, SecHits3, SecHits3Date. Any ideas on how I can achieve this through a SQL Statement?

Thanks,
Josh

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-06 : 14:21:14

Can you please explain what exactly you mean one record?
Of the 3 records you have shown as output which one you want to display & what will be the condition?

Jster
Starting Member

21 Posts

Posted - 2008-05-06 : 14:31:34

In one record grouped by the ID, FirstName, Lastname. Here is what the output would look like.

Firstname LastName ID SecHits1 SecHits1Date SecHits2 SecHits2Date SecHits3 SecHits3Date
Amy Birt ABirt00000 16 2008-04-04 11:43 1 2008-04-04 10:40 17 2008-04-04 11:43

Let me know if that make sense.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-06 : 14:42:20

quote:
Originally posted by Jster

In one record grouped by the ID, FirstName, Lastname. Here is what the output would look like.

Firstname LastName ID SecHits1 SecHits1Date SecHits2 SecHits2Date SecHits3 SecHits3Date
Amy Birt ABirt00000 16 2008-04-04 11:43 1 2008-04-04 10:40 17 2008-04-04 11:43

Let me know if that make sense.

Yes that make sense.But what if there are more records say 5 or 7.You want output to be SecHits1..SecHits5..SecHits7

Jster
Starting Member

21 Posts

Posted - 2008-05-07 : 08:27:51

The way the database is setup there will be up to 3 sechits, nothing more, but it could be less.

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources