User1711366110 posted
I want to take running Total(cumulative Total) column where i dont have any orderid(Key value) in my query this is my query
As per this case, you can get the cumulative Total with the help of using Common Table expression (CTE) with JOIN like below :
GO
with CTE as
(
select sms.LadleNo,sms.DateStamp,sms.heatNo[HeatNo],
(case sms.plantNo when 9 then 1 when 10 then 2 when 11 then 3 when 12 then 3 else 0 End) [LF],
bof.TAPSTART,bof.TAPEND,
SMS.actStrt[LFIn],SMS.actEnnd [LFOut],
DATEDIFF(MINUTE,BOF.TAPEND,SMS.actEnnd) [ProcessTime],
ROW_NUMBER() over(order by (select 1)) [LadleLife]
from smsHeatTracker[sms]
INNER JOIN BOF_HEAT_MIS_DATA[bof]
ON sms.heatNo=bof.HEAT_ID_CUST and sms.plantNo in (9,10,11,12)
where Convert(char(10),SMS.actEnnd,126)='2015-06-30'
)
select a.LadleNo, a.DateStamp, a.HeatNo,
a.LF, a.TAPSTART, a.TAPEND, a.LFIn, a.LFOut,a.ProcessTime,
sum(b. LFIn)-sum(b. LFOut)[CumulativeTotal]
from CTE[a] CROSS JOIN CTE [b]
where b.LadleLife <= a. LadleLife
group by a.LadleNo, a.DateStamp, a.HeatNo,
a.LF, a.TAPSTART, a.TAPEND, a.LFIn, a.LFOut,a.ProcessTime
order by a.LadleNo desc
GO
Note : In the above query, you can put the corresponding column in cumulative total & use corresponding join based on your business logics.
