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
 Long sproc. t-sql problems

Author  Topic 

tminer
Starting Member

2 Posts
Posted - 2005-12-06 : 12:00:13
I am very new to SQL and have a T-sql question. i am in over my head with a project given to me. it involves taking records from numerous tables and evaluating different aspects of them, followed by calculating fields based on numerous variables. first i will give my basic select statement:

select
LD.ldProject, LD.ldLaborCode, LD.ldDate, LD.ldRegHrs, LD.ldOvtHrs,
EM.emEmployee, EM.emLastName, EM.emFirstName, EM.emJobCostRate, EM.emStatus, EM.emProfCtr, EM.emSelect1, EM.emSelect3, EM.emSelect16, EM.emSelect18, EM.emSelect20, EM.emSelect21, EM.emTerminationDate,
CFGPCControl.Label
FROM
{ oj (Advantage.dbo.LD LD INNER JOIN Advantage.dbo.EM EM ON
LD.ldEmployee = EM.emEmployee)
INNER JOIN Advantage.dbo.CFGPCControl CFGPCControl ON
EM.emProfCtr = CFGPCControl.ProfCtr}

WHERE
LD.ldDate between 09-03-2005 AND 09-10-2005 AND
EM.emStatus = 'A' AND
EM.emSelect1 = 'Salary' AND
EM.emSelect3 = 'Engineer/Scientist 1' OR
EM.emSelect3 = 'Engineer/Scientist 2' OR

EM.emSelect3 = 'Project Engin/Scien.' OR
EM.emSelect3 = 'Field Supervisor' OR
EM.emSelect3 = 'Party Chief' OR
EM.emSelect3 = 'Drafter' OR
EM.emSelect3 = 'Drafting/Graphics PM' OR
EM.emSelect3 = 'Technician' AND
EM.emProfCtr <> 'COCO' AND
EM.emProfCtr <> 'COOF' AND
EM.emProfCtr <> 'COPE' AND
EM.emProfCtr <> 'NOAD' AND
EM.emProfCtr <> 'NOFI' AND
EM.emProfCtr <> 'NOMK' AND
EM.emProfCtr <> 'NOHR' AND
EM.emProfCtr <> 'NOGP' AND
EM.emProfCtr <> 'NOSY' AND
EM.emProfCtr <> 'EAAD' AND
EM.emProfCtr <> 'WMAD' AND
EM.emprofCtr <> 'HOAD'

first problem. i need the records grouped by employee for a certain time period. what i am doing is finding total hours in a certain period (one week) and computing a bonus. the problem is the employees post hours by the job, moybe 3 hours on one job, 2 on another, etc. i need these combined. also, there are numerous variables used in the calculation to find the bonus. productivity, weeks worked, etc. i will include them below. thanks in advance, i have hit a brick wall.

variables:

/*define vacused variable*/
Select @vacused = ldRegHrs + ldOvtHrs From LD
Where LD.ldLaborCode like "312??"

/* Define totalhours*/
Set @totalhours = (Select sum(ldRegHrs) From LD) + (Select sum(ldOvtHrs) From LD)

/* Define paid hours*/
If exists (Select * From EM where emSelect1 = "Salary")
Set @paidhours = @weeksworked * 40
Else
Set @paidhours = @totalhours

/* Define Direct Hours*/
Select @directhours = ldRegHrs + ldOvtHrs From LD
Where Not LD.ldProject Like "3000010??"
And
Not LD.ldLaborCode Like "999??"
And
Not LD.ldLaborCode Like "???99"
And
Not LD.ldLaborCode Like "3????"
And
Not LD.ldLaborCode Like "500??"

/*Define weekly direct hours*/
Select @weeklydirecthours = ldRegHrs + ldOvtHrs From LD
Where Not LD.ldProject Like "3000010??"
And
Not LD.ldLaborCode Like "999??"
And
Not LD.ldLaborCode Like "???99"
And
Not LD.ldLaborCode Like "3????"
And
Not LD.ldLaborCode Like "500??"
And LD.ldDate between 09-03-2005 and 09-10-2005

