September 18, 2017 at 5:00 am
Hello comunity
I will some help to modify my CTE for working on SQL Server 2008, this is my CTE:
DECLARE @SaldoFinalBanco AS NUMERIC(15,2)
DECLARE @data AS date
DECLARE @Nrconta as INT
SET @SaldoFinalBanco = 1000.00
SET @data = '20170130'
SET @Nrconta = 7
--GESTAO
;WITH CTE_ABT_GEST AS (
SELECT
'A' [Ordem],''[Data], Space(30)+' »»»»»»»»»»'[Documento],'Saldo Final Periodo - Gestão'[Descricao],''[Cheque],
ROUND(SUM(Eentrada) - SUM(ESaida), 2) [Saldo_Acumulado] ,
0 [Debito],
0 [Credito],
0 [IDBanco], '' [Banco],@SaldoFinalBanco [Saldo Bancario]
FROM ba WHERE
ba.contado = @Nrconta
AND ba.data <= @data
UNION
SELECT
'A' [Ordem],DATA [Data], documento [Documento],descricao [Descricao],cheque,
0,
eentrada [Debito],
esaida [Credito],
contado,
(SELECT Cast(banco +replicate(' ',10-len(banco)) + conta+REPLICATE(' ',20-LEN(conta)) as varchar)
FROM bl WHERE bl.noconta = ba.contado) [Banco],
@SaldoFinalBanco
FROM ba
WHERE reco = 0
AND ba.contado = @Nrconta
AND ba.data <= @data
UNION
SELECT
'C' [Ordem], data, '',descricao, cheque,
0,
SUM(CASE WHEN evalor < 0 THEN abs(evalor) ELSE 0 END)[Debito] ,
SUM(CASE WHEN evalor > 0 THEN evalor ELSE 0 END)[Credito],
contado,
(SELECT Cast(banco +replicate(' ',10-len(banco)) + conta+REPLICATE(' ',20-LEN(conta)) as varchar)
FROM bl WHERE bl.noconta = br.contado) [Banco],
@SaldoFinalBanco
FROM br
WHERE reco = 0 AND contado = @Nrconta
AND br.data <= @data
Group by br.contado,br.data,br.descricao,br.cheque,br.evalor
)
SELECT *, sum(Saldo_acumulado - Debito + ABS(Credito))
OVER (ORDER BY Ordem ROWS UNBOUNDED PRECEDING) AS CumulativeSumByRowsGESTAO
,sum(Debito + ABS(Credito)) OVER ( ORDER BY Ordem ROWS UNBOUNDED PRECEDING) AS CumulativeDebito-Credito
FROM CTE_ABT_GEST
How can i replace [ ROWS UNBOUNDED PRECEDING ].
Many Thanks,
Luis
September 18, 2017 at 5:10 am
You need to likely use a CROSS APPLY or something similar. With no sample or expected data I've no idea what your data looks like, but here's an example of a running total, which might get your on the right track:USE Sandbox;
GO
CREATE TABLE SampleTable (SaleID int IDENTITY(1,1), SaleDate date, SaleValue decimal(12,2));
GO
INSERT INTO SampleTable (SaleDate, SaleValue)
VALUES
('20170901',2104),
('20170901',165),
('20170902',87),
('20170904',6547),
('20170904',64),
('20170905',676),
('20170906',987),
('20170909',374),
('20170909',9879),
('20170913',149),
('20170914',4196);
GO
SELECT *
FROM SampleTable;
GO
SELECT ST.*, rt.RunningTotal
FROM SampleTable ST
CROSS APPLY (SELECT SUM(SaleValue) RunningTotal
FROM SampleTable ca
WHERE ca.SaleID <= ST.SaleID) rt;
GO
DROP TABLE SampleTable;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 18, 2017 at 1:08 pm
In order for the CROSS APPLY to replicate ROWS UNBOUNDED PRECEDING, you'll need a triangular join. You're better off using a quirky update in SQL 2008.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2017 at 1:53 pm
drew.allen - Monday, September 18, 2017 1:08 PMIn order for the CROSS APPLY to replicate ROWS UNBOUNDED PRECEDING, you'll need a triangular join. You're better off using a quirky update in SQL 2008.Drew
Hello Drew,
Can you give me anexo example bases on my CTE?
Best regards
September 18, 2017 at 1:59 pm
luissantos - Monday, September 18, 2017 1:53 PMdrew.allen - Monday, September 18, 2017 1:08 PMIn order for the CROSS APPLY to replicate ROWS UNBOUNDED PRECEDING, you'll need a triangular join. You're better off using a quirky update in SQL 2008.Drew
Hello Drew,
Can you give me anexo example bases on my CTE?
Best regards
I no longer have access to a SQL 2008 system.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2017 at 2:44 pm
luissantos - Monday, September 18, 2017 1:53 PMdrew.allen - Monday, September 18, 2017 1:08 PMIn order for the CROSS APPLY to replicate ROWS UNBOUNDED PRECEDING, you'll need a triangular join. You're better off using a quirky update in SQL 2008.Drew
Hello Drew,
Can you give me anexo example bases on my CTE?
Best regards
An example might not be enough, you need to understand the process and follow certain rules to ensure the results are correct. Here's the article that describes it completely: Solving the Running Total and Ordinal Rank Problems (Rewritten) - SQLServerCentral
September 18, 2017 at 4:10 pm
drew.allen - Monday, September 18, 2017 1:08 PMIn order for the CROSS APPLY to replicate ROWS UNBOUNDED PRECEDING, you'll need a triangular join. You're better off using a quirky update in SQL 2008.Drew
The problem I have with the Quriky update, is that it is "Quirky". I don't know what the experience is the the OP, but it's not a simple implementation for those who don't understand SQL Server, or are new to it.
With 2008 phasing out now (it has, what 2 years at most left in the cycle), I'm kind of cautious about it now. Thosestill using 2008 are probably a way behind the times because they have no reason to update, which means they probably wouldn't use the features of 2012+; or understand what they are (no offence OP).
Although, I will admit, I was at one of the biggest UK insurers last week, and they were still using SSRS 2008, integrated with SharePoint 2016. I think my eyes were bleeding... It looked awful (I forgot how bad that GUI was) O.o
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply