存储过程-统计

CREATE PROCEDURE Web_Master_GetSatationCardStat
 
 @StationStr NVARCHAR(1000) = '' -- 站点查询条件
AS
Declare @whereSql NVARCHAR(1000)
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 IF (@StationStr = '' )
    return N'站点条件不能为空';

 SET @whereSql = ' SELECT  StationID,CardType ,  COUNT(ACardID) AS TypeNum  , SUM(MemberScore) AS TypeScore
    ,SUM( CASE WHEN Used = 1 THEN  1 ELSE 0 END) As Used
 ,SUM (CASE WHEN Nullity = 1 THEN 1 ELSE 0 END) AS Nullity
    FROM [AssociatorCard](NOLOCK)
    WHERE  '+ @StationStr +'
 GROUP BY CardType ,StationID ';
  exec sp_executesql @whereSql
  
END
GO

 

 

----------

 

 

CREATE PROCEDURE Web_Master_GetFillCardStat

@WhereStr NVARCHAR(1000) -- 查询条件
AS
Declare @execSql NVARCHAR(1000)
BEGIN
SET NOCOUNT ON;

SET @execSql = ' select
case when grouping(StationID)=1 then ' + '-3'+ ' else StationID end StationID ,
case when grouping(CardType)=1 then ' + '-1'+ ' else cast(CardType as varchar) end CardType ,
COUNT(RecordID) AS TypeNum  ,
SUM( CASE WHEN OrdersID = ''''  THEN  1 ELSE 0 END) As FillOnline ,
SUM (CASE WHEN OrdersID <> '''' THEN  1 ELSE 0 END) AS FillReal
from RecordCardFill (NOLOCK)
where '+ @WhereStr + '
group by CardType , StationID with ROLLUP 
having  grouping(StationID)=0 or grouping(CardType)=1  '
 ;

EXEC sp_executesql @execSql
  
END
GO

 

--------------------

 

CREATE PROCEDURE Web_Master_GetFillCardStat

@WhereStr NVARCHAR(1000) -- 查询条件
AS
Declare @execSql NVARCHAR(1000)
BEGIN
SET NOCOUNT ON;
-----分站点、卡类型的统计-----
SET @execSql = ' select
StationID ,
CardType ,
COUNT(RecordID) AS TypeNum  ,
SUM( CASE WHEN OrdersID = ''''  THEN  1 ELSE 0 END) As FillOnline ,
SUM (CASE WHEN OrdersID <> '''' THEN  1 ELSE 0 END) AS FillReal
from RecordCardFill (NOLOCK)
where '+ @WhereStr + '
group by CardType , StationID ' ;

EXEC sp_executesql @execSql

-----分卡类型的统计、用于图表显示-------
SET @execSql = ' select
CardType ,
COUNT(RecordID) AS TypeNum  ,
SUM( CASE WHEN OrdersID = ''''  THEN  1 ELSE 0 END) As FillOnline ,
SUM (CASE WHEN OrdersID <> '''' THEN  1 ELSE 0 END) AS FillReal
from RecordCardFill (NOLOCK)
where '+ @WhereStr + '
group by CardType ';

EXEC sp_executesql @execSql

END
GO