MS SQL Server DTS - Can DTS do this?

Author

Can DTS do this?

Drew

2005-07-22, 8:23 pm

I have a report which I have to complete every quarter. The report is done
in Excel and is called, "Agency Training Metrics Report". There are 20
categories which our training courses fall in. The report is broken into 2
sides, Supervisor and Non-Supervisor and lists all the categories in rows
and has 6 columns, A, B, C, D, E, F which represent race codes,

A= White
B= Black
C= Hispanic
D= Asian/Pacific Islander
E= American Indian
F= Unknown

It also looks for Male and Female,

In essence, the report is for showing the totals between all the variables,
i.e. All Black Male Non-Supervisors who have had a training course in
Executive Development category OR All Hispanic Female Supervisors who have
had a training course in Business Practices, Quality and Competition
category.

This report is cumbersome and long, but I developed 2 Stored Procedures to
give me this data and outputted it into a Access ADP file for reporting. I
would like to skip that step and try to get the data directly into the Excel
file instead.

The stored procedures that I developed are the same, except one looks for
Supervisors and the other looks for Non-Supervisors. Currently my SP only
returns the Category and Employee # for each course taken. I'm not sure how
to output all the data needed. Here are my SP's,

CREATE PROCEDURE spMetricsCollection_
Supervisors
@EmpSex varchar(1),
@EmpRace char(1),
@StartDate datetime,
@EndDate datetime
AS
SELECT E.EmpID, CAT.Cat
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
EP.EmpRace = @EmpRace AND P.PosSupervisor = 1
ORDER BY EmpLName
GO

CREATE PROCEDURE spMetricsCollection_
NonSupervisors
@EmpSex varchar(1),
@EmpRace char(1),
@StartDate datetime,
@EndDate datetime
AS
--Supervisors
SELECT E.EmpID, CAT.Cat
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
EP.EmpRace = @EmpRace AND P.PosSupervisor IS NULL
ORDER BY EmpLName
GO

Can I accomplish this with DTS? After my primer (with mounds of help from
this group) I feel pretty comfortable with DTS, but still am not sure how to
go about doing this. The Excel file has calculations and is formatted, so
it's not like a blank file.

Thanks,
Drew Laing

frank chang

2005-07-24, 7:27 am

Drew, Yes, DTS should be able to do this. Do you have a hotmail, yahoo, aol,
or gmail account? I need a little more information about requirement before I
can propose a solution. Thank you.

"Drew" wrote:

> I have a report which I have to complete every quarter. The report is done
> in Excel and is called, "Agency Training Metrics Report". There are 20
> categories which our training courses fall in. The report is broken into 2
> sides, Supervisor and Non-Supervisor and lists all the categories in rows
> and has 6 columns, A, B, C, D, E, F which represent race codes,
>
> A= White
> B= Black
> C= Hispanic
> D= Asian/Pacific Islander
> E= American Indian
> F= Unknown
>
> It also looks for Male and Female,
>
> In essence, the report is for showing the totals between all the variables,
> i.e. All Black Male Non-Supervisors who have had a training course in
> Executive Development category OR All Hispanic Female Supervisors who have
> had a training course in Business Practices, Quality and Competition
> category.
>
> This report is cumbersome and long, but I developed 2 Stored Procedures to
> give me this data and outputted it into a Access ADP file for reporting. I
> would like to skip that step and try to get the data directly into the Excel
> file instead.
>
> The stored procedures that I developed are the same, except one looks for
> Supervisors and the other looks for Non-Supervisors. Currently my SP only
> returns the Category and Employee # for each course taken. I'm not sure how
> to output all the data needed. Here are my SP's,
>
> CREATE PROCEDURE spMetricsCollection_
Supervisors
> @EmpSex varchar(1),
> @EmpRace char(1),
> @StartDate datetime,
> @EndDate datetime
> AS
> SELECT E.EmpID, CAT.Cat
> FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
> ClassesTaught CT ON C.CourseTitle = CT.Title
> INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
> EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
> INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
> JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
> WHERE EP.EmpSex = @EmpSex AND
> (CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
> @StartDate AND @EndDate) AND
> EP.EmpRace = @EmpRace AND P.PosSupervisor = 1
> ORDER BY EmpLName
> GO
>
> CREATE PROCEDURE spMetricsCollection_
NonSupervisors
> @EmpSex varchar(1),
> @EmpRace char(1),
> @StartDate datetime,
> @EndDate datetime
> AS
> --Supervisors
> SELECT E.EmpID, CAT.Cat
> FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
> ClassesTaught CT ON C.CourseTitle = CT.Title
> INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
> EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
> INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
> JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
> WHERE EP.EmpSex = @EmpSex AND
> (CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
> @StartDate AND @EndDate) AND
> EP.EmpRace = @EmpRace AND P.PosSupervisor IS NULL
> ORDER BY EmpLName
> GO
>
> Can I accomplish this with DTS? After my primer (with mounds of help from
> this group) I feel pretty comfortable with DTS, but still am not sure how to
> go about doing this. The Excel file has calculations and is formatted, so
> it's not like a blank file.
>
> Thanks,
> Drew Laing
>
>
>

Drew

2005-07-25, 9:29 am

Yes... I have a gmail account, dr00bertNOSPAMgmailD
OTcom (note: two zeros
instead of o's)

replace NOSPAM and DOT

Drew

"frank chang" < frankchang@discussio
ns.microsoft.com> wrote in message
news:C6091F4D-F76C-459C-8F54- C13FA9F2E587@microso
ft.com...[color=darkred]
> Drew, Yes, DTS should be able to do this. Do you have a hotmail, yahoo,
> aol,
> or gmail account? I need a little more information about requirement
> before I
> can propose a solution. Thank you.
>
> "Drew" wrote:
>

Sponsored Links