存储过程统计年月日的访问人数

   为了统计每天、每月的体侧人数,利用sql函数写成了存储过程,如下:

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [dbo].[usp_GetStatisticsValue]
ALTER PROC [dbo].[usp_GetStatisticsValue]
AS
BEGIN

--定义一张临时变量表 DECLARE @tbl TABLE(year int,month int,day int) DECLARE @total int DECLARE @year int DECLARE @month int DECLARE @day int
--tbl_physical_fitness 体侧项目表 获得总的体健数 SELECT @total = COUNT(1) FROM [dbo].[tbl_physical_fitness] INSERT INTO @tbl SELECT DATEPART(YEAR,exam_date),DATEPART(MONTH,exam_date),DATEPART(DAY,exam_date) FROM tbl_exam WHERE DATEPART(YEAR,exam_date) = '2015' -- DATEPART(YEAR,GETDATE()) SELECT @year = @@ROWCOUNT SELECT @month = COUNT(1) FROM @tbl WHERE month = DATEPART(MONTH,GETDATE()) SELECT @day = COUNT(1) FROM @tbl WHERE day = DATEPART(DAY,DATEADD(DAY,-1,GETDATE())) AND month = DATEPART(MONTH,GETDATE()) SELECT @day AS pre_day,@month month,@year AS year,@total AS total END

 



posted @ 2016-07-31 18:00  石shi  阅读(682)  评论(0编辑  收藏  举报