请问下面的SQL语句如何写成存储过程??
declare @FirstDay datetime
declare @NextMonth datetime
declare @Days int
select @FirstDay=convert(varchar(8),min(dates),120)+ '01 '
from @t1
set @NextMonth=dateadd(month,1,@FirstDay)
set @Days=datediff(day,@FirstDay,@NextMonth)
print @NextMonth
print @Days
select
t.MName as 矿名,
t.SCode as 编号,
t.SName as 名称,
t.SType as 类型,
sum(case t.Status when 0 then 1 else 0 end) as 累计开次数,
case when sum(t.Times) <24*60*60 then convert(varchar(8),dateadd(ss,sum(t.Times),0),108)
else right(convert(char(19),dateadd(ss,sum(t.Times),-1),120),11) end as 累计开时间,
sum(case t.Status when 0 then 0 else 1 end) as 累计关次数,
right(convert(char(19),dateadd(ss,(@Days*24*60*60-sum(t.Times)),-1),120),11) as 累计关时间
from
(select
a.*,
(case a.Status
when 0
then datediff(ss,a.Dates,isnull(
(select
top 1 Dates
from
@t1
where
Mname=a.Mname and SCode=a.SCode and Status!=0 and Dates> a.Dates
order by
Dates),@NextMonth)) else 0 end) as Times
from
@t1 a ) t
group by
t.MName,t.SCode,t.SName,t.SType
--结果
矿名 编号 名称 类型 累计开次数 累计开时间 累计关次数 累计关时间
---------- ---------- ------------- ---------- ----------- ------------- ----------- -------------
A矿 c001 一层传感器 风门 4 01 00:24:41 6 29 23:35:19
A矿 c002 二层传感器 风机 1 29 00:10:10 1 01 23:49:50
B矿 d001 总回风 风门 2 00:40:10 2 30 23:19:50
[解决办法]
create proc sp_name (@FirstDay datetime）
as
declare @NextMonth datetime
declare @Days int
select @FirstDay=convert(varchar(8),min(dates),120)+ '01 '
from @t1
set @NextMonth=dateadd(month,1,@FirstDay)
set @Days=datediff(day,@FirstDay,@NextMonth)
print @NextMonth
print @Days
select
t.MName as 矿名,
t.SCode as 编号,
t.SName as 名称,
t.SType as 类型,
sum(case t.Status when 0 then 1 else 0 end) as 累计开次数,
case when sum(t.Times) <24*60*60 then convert(varchar(8),dateadd(ss,sum(t.Times),0),108)
else right(convert(char(19),dateadd(ss,sum(t.Times),-1),120),11) end as 累计开时间,
sum(case t.Status when 0 then 0 else 1 end) as 累计关次数,
right(convert(char(19),dateadd(ss,(@Days*24*60*60-sum(t.Times)),-1),120),11) as 累计关时间
from
(select
a.*,
(case a.Status
when 0
then datediff(ss,a.Dates,isnull(
(select
top 1 Dates
from
@t1
where
Mname=a.Mname and SCode=a.SCode and Status!=0 and Dates> a.Dates
order by
Dates),@NextMonth)) else 0 end) as Times
from
@t1 a ) t
group by
t.MName,t.SCode,t.SName,t.SType
//这样应该就可以了
