SELECT Employee,TryWorkDay =(SELECT DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N'TryWorkDay' )
FROM dbo.PAPersonInfo DesignFormulas='CONVERT(VARCHAR(10), CASE WHEN IncomeDate IS NULL THEN NULL
ELSE CASE WHEN W.WorkStation = ''员工''
OR W.WorkStation =''技工''
THEN 60
+ IncomeDate
ELSE IncomeDate
+ 90
END
END, 120)'如何使用DesignFormulas进行公式换算 引号是双引号
select
CASE WHEN IncomeDate IS NULL THEN NULL
WHEN IncomeDate IS NOT NULL and (W.WorkStation = '员工' OR W.WorkStation ='技工')THEN CONVERT(VARCHAR(10), dateadd(day,60,IncomeDate),120)
ELSE CONVERT(VARCHAR(10), dateadd(day,90,IncomeDate),120)
END
from tb
DesignFormulas='CONVERT(VARCHAR(10), CASE WHEN IncomeDate IS NULL THEN NULL
ELSE CASE WHEN W.WorkStation = ''员工''
OR W.WorkStation =''技工''
THEN 60
+ IncomeDate
ELSE IncomeDate
+ 90
END
END, 120)'如何把这套公式应用到关联的语句中,3楼不符合要求
,TryWorkDay =CONVERT(VARCHAR(10), CASE WHEN IncomeDate IS NULL THEN NULL
ELSE (CASE WHEN W.WorkStation in ('员工','技工') THEN 60 else 90 END + IncomeDate)
END, 120)
FROM dbo.PAPersonInfo a
left join dbo.PAWageSetCalculate b on WageItem = N'TryWorkDay'
而且是几十个字段用到公式
set @sql='SELECT Employee'
select @sql=isnull(@sql,'')+',TryWorkDay =(SELECT DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N''TryWorkDay'')'
FROM dbo.PAPersonInfo
print @sql
exec(@sql)
select @sql=isnull(@sql,'')+'TryWorkDay =(SELECT DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N''TryWorkDay'' )'
set @sql='SELECT Employee,'+@sql+' FROM dbo.PAPersonInfo'
print @sql
exec(@sql)
是加天数..你这个也不可行.这个加天数的条件语句我是存在一字段DesignFormulas里面的,可以动态改变设定的。
SELECT Employee,TryWorkDay =(SELECT DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N'TryWorkDay' ) FROM dbo.PAPersonInfo结果张三 CONVERT(VARCHAR(10)....省略了我要的结果是张三 1(即公式参与计算的结果) 类似例子
DECLARE
@AB AS VarChar(1000)
BEGIN
select @AB=case when 1=1 then 3 else 1 end
SELECT 1,isnull(@AB,99) ,2
END
结果 1 3 2只是 现在@AB 是字段, 公式放在字段里了。 1=1 也变成了字段参与明白了吗
declare @sql varchar(8000)
declare @TryWorkDay varchar(2000)
select @TryWorkDay = DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N'TryWorkDay'
set @sql = 'SELECT Employee,TryWorkDay =('+@TryWorkDay+') FROM dbo.PAPersonInfo'
exec(@sql)
如果OK,记得结贴哦
returns varchar(10) as
begin
...
end
goselect .. dbo.fn_DesignFormulas(IncomeDate, W.WorkStation) ..
CREATE TABLE #tp
(
id INT IDENTITY,
[time] datetime
)CREATE TABLE #tb
(
id INT IDENTITY,
format VARCHAR(30)
)INSERT INTO #tp
SELECT '2010-06-20 14:35:00' UNION ALL
SELECT '2010-01-20 14:32:00'INSERT INTO #tb
SELECT 'CONVERT(VARCHAR(7),[time],120)' DECLARE @SQL VARCHAR(100)
DECLARE @FORMAT VARCHAR(100)
SELECT @FORMAT = FORMAT FROM #TB WHERE ID = 1
SET @SQL = 'SELECT [TIME] =('+@FORMAT+') FROM #TP'
EXEC(@SQL)TIME
-------
2010-06
2010-09(2 row(s) affected)
declare @TryWorkDay varchar(2000)
select @TryWorkDay = DesignFormulas FROM dbo.PAWageSetCalculate WHERE WageItem = N'TryWorkDay'
set @sql = 'SELECT Employee,TryWorkDay =('+@TryWorkDay+') FROM dbo.PAPersonInfo'
exec(@sql)select @TryWorkDay = DesignFormulas FROM dbo.PAWageSetCalculate
WHERE WageItem = N'TryWorkDay' and (。。如 PAWageSetCalculate.A=PAPersonInfo.A' )
省略号那有个查询是根据后面,这个怎么加进去?
改成:
select @TryWorkDay = DesignFormulas FROM dbo.PAWageSetCalculate
WHERE WageItem = N'TryWorkDay' and A = (Select A FROM PAPersonInfo where ...' )
你好,我觉得不能先select @TryWorkDay 查出 @TryWorkDay 的值,
因为后面的查询PAPersonInfo得出的公式会有不一样的