sql存储过程

USE [AutoRent]
GO

/****** Object:  StoredProcedure [dbo].[USP_ViolationAmountDateGroupSelect]    Script Date: 09/22/2015 11:27:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




ALTER  PROCEDURE [dbo].[USP_ViolationAmountDateGroupSelect]
    @GroupID int = null,--分组条件
    @ProvinceID int = null,--违章省份
    @ViolationCityID int = NULL,--违章城市
    @BeginAddDatetime datetime = NULL,--起始违章时间
    @EndAddDatetime datetime = NULL,--结束违章时间
    @DateYear int = NULL,--违章年份
    @HalfYear int = NULL,--上、下半年
    @OrderStatus int = NULL , --订单状态
    @DealResult int = NULL    --违章处理状态

AS 
BEGIN
--GroupID
--1: 年
--2: 季度
--3: 月
--4: 周
DECLARE @SQL nvarchar(4000)
CREATE TABLE #Temp
(
YearDate int,
QuarterDate int,
MonthDate int,
WeekDate int,
OrderCount  int,
DetailCount int,
ViolationFines float,
AgencyFees float,
Postage float
)
SET @SQL = '
INSERT INTO #Temp
SELECT
    YEAR(VO.AddDatetime) AS YearDate,--年
    DATEPART(qq,VO.AddDatetime) AS QuarterDate,--季度
    MONTH(VO.AddDatetime) AS MonthDate,--月
    CASE WHEN MONTH(VO.AddDatetime) = 1 THEN DATEPART(week,VO.AddDatetime)
         ELSE DATEPART(week,VO.AddDatetime)-DATEPART(week,VO.AddDatetime-day(VO.AddDatetime)) + 1 
    END AS WeekDate,    --周
    COUNT(distinct VO.ViolateOrderID) AS OrderCount,--违章订单数
    COUNT(VD.ViolationsID) AS DetailCount,--违章记录数
    SUM(ISNULL(VD.ViolationAmount,0)) AS ViolationFines,--罚款金额
    SUM(ISNULL(VD.AgencyFees,0)) AS AgencyFees,--代办费
    SUM(ISNULL(VO.Postage,0)) AS Postage--邮寄费
FROM
    dbo.TB_ViolationsOrder VO LEFT OUTER JOIN 
    dbo.TB_ViolationsDetail VD ON VO.ViolateOrderID = VD.ViolateOrderID
'
IF (NOT @ProvinceID IS NULL)--违章省份
 BEGIN
  SET @SQL = @SQL + ' LEFT OUTER JOIN
    dbo.TB_Metadata M ON VD.ViolationCityID = M.MetadataID
    '
  END

SET @SQL = @SQL + '
WHERE 
    1 = 1
'

IF (NOT @ProvinceID IS NULL)--违章省份
 BEGIN
  SET @SQL = @SQL + ' AND M.ParentID = ' + CONVERT(varchar,@ProvinceID)
 END

IF (NOT @ViolationCityID IS NULL)--违章城市
 BEGIN
  SET @SQL = @SQL + ' AND VD.ViolationCityID = ' + CONVERT(varchar,@ViolationCityID)
 END

IF (NOT @BeginAddDatetime IS NULL)--起始违章时间
 BEGIN
  SET @SQL = @SQL + ' AND VO.AddDatetime >= ''' + CONVERT(varchar,@BeginAddDatetime,23) + ''''
 END

IF (NOT @EndAddDatetime IS NULL)--结束违章时间
 BEGIN
  SET @SQL = @SQL + ' AND VO.AddDatetime <= ''' + CONVERT(varchar,dateadd(dd,1,@EndAddDatetime),23) + ''''
 END

IF (NOT @DateYear IS NULL)--违章时间(半年)
 BEGIN
  SET @SQL = @SQL + ' AND YEAR(VO.AddDatetime) = ' + CONVERT(varchar,@DateYear) 
  IF (@HalfYear = 0)
    BEGIN 
        SET @SQL = @SQL + ' AND MONTH(VO.AddDatetime) >= 1 AND MONTH(VO.AddDatetime) <= 6'
    END
  ELSE
    BEGIN 
        SET @SQL = @SQL + ' AND MONTH(VO.AddDatetime) >= 7 AND MONTH(VO.AddDatetime) <= 12'
    END
    
 END

IF (NOT @DealResult IS NULL)--违章处理状态
 BEGIN
  SET @SQL = @SQL + ' AND VD.DealResult = ' + CONVERT(varchar,@DealResult)
 END

IF (NOT @OrderStatus IS NULL)--订单状态
 BEGIN 
  SET @SQL = @SQL + ' AND VO.OrderStatus ' 
            + Case CONVERT(varchar,@OrderStatus)
                WHEN '1' THEN ' in (1,2)'    --成功单--订单状态 1：办理中 2：已完成
                WHEN '2' THEN '= 3' --等待支付
                WHEN '3' THEN '= 4'    --已撤销
              END
 END
 
  SET @SQL = @SQL + '
GROUP BY 
    YEAR(VO.AddDatetime),--年
    DATEPART(qq,VO.AddDatetime),--季度
    MONTH(VO.AddDatetime),--月
    CASE WHEN MONTH(VO.AddDatetime) = 1 THEN DATEPART(week,VO.AddDatetime)
         ELSE DATEPART(week,VO.AddDatetime)-DATEPART(week,VO.AddDatetime-day(VO.AddDatetime)) + 1 
    END--周
'

--print @SQL
EXEC SP_EXECUTESQL @SQL

SET @SQL = '
SELECT
'+

    CASE @GroupID 
        WHEN 1 THEN ' CONVERT(varchar,YearDate)' + '+' + '''' + '年' + ''''
        WHEN 2 THEN ' CONVERT(varchar,YearDate)' + '+' + '''' + '年 第' + '''' + '+' + 'CONVERT(varchar,QuarterDate)' + '+' + '''' + '季度' + ''''
        WHEN 3 THEN ' CONVERT(varchar,YearDate)' + '+' + '''' + '年 第' + '''' + '+' + 'CONVERT(varchar,MonthDate)' + '+' + '''' + '月' + ''''
        WHEN 4 THEN ' CONVERT(varchar,YearDate)' + '+' + '''' + '年 第' + '''' + '+' + 'CONVERT(varchar,MonthDate)' + '+' + '''' + '月 第' + '''' + '+' + 'CONVERT(varchar,WeekDate)' + '+' + '''' + '周' + ''''
    END + ' AS GroupDate,
    SUM(OrderCount) AS OrderCount,--违章订单数
    SUM(DetailCount) AS DetailCount,--违章记录数
    SUM(ViolationFines) AS ViolationFines,--罚款金额
    SUM(AgencyFees) AS AgencyFees,--代办费
    SUM(Postage) AS Postage--邮寄费
FROM #Temp
GROUP BY
'
+
CASE @GroupID
        WHEN 1 THEN 'YearDate'
        WHEN 2 THEN 'YearDate,QuarterDate'
        WHEN 3 THEN 'YearDate,MonthDate'
        WHEN 4 THEN 'YearDate,MonthDate,WeekDate'
    END
--print @SQL
EXEC SP_EXECUTESQL @SQL

END

GO