Need assistance

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
ShaninThis 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.
ML|||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. :)
ML|||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|||> now the HR Manager needs a report like the one below, grouped by
> Empname, number and loccode
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
"Cupid Shan" <shaninraja@.gmail.com> wrote in message
news:1130751379.384631.160800@.g44g2000cwa.googlegroups.com...
> 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
>