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

Transact-SQL (2000)

Query

Author

Topic

ragh
Starting Member

34 Posts

Posted - 2005-06-11 : 04:13:55

I got a table of employee details where employee id and name is repeated, but other details are changed, i want to write a query to show empid and empname once and details completely. how can i do this?

Ex: this is real
empid empname time time2 date
1 aaaa 9 am 10 am 01/01/2005
1 aaaa 11 am 12 pm 01/01/2005
2 bbbb 10 am 11 am 01/01/2005
2 bbbb 12 pm 13 pm 01/01/2005

i want like this :
empid empname time time2 date
1 aaaa 9 am 10 am 01/01/2005
11 am 12 pm 01/01/2005
2 bbbb 10 am 11 am 01/01/2005
12 pm 13 pm 01/01/2005

- represents nothing

Help me please with a query in SQL Server and MS ACCESS

regards

Ragh

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-11 : 06:38:13

This is a presentation issue, not a query issue. MS Access has built-in reporting and it can suppress duplicate rows, that's the best approach to take in that regard. If you're using a different reporting package for SQL Server, it will have a similar functionality.

ragh
Starting Member

34 Posts

Posted - 2005-06-11 : 07:14:31

no, am using ms access db with VB. i want to generate a report using data reports in vb, by selecting the start date and end date.
ie. it must display like
what is happening now is :
Empid empname time time2 date
1 AAAA 9 am 10 am 01/01/2005
1 AAAA 11 am 12 pm 01/01/2005
both empid and name is displayed for all rows, i dont want that to happen, just show empid and name once but display all other details correctly.
like this:
Empid empname time time2 date
1 AAAA 9 am 10 am 01/01/2005
11 am 12 pm 01/01/2005
i know its wholy VB Question, but if i get a query in such away then can create temp table and do it right?

regards,

Ragh

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-11 : 13:54:02

In principle, I would have to agree with Rob about this being a presentation issue. However, if you really really need to use a query instead, you can try something like this (and I'm guessing a little bit as to your datatypes):

SELECT A.empid, B.empname, B.time, B.time2, B.date
FROM
(SELECT empid, MIN(CONVERT(datetime, [date] + ' ' + [time])) as mindate
FROM YourTable
GROUP BY empid) AS A
RIGHT JOIN
(SELECT empid, empname, time, time2, date, CONVERT(datetime, [date] + ' ' + [time]) as mindate
FROM YourTable) AS B
ON A.mindate = B.mindate

SELECT ISNULL(CONVERT(varchar, A.empid), '') AS empid, B.empname, B.time, B.time2, B.date
FROM
(SELECT empid, MIN(CONVERT(datetime, [date] + ' ' + [time])) as mindate
FROM YourTable
GROUP BY empid) AS A
RIGHT JOIN
(SELECT empid, empname, time, time2, date, CONVERT(datetime, [date] + ' ' + [time]) as mindate
FROM YourTable) AS B
ON A.mindate = B.mindate

The first query will give you NULLs for repeated empid's, and the second will give you an empty string for a repeated empid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources