12月 24

     

USE [DB_DianPing]
GO
/****** 对象:  UserDefinedFunction [dbo].[Func_GetTaskItemByItemID]    脚本日期: 12/24/2010 16:57:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[Func_GetTaskItemByItemID]
(
 @ID int
)
Returns Table
as
Return
(
 with TaskItem(ItemID,ItemName,CreatedDate,LastDate,TaskID,ParentItemID,PlannedStartTime,PlannedEndTime,StartTime,EndTime,PlannedDuration,ActualDuration,ConcernNoList,Priority)
 as
 (
  Select ItemID,ItemName,CreatedDate,LastDate,TaskID,ParentItemID,PlannedStartTime,PlannedEndTime,StartTime,EndTime,PlannedDuration,ActualDuration,ConcernNoList,Priority
  from DP_TaskItem
  where ItemID=@ID
  Union ALL
  Select A.ItemID,A.ItemName,A.CreatedDate,A.LastDate,A.TaskID,A.ParentItemID,A.PlannedStartTime,A.PlannedEndTime,A.StartTime,A.EndTime,A.PlannedDuration,A.ActualDuration,A.ConcernNoList,A.Priority
  from DP_TaskItem A
  JOIN TaskItem N on N.ParentItemID=A.ItemID
 )
 Select ItemID,ItemName,CreatedDate,LastDate,TaskID,ParentItemID,PlannedStartTime,PlannedEndTime,StartTime,EndTime,PlannedDuration,ActualDuration,ConcernNoList,Priority
 From TaskItem
)

 

时隔N年,果然用上了啊.

;with f as
(
select EmployeeNum,SuperiorEmpNum from BPMDBTemp.dbo.Lurelationship a where   SuperiorEmpNum=88014212
union all
select b.EmployeeNum,b.SuperiorEmpNum from BPMDBTemp.dbo.Lurelationship b INNER JOIN f ON b.SuperiorEmpNum=f.EmployeeNum WHERE
  b.eAppType='eAppraisal' AND b.EmployeeNum<>b.SuperiorEmpNum
),CC AS (
SELECT DISTINCT CostCenter,CompanyCode FROM f LEFT JOIN BPMDBTemp.dbo.LuEmployee ON f.EmployeeNum=LuEmployee.EmployeeNum
 WHERE CostCenter IS NOT NULL AND CompanyCode IS not NULL
)
 SELECT   SUM(NET_AMOUNT)  ToatlAmount FROM PR_Header h
                                                 INNER JOIN PR_Detail d ON h.PR_No=d.PR_No
INNER JOIN CC ON CC.CostCenter=h.CC_Code AND CC.CompanyCode=h.Co_Code
                                                 WHERE
                                                    h.pr_no IN (SELECT pr_no FROM pr_detail PRD WHERE PRd.capex_opex = 'C' AND budget_nonbudget='NB' AND cxIInd = 'N')    
                                                 AND   h.RecSts NOT IN ('12','21','4','5','51','65','9')
                                           AND h.cxIInd = 'N' 
 
                                                 AND h.CreatedOn>=@DateYear

 

written by ocean

12月 06

大家都知道扩展方法是不能直接在2.0中使用的

需要引用一个‍System.Core的dll

不过现在有更加简单的方法了

只要在工程项目中加入以下代码就OK啦

‍namespace System.Runtime.CompilerServices
{
     [AttributeUsage(AttributeTargets.Method | AttributeTargets.Class | AttributeTargets.Assembly)]
     public sealed class ExtensionAttribute : Attribute { }
}

简直太赞了

written by ocean