July 12, 2011 at 1:43 pm
I do not use the wizard.
If you do it manually and you know what you are doing you can do it quickly,you know what you are doing and you can clean up the flaws in the DB, etc.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 12, 2011 at 2:41 pm
I would suggest that you look into the JOIN Statement.
SELECT tbl_PI_Data.Date, tbl_PI_Data.Assoc_Name, tbl_PI_Associates.MgrName, tbl_PI_Data.Func_Name, tbl_PI_Data.Total_Proc, tbl_PI_Data.Prod_Hrs, tbl_PI_Data.Paid_Hrs, [Total_Proc]/[Prod_Hrs] AS PPH, tbl_PI_Functions.PPH_Goal, [Prod_Hrs]/[Paid_Hrs] AS PTP, tbl_PI_Functions.PTP_Goal
FROM tbl_PI_Functions INNER JOIN (tbl_PI_Associates INNER JOIN tbl_PI_Data ON tbl_PI_Associates.Assoc_Name = tbl_PI_Data.Assoc_Name) ON tbl_PI_Functions.Func_Name = tbl_PI_Data.Func_Name
Try using aliases as I suggested. It lends itself to cleaner code.
This is a start of the stored procedure ut you need to incorporate the other tables. Take a stab at it...
No need for the brackets unless you use a reserved word or have spaces.
If you create an alias as I suggested your code will be a lot cleaner when you migrate it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 13, 2011 at 12:47 pm
Your data does not match so that even after constructing a query with valid joins you will not return any results.
The func_name column in the tbl_PI_Functions Table does not match the func_name column in the tbl_PI_Data Table.
The Assoc_Name in the tbl_PI_Associates Table does not match the Assoc_Name tbl_PI_Data Table.
The Wizard to to convert to Access to horrible. For example, take a look at your Data Types. THe columns that should be defined as the Primary & Foriegn Keys are NVarChar(50).
I use SSIS (or DTS in the past) and change the Data Types and change the names of columns with reserved words, spaces, etc.
What is the purpose of the ID Column in the tbl_PI_Data Table? That should be the Primary Key the sample Data that you provided does not fil that model.
Where are your Constarints and Indexes?
You should update your data and when the data maches if you change the JOIN to an INNER you will not get any Nulls.
SELECT PiData.Date, PiData.Assoc_Name, PiAssoc.MgrName, PiData.Func_Name, PiData.Total_Proc,
PiData.Prod_Hrs, PiData.Paid_Hrs, Total_Proc/Prod_Hrs AS PPH, PiFunc.PPH_Goal,
PiData.Prod_Hrs/PiData.Paid_Hrs AS PTP, PiFunc.PTP_Goal
FROM tbl_PI_Functions AS PiFunc
FULL OUTER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name
FULL OUTER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2011 at 4:13 am
Check and update your Data:
SELECT PiData.Date, PiData.Assoc_Name, PiAssoc.MgrName, PiData.Func_Name, PiData.Total_Proc,
PiData.Prod_Hrs, PiData.Paid_Hrs, Total_Proc/Prod_Hrs AS PPH, PiFunc.PPH_Goal,
PiData.Prod_Hrs/PiData.Paid_Hrs AS PTP, PiFunc.PTP_Goal
FROM tbl_PI_Functions AS PiFunc
FULL OUTER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name
FULL OUTER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name
Create your Stored Procedure:
CREATE PROCEDURE AssocHours (@StartDate Datetime, @EndDate DateTime)
AS
SELECT PiData.Func_Name,PiAssoc.MgrName AS Manager, PiData.Assoc_Name AS Associate,
SUM(PiData.Total_Proc) AS Processed,SUM(PiData.Prod_Hrs) AS Productive_Hrs,
SUM(PiData.Paid_Hrs) AS Paid_Hrs
FROM tbl_PI_Functions AS PiFunc
INNER JOIN tbl_PI_Data AS PiData ON PiFunc.Func_Name = PiData.Func_Name
INNER JOIN tbl_PI_Associates PiAssoc ON PiData.Assoc_Name = PiAssoc.Assoc_Name
WHERE PiData.Date BETWEEN @StartDate AND @EndDate
GROUP BY PiData.Func_Name,PiAssoc.MgrName, PiData.Assoc_Name
Execute your SP:
EXEC AssocHours @StartDate = '2008-01-01', @EndDate = '2008-12-31'
Func_NameManagerAssociateProcessedProductive_HrsPaid_Hrs
Preptest2Lisa Taylor03.99
TestagainRony47.92
-------------------------------------------------- (2 row(s) affected)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply