sql server programming: Am a newbie to SQL QUERIES (reporting) - Please assist

Scenario:
I have a simple table where I have columns like

empno
empname
latedate
reasonlate
justification

This table contains mulitple data for a single empno as you can see the
structure

for eg:

empno empname latedate reasonlate justification

101 Kevin 10/1/2005 Business Training day 1
101 Kevin 10/2/2005 Business Training day 2
101 Kevin 10/3/2005 Business Training day 3
103 Tracy 10/1/2005 Personal Sick
103 Tracy 10/3/2005 Business Seminar
.....
.....

I need to have a report like
Kevin (101)
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3

Tracy
10/1/2005 Personal Sick
10/3/2005 Business Seminar


FYI: I also have another table as EmpMaster with empno and name, so
that I can fetch those data from there.

Someone please assist me in preparing this report

Thankyou in advance

Best Regards
Shanin

This script returns data for a specified employee:

declare @emp_name <datatype>

set @emp_name = 'Kevin'

select latedate
,reasonlate
,justification
from dbo.EmpMaster
inner join <other_table>
on <other_table>.empno = dbo.EmpMaster.empno
where (dbo.EmpMaster.empname = @emp_name)


You could design a procedure based on the query above.

To help you find a better solution we'd need to see DDL, more sample data
and preferably a more elaborate description of expected results.

Thank you ML for the reply

I actually need to use this script in an ASP file and generate a
report, I will not be able use hard coding, should be dynamic.

I forgot to provide the information that the core data is in a table
called HRDATA where I have those columns

empno
empname
latedate
reasonlate
justification

and have another master table viz. EmpMaster just in case we need to
join or fetch empno and empname alone

By the way what's DDL ?

Regards
Shanin

DDL = Data Definition Language

For relevant info, please see:
http://www.aspfaq.com/etiquette.asp?id=5006

If you need to access data from a web page, then using a stored procedure is
the best way to do it. The procedure itself may not be dynamic, but the
purpose it serves is far from being "hard coded".

After you provide us with better specifications, we can help you design a
better solution.

Maybe this might also be a genuine opportunity to wipe the dust from your
copy of Books Online. :)

The specification was the one I provided above, ok let me be more
precise

I have a table viz. HRDATA andthe columns to be considered from this
are

empno - float(8)
empname - varchar(50)
loccode - varchar(50)
latedate - datetime
reasonlate-varchar(100)
justification-varchar(900)
approved-numeric(9)

now each time a employee submits his data from a ASP web form for a
certain date, a record is created in this table, hence for each date he
is late or absent he shall submit his justification, and hence the
records are like below

empno empname loccode latedate reasonlate justification
approved
101 Kevin JJ 10/1/2005 Business Training
day 1 1
101 Kevin JJ 10/2/2005 Business Training
day 2 1
101 Kevin JJ 10/3/2005 Business Training
day 3 1
103 Tracy OL 10/1/2005 Personal Sick
2
103 Tracy OL 10/3/2005 Business Seminar
1

now the HR Manager needs a report like the one below, grouped by
Empname, number and loccode

Kevin (101) - JJ
10/1/2005 Business Training day 1
10/2/2005 Business Training day 2
10/3/2005 Business Training day 3

Tracy (103) - OL
10/1/2005 Personal Sick
10/3/2005 Business Seminar

I have another master table viz. EmpMaster where I have all the
employees name, no, and loccode. Hope am repeating what I said in my
first post.
Ths is the actual scenario, I guess it's very simple for SQL GURUS..

Regards
Shanin

Thankx guys, I have made it possible through ASP coding itself.

Regards
Shanin

[quoted text, click to view]

The query below will provide the needed data. However, the grouping in your
specification is really report formatting (i.e. group section headers) and
is beyond the scope of SQL. The particulars depend on your reporting tool.

SELECT
empno,
empname,
loccode,
latedate,
reasonlate,
justification
FROM HRDATA
ORDER BY
empname,
empno,
loccode

The EmpMaster table isn't needed since HRDATA contains the required info.
In fact, redundant non-key data like empname is a big red flag in a
relational database. There are some cases where redundancy is deliberately
introduced but I get the feeling that poor design is the case here. Which
name would your HR Manager expect when you have different names in both
tables for the same employee (empno)?

In the future, please include the DDL scripts and sample data like the
following. Many of your fellow SQL Server community members will take the
time to develop and test a solution to your problem. Scripts eliminate
ambiguity and is quite time consuming to construct these scripts from
narrative.


CREATE TABLE HRDATA
(
empno float(8),
empname varchar(50),
loccode varchar(50),
latedate datetime,
reasonlate varchar(100),
justification varchar(900),
approved numeric(9)
)

INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/1/2005', 'Business', 'Training day 1', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/2/2005', 'Business', 'Training day 2', 1)
INSERT INTO HRDATA VALUES
(101, 'Kevin', 'JJ', '10/3/2005', 'Business', 'Training day 3', 1)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/1/2005', 'Personal', 'Sick', 2)
INSERT INTO HRDATA VALUES
(103, 'Tracy', 'OL', '10/3/2005', 'Business', 'Seminar', 1)


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]