有一段代码:是按月分组,求最大值:select convert(varchar(7),c.Curdate,120) as monthDate,c.MeterId,max(c.totel) from
(
select t1.Curdate as Curdate,t1.MeterId as MeterId,t1.Value as Value,t2.curdate as priordate,t2.value as priorvalue,(t1.value-t2.value) as totel from #temp t1,#temp t2
where t1.rownum - t2.rownum = 1 and t1.meterid = t2.meterid
) c
group by convert(varchar(7),c.Curdate,120),c.MeterId
order by convert(varchar(7),c.Curdate,120),c.MeterId但是一个月如果按上月25号到这个月26号算一个月,改如何修改这个sql?
(
select t1.Curdate as Curdate,t1.MeterId as MeterId,t1.Value as Value,t2.curdate as priordate,t2.value as priorvalue,(t1.value-t2.value) as totel from #temp t1,#temp t2
where t1.rownum - t2.rownum = 1 and t1.meterid = t2.meterid
) c
group by convert(varchar(7),c.Curdate,120),c.MeterId
order by convert(varchar(7),c.Curdate,120),c.MeterId但是一个月如果按上月25号到这个月26号算一个月,改如何修改这个sql?
SELECT CONVERT(VARCHAR(7),(CASE WHEN DAY(t1.Curdate)>=25 THEN DATEADD(MONTH,1,t1.Curdate) ELSE t1.Curdate END),120) AS monthDate,
t1.MeterId AS MeterId,
MAX(t1.value-t2.value) AS totel
FROM #temp t1, #temp t2
WHERE t1.rownum =t2.rownum +1
AND t1.meterid = t2.meterid
GROUP BY CONVERT(VARCHAR(7),(CASE WHEN DAY(t1.Curdate)>=25 THEN DATEADD(MONTH,1,t1.Curdate) ELSE t1.Curdate END),120), t1.MeterId
ORDER BY CONVERT(VARCHAR(7),(CASE WHEN DAY(t1.Curdate)>=25 THEN DATEADD(MONTH,1,t1.Curdate) ELSE t1.Curdate END),120), t1.MeterId;