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