|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-30 : 23:59:03
|
Warren writes "I am fairly new to SQL and am having trouble with a computed variable. In the sp below I compute the standard hours based on the start and end dates.
However, if someone has a termination date, then their standard hours should be less than what was calculated with the start and end date
ie @start = '5/1/01'
@end = '5/20/01'
then @count = 14 (all weekdays)
However the count should be less if the employee has been terminated before @end.
ie if c.termination_Date = '5/18/01'
then the @count(or another variable) = 13
any ideas would help tremendously.
thanks
warren
ALTER procedure WEEKLY_COMP_EXCEPTION
@start datetime,
@end datetime
AS /*count weekdays not in saturday and sunday*/
declare @current datetime
set @current = @start
declare @count int
set @count = 0
while @current <= @end
begin
if (datepart(dw, @current) not in (1,7))
set @count = @count + 1
set @current = dateadd(d,1,@current)
end
begin
/*select terminated resources within parameters*/
select
a.org_name AS OFFICE,
b.org_unit AS DEPARTMENT,
b.org_name AS DEPARTMENT_NAME,
c.name_last + ',' + c.name_first + ' ' + c.name_init as RESOURCE_NAME,
c.start_date AS START_DATE,
c.termination_date AS TERMINATION_DATE,
@count * 8 as STANDARD_HOURS,
SUM (d.units) as REPORTED_HOURS
FROM
parent_unit a,
org_unit b,
plv_resource c,
pld_Transactions d
WHERE
a.org_unit = b.parent_unit and
b.org_unit = c.org_unit and
c.resource_id = d.resource_id and
(ISNUMERIC(C.org_unit) = 1) and c.ACTIVE_FLAG = 0 and C.RES_TYPE IN (1,2) AND
c.termination_date >= @start and
c.termination_date <= @end and
d.applied_date >= @start and
d.applied_date <= @end
GROUP BY
a.org_name,b.org_unit,b.org_name,c.name_last,
c.name_first,c.name_init,c.active_flag,c.start_date,c.termination_date
HAVING
SUM(d.units) < @count *8
UNION
/*select active resources within parameters*/
SELECT
a.org_name as OFFICE,
b.org_unit as DEPARTMENT,
b.org_name as DEPARTMENT_NAME,
c.name_last + ',' + c.name_first + ' ' + c.name_init as RESOURCE_NAME,
c.start_date as START_DATE,
C.TERMINATION_DATE as TERMINATION_DATE,
@count * 8 as STANDARD_HOURS,
SUM(d.units) as REPORTED_HOURS
FROM
parent_unit a,
org_unit b,
plv_resource c,
pld_transactions d
WHERE
a.org_unit = b.parent_unit and
b.org_unit = c.org_unit and
c.resource_id = d.resource_id and
(ISNUMERIC(c.org_unit) = 1) and
c.ACTIVE_FLAG =1 and C.RES_TYPE IN (1,2) AND
d.applied_date >= @start and
d.applied_Date <= @end
GROUP BY
a.org_name,b.org_unit,b.org_name,c.name_last,
c.name_first,c.name_init,c.active_flag,c.start_date,c.termination_date
HAVING
SUM(d.units) < @count*8
END
" |
|
computed variable in sp