July 22, 2016 at 2:15 am
USE AC
GO
Select t1.linkid,t1.Ddate as Date,t1.etype,t1.Refrence,t1.linkacc as ContraAcc,t1.Description,
sum(case when t1.amount > 0 then t1.amount else 0 end) as Debits,
sum(case when t1.amount < 0 then t1.amount else 0 end) as Credits,
(
SELECT SUM(amount)
FROM dbo.vw_LT T2
WHERE T2.LinkID <= T1.LinkID
)Cumulative
FROM dbo.vw_LT t1
WHERE t1.accnumber ='8400000'
AND t1.DDate BETWEEN '2016-04-01 00:00:00' AND '2016-04-30 00:00:00'
AND t1.DataSource = 'PAS11CEDCRE17'
group by t1.linkid,t1.Ddate,t1.etype,t1.Refrence,t1.linkacc,t1.Description,t1.Amount
order by t1.ddate
it is not adding the previous row value but putting some other random number
July 22, 2016 at 3:52 am
With no data or test script to work with, my guess would be that this part of the query
nashpats (7/22/2016)
(SELECT SUM(amount)
FROM dbo.vw_LT T2
WHERE T2.LinkID <= T1.LinkID
)Cumulative
Needs to be filtered in the same way as the main part of the query, so should read
(
SELECT SUM(amount)
FROM dbo.vw_LT T2
WHERE T2.LinkID <= T1.LinkID
AND t1.accnumber ='8400000'
AND t1.DDate BETWEEN '2016-04-01 00:00:00' AND '2016-04-30 00:00:00'
AND t1.DataSource = 'PAS11CEDCRE17'
)Cumulative
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply