sqlserver今日统计数据存储过程

日期个格式

如：‘年月日’ 2022-03-15
CONVERT(varchar(100), 日期, 23)

判断

case when 判断条件
then
结果1
else
结果2
end

完整的

--首页今日统计数据
ALTER PROCEDURE [dbo].[homeDayTotal]
@CompanyId varchar(10), --公司id
@ProjectId varchar(10) --产品id
AS
DECLARE @sql1 varchar(2000)
DECLARE @sql2 varchar(2000)
BEGIN
    if @ProjectId=''
    BEGIN
        set @sql2 = ''
    END
    else
    BEGIN
        set @sql2 = 'and jk.ProjectId='+@ProjectId
    END

    set @sql1 = 'select 
    CONVERT(varchar(100), Jk.ApplyDate, 23) times,
    (select count(Id) from SysUserMember where RegisterSource in(select top 1 AppId from Sys_App where CompanyId='+@CompanyId+' order by Id desc) and convert(VARCHAR(100),RegistreDate,23)=CONVERT(varchar(100), GETDATE(), 23)) as zhuceshu,
    count(jk.Id) as shengqingshu,
    count(case when jk.IsCheckedState=4 THEN jk.Id else null end) as shengqingtongguoshu,
    count(case when jk.IsCheckedState=1 THEN jk.Id else null end) as weiqianyue,
    count(case when jk.ApplyType=1 THEN jk.Id else null end) as jishentongguoshu,
    count(case when jk.ApplyType=2 THEN jk.Id else null end) as jishenjujueshu, 
    count(case when jk.IsCheckedState>1 THEN jk.Id else null end) as renshenheshu,
    count(case when jk.State=2 THEN jk.Id else null end) as beijujueshu,
    sum(jk.Amount) as jiekuanjine,
    sum(case when jk.State=7 then jk.Loanamount else 0 end) as daifangkuanjine,
    count(case when jk.State=7 then jk.Id else null end) as daifangkuanbishu,
    sum(case when jk.State=5 then jk.Loanamount else 0 end) as yifangkuanjine,
    count(case when jk.State=5 then jk.Id else null end) as yifangkuanbishu,
    count(case when jk.Isrenew=1 then jk.Id else null end) as xujieshu,
    count(case when jk.State=8 then jk.Id else null end) as yijieqingshu
     from SysJkApply as jk left JOIN SysRePayment as rp on jk.OrderNO=rp.OrderNO
    where CONVERT(varchar(100), Jk.ApplyDate, 23)=CONVERT(varchar(100), GETDATE(), 23) 
    '+@sql2+' 
    and jk.CompanyId='+@CompanyId+'  
    GROUP BY CONVERT(varchar(100), Jk.ApplyDate, 23)'
--  print @sql1
    EXEC(@sql1)
END

页面效果

image.png