/* Define productivity*/
If (Select emEmployee from EM) = " " and @paidhours = 0
Set @productivity = 0
Else
If exists (Select * From EM where emSelect1 = "Salary")
Set @productivity = (@directhours - 0.0) / ((@weeksworked * 40) - @Vacused)
Else
Set @productivity = (@directhours) / (@paidhours)

If @paidhours = 0
Set @productivity = 0
Else
If (Select emselect1 from EM) = 'salary'
Set @productivity = (@directhours - (Select emSelect21 From EM)) / ((@weeksworked * 40) - @vacused)
Else
Set @productivity = (@directhours) / (@paidhours)
End

/* Define weeksworked */
If (Select emStatus From EM) = 'A' and (Select emSelect18 From EM) = '0'
Set @weeksworked = @weeknumber
Else
If @weeknumber < (Select emSelect18 From EM)
Set @weeksworked = 0
Else
If @weeknumber = (Select emSelect18 From EM)
Set @weeksworked = 1
Else
If @weeknumber > (Select emSelect18 From EM)
Set @weeksworked = @weeknumber - ((Select emSelect18 From EM) - 1)
Else
If @weeknumber < (Select emSelect18 From EM)
Set @weeksworked = 0
Else
If (Select emSelect18 From EM) = 0 and @weeknumber <= @termweekadj
Set @weeksworked = @weeknumber
Else
Set @weeksworked = @termweekadj


thanks again to anyone who can offer any help at all.

tminer

X002548
Not Just a Number

15586 Posts
Posted - 2005-12-06 : 12:42:22
please read the hint link in my sig, and post what it says in there



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

druer
Constraint Violating Yak Guru

314 Posts
Posted - 2005-12-06 : 12:58:25
1. Consider using the IN clause along with a NOT IN clause for the biggest chunk of the query up top. In other words if Variable = '1' OR Variable = '2' can become Variable IN ('1', '2') etc. Likewise Variable <> '1' AND Variable <> '2' can become Variable NOT IN ('1','2'). Just to make things easier to read, and to make it easy to change in the future.

2. Selecting totals and grouping is pretty easy to do. You just need to use the GROUP BY operator to tell the SUM'ing to be done by a particular column or multiple columns:

select EmployeeId, Sum(HourField) as TotalHours
from Employees
where DateWorked between '05/01/00' and '05/08/00'
group by EmployeeId

3. Where does the variable @weeksworked come from? I see it being used, but I'm not sure where it is coming from, or what you are using it for.

If you could post the table designs and give some sample data that would be a huge help to anyone who is going to try and help with this.
Go to Top of Page

tminer
Starting Member

2 Posts
Posted - 2005-12-06 : 13:25:03
To Clarify:

