Stored Procedure and Join Statement.

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure and Join Statement.

Author

Topic

raybl
Starting Member

5 Posts

Posted - 2007-03-02 : 15:36:59

I'm very new to SQL and have been assigned to modify an existing Store Procedure. The existing is pasted below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE RPT_ACTIVITY_LEDGER
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS

SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4'

FROM ACTIVITIES
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

*************

The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-02 : 15:58:13

What will the join criteria be? IOW, how does Activities table relate to Employees?

SELECT t1.asdf, t1, ghjk, t2.E1, t2.E2
FROM Table1 t1
JOIN Table2 t2
ON t1.SomeColumn = t2.SomeColumn

Tara Kizer

raybl
Starting Member

5 Posts

Posted - 2007-03-02 : 16:33:30

Thanks for the response.

Basically, the current report (stored procedure) has the following field...ActEmpName AS 'Employee'. This field has the employees full name list as ex. John Doe.

The request is to be able to have the abillity to pull in the last name field, then first name within the Pivot Table used to access. Bascially they just want the ability to change at will the representation of the name from/to :

John Doe
Doe, John

Which isn't possible with the current stored procedure the pivot table is using.

The ACTIVITIES table has no fields seperating First and Last name, it just has one field with the fullname. However the EMPLOYEES table does E1=First Name, E2=Last Name, and E3=Fullname.

So my problem, with no real SQL background is to change the existing stored procedure that will allow for the Last Name, First Name fields being moved into the pivot tables. To do this I'm assuming I need to make these fields available in the data set. I've tried a few Join Statements added to the existing SP with no syntax errors. However, when I open the pivot table no data is returned. Here is an example:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE RPT_ACTIVITY_LEDGER_GSD
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS

SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4',
EmpFullName as 'Employee Full Name',
EmpFname as 'Employee First Name',
EmpLname as 'Employee Last Name'

FROM ACTIVITIES LEFT JOIN EMPLOYEES
ON ActEmpName=EmpFullName WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-02 : 16:36:27

FROM ACTIVITIES a
LEFT JOIN EMPLOYEES e
ON a.EmpFullName = e.EmpFname + ' ' + e.EmpLname

If the full name is not FirstName space LastName, then you'll need to fudge with the join condition a little.

Tara Kizer

raybl
Starting Member

5 Posts

Posted - 2007-03-05 : 13:25:17

Tara, Thanks again for the input. I'm still not there yet. I added the following into the existing store procedure:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE RPT_ACTIVITY_LEDGER_GSD
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS

SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4',
EmpFullName as 'Employee Full Name',
EmpFname as 'Employee First Name',
EmpLname as 'Employee Last Name'

FROM ACTIVITIES
LEFT JOIN EMPLOYEES
ON ACTIVITIES.ActEmpName=EMPLOYEES.EmpFname +''+ EMPLOYEES.EmpLname
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )
AND (ISNULL(EMPLOYEES.EmpFullName,'') LIKE @EmpName )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

***********

Once this is added I get no parse errors. I take the existing stored procedure that is called by the report and change it to call the new stored procedure. I run the report and I get no errors. However, no default data set for the current month returns. When I run the original report with the original stored procedure a default data set for the current month is returned. The front end report is using Office Web Components if that helps. Also, even though no default data is brought in I can click on the properties of the reports field list and bring fields into the report manually instead of them being there already. Any ideas on this one?

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 13:28:52

You have to determine what the join criteria will be. I showed an example.

This is the part that needs to be fixed:
ON ACTIVITIES.ActEmpName=EMPLOYEES.EmpFname +''+ EMPLOYEES.EmpLname

I can't see your data on your system, so I can't tell you exactly what you want. But I suspect that you want a space to be in between those single quotes. Otherwise, you are joining on TaraKizer (an example of course), rather than Tara Kizer.

To see what you need to do, run these queries:

SELECT TOP 10 EmpFname + '' + EmpLname
FROM EMPLOYEES

SELECT TOP 10 EmpFname + ' ' + EmpLname
FROM EMPLOYEES

SELECT TOP 10 ActEmpName
FROM ACTIVITIES

Tara Kizer

raybl
Starting Member

5 Posts

Posted - 2007-03-05 : 14:03:51

Hmm, I think I see the potential issue. The SELECT TOP 10 EmpFname + ' ' + EmpLname
FROM EMPLOYEES works just fine. The employee names using the above match first name space last name eg: Ray Blankenship in both fields.

However, I think the issue is in the ActEmpName
FROM ACTIVITIES.

In comparison, the EmpFname and EmpLname is just a list of all employees. Single entry in the entire table for each employee. The ActEmpName has multiple entries for each employee. This table is Project related table and each user has is listed multiple time in the ActEmpName field. Not sure if this really makes a difference though. Thanks for helping me out with this I appreciate it.

Actually, now that I think about it, it shouldn't. If i'm understanding correctly on the LEFT JOIN, doesn't this just say if EmpFname + ' ' + EmpLname is the same as ActEmpName then include EmpFullName as 'Employee Full Name', EmpFname as 'Employee First Name', EmpLname as 'Employee Last Name' from EMPLOYEES table in the data set?

Another thing, I was incorrect on the data set. When I run the report I get no defaul results. When I open the field list in OWC and drop fields from the Employee Table I get no data, I do get data from fields on the Activities. Well if nothing else it's been interesting learning the stuff.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 14:11:18

Please show a data example of what you are asking.

Tara Kizer

raybl
Starting Member

5 Posts

Posted - 2007-03-05 : 14:18:17

Another thought, I changed this to the following

FROM ACTIVITIES
LEFT JOIN EMPLOYEES
ON ACTIVITIES.ActEmpName=EMPLOYEES.EmpFullName

These two fields are identical instead of using the first and last. I just realized this so don't beat me up over it. LOL

I'm pulling data from Employees table now, however the default data set still doesn't come in like it does with the original stored procedure. I understand this is difficult for you with out seeing the data set so I appreciate anything you can add. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 14:35:49

I have no idea what you mean by default data. I've never used OWC, so I can't offer much help. All we can do is get your queries correct. So I'd suggest running the stored procedure in Query Analyzer and making sure it returns the correct data. Once you see it returns the correct data, then you know the problem is in OWC. If it doesn't return the correct data, then the problem is with your stored procedure. Always, always get it working first in Query Analyzer that way you aren't overcomplicating things.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources