set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-- =============================================
-- Author: wkk
-- Create date: 20100414
-- Description: 职位趋势统计
-- =============================================
ALTER PROCEDURE [dbo].[UP_StatisticsPost]
@StartDate DATETIME,
@EndDate DATETIME,
@StatisticsType NVARCHAR(5)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(4000) /*实际存在的数据*/
SELECT TOP 10 * INTO #t_ShowData FROM
(SELECT et_s_PostVisit.PostID,et_s_PostVisit.VisitCount,et_s_PostVisit.CreateDate FROM et_s_PostVisit
WHERE CONVERT(NVARCHAR(10),CreateDate,120) BETWEEN CONVERT(NVARCHAR(10),@StartDate,120) AND CONVERT(NVARCHAR(10),@EndDate,120)
UNION
SELECT t_Full_Post.[iID]
,ISNULL(t_Actual_Post_Detail.[Count],0)[Count]
,CONVERT(NVARCHAR(10),GETDATE(),120) FROM
(
SELECT tb_b_FunType.iID,tb_b_FunType.nvcName
FROM tb_b_FunType
WHERE LEN(tb_b_FunType.iID)=2 AND tb_b_FunType.tiIsValid=1)t_Full_Post
LEFT JOIN
(
SELECT LEFT(iFunTypeID1,2)[ID],COUNT(*)[Count]
FROM et_d_JobInfo
WHERE iFunTypeID1<>-1
AND CONVERT(NVARCHAR(10),dtUpdateTime,120) BETWEEN CONVERT(NVARCHAR(10),GETDATE(),120) AND CONVERT(NVARCHAR(10),GETDATE(),120)
AND tiJobState IN (1,4) GROUP BY LEFT(iFunTypeID1,2))t_Actual_Post_Detail
ON t_Full_Post.iID=t_Actual_Post_Detail.[ID])t_Actual_Data
WHERE CONVERT(NVARCHAR(10),CreateDate,120) BETWEEN CONVERT(NVARCHAR(10),@StartDate,120) AND CONVERT(NVARCHAR(10),@EndDate,120)
ORDER BY t_Actual_Data.VisitCount DESC SELECT t_Full_Data.[Date],tb_b_FunType.[nvcName],t_Full_Data.[VisitCount]
INTO #T_Convert_Day
FROM
(SELECT t_Base_Data.[Date],t_Base_Data.[PostID],ISNULL(#t_ShowData.[VisitCount],0)[VisitCount] FROM
(SELECT t_Full_Date.[Date],#t_ShowData.[PostID] FROM
(SELECT CONVERT(NVARCHAR(10),[Date],120)[Date]
FROM dbo.f_getdate(@StartDate,@EndDate,NULL))t_Full_Date FULL JOIN #t_ShowData ON 1=1)t_Base_Data LEFT JOIN #t_ShowData
ON CONVERT(NVARCHAR(10),t_Base_Data.[Date],120)=CONVERT(NVARCHAR(10),#t_ShowData.[CreateDate],120)
AND t_Base_Data.[PostID]=#t_ShowData.[PostID])t_Full_Data LEFT JOIN tb_b_FunType ON t_Full_Data.[PostID]=tb_b_FunType.[iID] SET @SQL=' SELECT [nvcName] [职位],'
SELECT @SQL=@SQL+' SUM(CASE WHEN [Date]='''+[Date]+''' THEN [VisitCount] ELSE 0 END) ['+[Date]+'],' FROM (SELECT DISTINCT [Date] FROM #T_Convert_Day)T_Date
SET @SQL=LEFT(@SQL,LEN(@SQL)-1)+' FROM #T_Convert_Day GROUP BY [nvcName] ORDER BY LEN([nvcName]) DESC' EXEC (@SQL) DROP TABLE #t_ShowData
DROP TABLE #T_Convert_Day
END
目前的存储过程是根据天来进行统计的,因为统计方式有四种:按日统计、按月统计、按季统计、按年统计
请问如果换成按月或其他的,应该重新写个,还是直接在动态语句里判断,请做过此类的高手帮我解答一下
select datepart(mm,getdate()) --月
select datepart(qq,getdate()) --季
select datepart(yy,getdate()) --年
用这个,自己改改代码应该能解决吧
select datepart(mm,getdate()) --月
select datepart(qq,getdate()) --季
select datepart(yy,getdate()) --年