my question is how to generate the sql script for a stored procedure to return summary rows for each emp. (emp.'s have many rows each) after doing numerous calculations for each row.

DDL for the tables EM, PR, CFGPC, LD;

CREATE TABLE [dbo].[CFGPCControl] (
[ProfCtr] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Label] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LabDistProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LabDistTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiscProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiscTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsBSProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsBSTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsIndProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsIndTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsOtherProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitsOtherTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistributeOH] [bit] NOT NULL ,
[DistributeTarget] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DistributePass] [smallint] NULL ,
[OHAllocMethod] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OHBasis] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OHRate] [float] NULL ,
[OHProvisionalRate] [float] NULL ,
[OHVarianceProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OHVarianceTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayrollProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PayrollTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FICAProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FICATask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PlugAmtRev] [float] NULL ,
[PlugAmtDirLab] [float] NULL ,
[PlugAmtDirExp] [float] NULL ,
[PlugAmtIndLab] [float] NULL ,
[PlugAmtIndExp] [float] NULL ,
[PrintsProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrintsTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmplExpProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmplExpTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmplAdvProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmplAdvTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmplDefaultBank] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADPCompanyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeDisabled] [bit] NOT NULL ,
[XChargeRegMult] [float] NULL ,
[XChargeOHMult] [float] NULL ,
[XChargeRegDebitProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegDebitTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegCreditProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegCreditTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHDebitProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHDebitTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHCreditProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHCreditTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegFromProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegFromTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegToProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeRegToTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHFromProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHFromTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHToProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[XChargeOHToTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConsAccrProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConsAccrTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EM] (
[emEmployee] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[emLastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emFirstName] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emJobCostRate] [float] NULL ,
[emJobCostType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emJCOvtPct] [float] NULL ,
[emTargetRatio] [float] NULL ,
[emHoursPerDay] [float] NULL ,
[emHireDate] [datetime] NULL ,
[emRaiseDate] [datetime] NULL ,
[emStatus] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emProfCtr] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emBillingCategory] [smallint] NULL ,
[emBillingPool] [smallint] NULL ,
[emSSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emAddress1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emAddress2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emAddress3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emCity] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emCountry] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emPhone] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emFAX] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emEMail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect6] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect7] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect8] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect9] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect10] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect11] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect12] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect13] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect14] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect15] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emSelect16] [float] NULL ,
[emSelect17] [float] NULL ,
[emSelect18] [float] NULL ,
[emSelect19] [float] NULL ,
[emSelect20] [float] NULL ,
[emSelect21] [float] NULL ,
[emSelect22] [float] NULL ,
[emSelect23] [float] NULL ,
[emSelect24] [float] NULL ,
[emSelect25] [float] NULL ,
[emSelect26] [datetime] NULL ,
[emSelect27] [datetime] NULL ,
[emSelect28] [datetime] NULL ,
[emSelect29] [datetime] NULL ,
[emSelect30] [datetime] NULL ,
[emTKGroup] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emTKAdminLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emTKAdminEdit] [bit] NOT NULL ,
[emEKGroup] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emEKAdminLevel] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emEKAdminEdit] [bit] NOT NULL ,
[emMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emADPFileNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emADPCompanyCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emADPRateCode] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emProvCostRate] [float] NULL ,
[emProvBillRate] [float] NULL ,
[emProvCostOTPct] [float] NULL ,
[emProvBillOTPct] [float] NULL ,
[emDefaultLC1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emDefaultLC2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emDefaultLC3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emDefaultLC4] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emDefaultLC5] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emChangeDefaultLC] [bit] NOT NULL ,
[emTerminationDate] [datetime] NULL ,
[emTKAdminApproval] [bit] NOT NULL ,
[emUseTotalHrsAsStd] [bit] NOT NULL ,
[emJCSpecialOvtPct] [float] NULL ,
[emProvCostSpecialOTPct] [float] NULL ,
[emProvBillSpecialOTPct] [float] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PR] (
[prProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[prName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prTaskType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prPrincipal] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prProjMgr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSupervisor] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prClient] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prFee] [float] NULL ,
[prReimbAllow] [float] NULL ,
[prConsultFee] [float] NULL ,
[prBudOHRate] [float] NULL ,
[prStatus] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prRevType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prMultAmt] [float] NULL ,
[prProfCtr] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prUnitTable] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prStartDate] [datetime] NULL ,
[prEndDate] [datetime] NULL ,
[prPctComp] [float] NULL ,
[prLabPctComp] [float] NULL ,
[prExpPctComp] [float] NULL ,
[prBillByDefault] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prBillableWarning] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect6] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect7] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect8] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect9] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect10] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect11] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect12] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect13] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect14] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect15] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prSelect16] [float] NULL ,
[prSelect17] [float] NULL ,
[prSelect18] [float] NULL ,
[prSelect19] [float] NULL ,
[prSelect20] [float] NULL ,
[prSelect21] [float] NULL ,
[prSelect22] [float] NULL ,
[prSelect23] [float] NULL ,
[prSelect24] [float] NULL ,
[prSelect25] [float] NULL ,
[prSelect26] [datetime] NULL ,
[prSelect27] [datetime] NULL ,
[prSelect28] [datetime] NULL ,
[prSelect29] [datetime] NULL ,
[prSelect30] [datetime] NULL ,
[prMemo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prADPSpecial] [bit] NOT NULL ,
[prADPRegCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prADPRegFieldID] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prADPOvtCode] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prADPOvtFieldID] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prBudgetedFlag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prBudgetedLevels] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prBillProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prBillTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prXCharge] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prXChargeMethod] [smallint] NULL ,
[prXChargeMult] [float] NULL ,
[prAddress] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prTaskExport] [smallint] NULL ,
[prSumComp] [smallint] NULL ,
[prAppendTeam] [smallint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[LD] (
[ldKey] [int] NOT NULL ,
[ldProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldLaborCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldEmployee] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldTransType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldDate] [datetime] NULL ,
[ldPeriod] [int] NULL ,
[ldPostSeq] [smallint] NULL ,
[ldName] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldRegHrs] [float] NULL ,
[ldOvtHrs] [float] NULL ,
[ldRegAmt] [float] NULL ,
[ldOvtAmt] [float] NULL ,
[ldBillExt] [float] NULL ,
[ldRate] [float] NULL ,
[ldOvtPct] [float] NULL ,
[ldOvtRate] [float] NULL ,
[ldEmType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldPool] [smallint] NULL ,
[ldCategory] [smallint] NULL ,
[ldEmProfCtr] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldPrProfCtr] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldPrType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldRateType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldDebitEDPtr] [int] NULL ,
[ldCreditEDPtr] [int] NULL ,
[ldSuppressBill] [bit] NOT NULL ,
[ldBillStatus] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldComment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldBilledProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldBilledTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldBilledInvoice] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldBilledPeriod] [int] NULL ,
[ldXferProject] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldXferTask] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldXferLaborCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldProjectCost] [bit] NOT NULL ,
[ldBillTaxCodeOverride] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ldSpecialOvtHrs] [float] NULL ,
[ldSpecialOvtAmt] [float] NULL ,
[ldSpecialOvtPct] [float] NULL ,
[ldSpecialOvtRate] [float] NULL ,
[ldWrittenoffPeriod] [int] NULL
) ON [PRIMARY]
GO

