想写个存储过程,传两个参数 :一个 时间,还有一个是周期数
有个表里有两个时间,cr.tabDate 有可能是空的,下面的where中case怎么写
关键是
(case ISNULL(cr.tabDate,0)
when 0 then select datediff(DAY,cr.billdate,@nowData) = @day
else (select datediff(DAY,cr.tabDate,@nowData)) = @day
end)要怎么加create procedure ud_getRemindRepair
@nowData datetime,
@day varchar
asselect gr.billid,cr.code,cr.billdate,cr.Mobile from goodsrecord gr
left join clientrepair cr on cr.repairid=gr.billid
where ISNULL(gr.goodshabitus,0)=0 and ISNULL(gr.vrhabitus,0)=0 and notetype=50 and
(case ISNULL(cr.tabDate,0)
when 0 then select datediff(DAY,cr.billdate,@nowData) = @day
else (select datediff(DAY,cr.tabDate,@nowData)) = @day
end)
group by gr.billid,cr.code,cr.billdate,cr.Mobile
有个表里有两个时间,cr.tabDate 有可能是空的,下面的where中case怎么写
关键是
(case ISNULL(cr.tabDate,0)
when 0 then select datediff(DAY,cr.billdate,@nowData) = @day
else (select datediff(DAY,cr.tabDate,@nowData)) = @day
end)要怎么加create procedure ud_getRemindRepair
@nowData datetime,
@day varchar
asselect gr.billid,cr.code,cr.billdate,cr.Mobile from goodsrecord gr
left join clientrepair cr on cr.repairid=gr.billid
where ISNULL(gr.goodshabitus,0)=0 and ISNULL(gr.vrhabitus,0)=0 and notetype=50 and
(case ISNULL(cr.tabDate,0)
when 0 then select datediff(DAY,cr.billdate,@nowData) = @day
else (select datediff(DAY,cr.tabDate,@nowData)) = @day
end)
group by gr.billid,cr.code,cr.billdate,cr.Mobile
@nowData datetime=null,
@day int=0
asselect gr.billid,cr.code,cr.billdate,cr.Mobile from goodsrecord gr
left join clientrepair cr on cr.repairid=gr.billid
where
datediff(DAY,cr.billdate,isnull(@nowData,cr.billdate)) = @day
group by gr.billid,cr.code,cr.billdate,cr.Mobile
select gr.billid,cr.code,cr.billdate,cr.Mobile from goodsrecord gr
left join clientrepair cr on cr.repairid=gr.billid
where ISNULL(gr.goodshabitus,0)=0 and ISNULL(gr.vrhabitus,0)=0 and notetype=50 and
(case ISNULL(cr.tabDate,0)
when 0 then datediff(DAY,cr.billdate,@nowData)
else datediff(DAY,cr.tabDate,@nowData)
end) = @day
group by gr.billid,cr.code,cr.billdate,cr.Mobile
试试