Example of DML: i can't really give a good example with sample data. i hope the code with real table names is clear enough.


select
LD.ldProject, LD.ldLaborCode, LD.ldDate, LD.ldRegHrs, LD.ldOvtHrs,
EM.emEmployee, EM.emLastName, EM.emFirstName, EM.emJobCostRate, EM.emStatus, EM.emProfCtr, EM.emSelect1, EM.emSelect3, EM.emSelect16, EM.emSelect18, EM.emSelect20, EM.emSelect21, EM.emTerminationDate,
CFGPCControl.Label
FROM
{ oj (Advantage.dbo.LD LD INNER JOIN Advantage.dbo.EM EM ON
LD.ldEmployee = EM.emEmployee)
INNER JOIN Advantage.dbo.CFGPCControl CFGPCControl ON
EM.emProfCtr = CFGPCControl.ProfCtr}

WHERE
LD.ldDate between 09-03-2005 AND 09-10-2005 AND
EM.emStatus = 'A' AND
EM.emSelect1 = 'Salary' AND
EM.emSelect3 = 'Engineer/Scientist 1' OR
EM.emSelect3 = 'Engineer/Scientist 2' OR

EM.emSelect3 = 'Project Engin/Scien.' OR
EM.emSelect3 = 'Field Supervisor' OR
EM.emSelect3 = 'Party Chief' OR
EM.emSelect3 = 'Drafter' OR
EM.emSelect3 = 'Drafting/Graphics PM' OR
EM.emSelect3 = 'Technician' AND
EM.emProfCtr <> 'COCO' AND
EM.emProfCtr <> 'COOF' AND
EM.emProfCtr <> 'COPE' AND
EM.emProfCtr <> 'NOAD' AND
EM.emProfCtr <> 'NOFI' AND
EM.emProfCtr <> 'NOMK' AND
EM.emProfCtr <> 'NOHR' AND
EM.emProfCtr <> 'NOGP' AND
EM.emProfCtr <> 'NOSY' AND
EM.emProfCtr <> 'EAAD' AND
EM.emProfCtr <> 'WMAD' AND
EM.emprofCtr <> 'HOAD' AND
emtermiationdate IS NULL AND
@weeklydirecthours > 45 AND
@productivity > .95

I am expecting one row for each employee, or emEmployee in the sp, with productivity calculated for the week and bonus calculation inserted into a table.

I have tried many different approaches to calculate and filter with these variables, i am out of ideas. i hope this form is more appropriate Brett. thanks again
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site