Insert imaginary Rows To CTE Dynamics Construct (use IF EXISTS in dynamics CTE ?)

1

I have below construct dependent on incoming payments.

According to various situations need to adapt the final report (for
example, at a certain date establishes the first row is zero, the second
displays credit payments deducted from the required cash credit).

I need to construct this CTE adapted to be able to create IMAGINARY ROWS depending on various conditions specified in ERP ...

INSERT INTO @PLATBY
( ROWNUMBER ,
OD ,
DO ,
UHRADA
)
EXECUTE
( 'WITH PLATBY AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY FAK.DATUM),
FAK.DATUM,SUM(FAK.CASTKA) AS CASTKA
FROM (
SELECT FAPLATV.FPV_DATUM AS
DATUM,SUM(FAPLATV.FPV_CASTKA) AS CASTKA
FROM ' + @DATABASE + '..CG_OP7UC UC
INNER JOIN ' + @DATABASE
+ '..CG_OP7FV FVxCO ON FVxCO.CODE = UC.CODE
INNER JOIN ' + @DATABASE
+ '..CG_POZN POZNAMKA ON
POZNAMKA.ID=FVxCO.TYP AND POZNAMKA.ZKRATKA in
(''UB'',''UDB'',''UP'',''UDP'')
INNER JOIN ' + @DATABASE
+ '..FAKTURYV FAKTURYV ON FAKTURYV.CISLO_FV
= FVxCO.CISLO_FV
INNER JOIN ' + @DATABASE
+ '..FAPLATV FAPLATV ON FAPLATV.FPV_CISLO =
FAKTURYV.CISLO_FV
WHERE UC.CODE LIKE ''' + @CODE + '''
GROUP BY FAPLATV.FPV_DATUM
UNION ALL
--Pridani Faktur s DPH
SELECT FVxCO.DAT AS DATUM,-FAKTURYV.CASTKA AS CASTKA
FROM ' + @DATABASE + '..CG_OP7UC UC
INNER JOIN ' + @DATABASE
+ '..CG_OP7FV FVxCO ON FVxCO.CODE = UC.CODE
INNER JOIN ' + @DATABASE
+ '..CG_POZN POZNAMKA ON
POZNAMKA.ID=FVxCO.TYP AND POZNAMKA.ZKRATKA in (''UDB'',''UDP'')
INNER JOIN ' + @DATABASE
+ '..FAKTURYV FAKTURYV ON FAKTURYV.CISLO_FV
= FVxCO.CISLO_FV
WHERE UC.CODE LIKE ''' + @CODE + '''
SELECT UC.DATSAZ AS DATUM, UC.UVER AS CASTKA
FROM ' + @DATABASE + '..CG_OP7UC UC
WHERE UC.CODE LIKE ''' + @CODE
+ '''
)FAK
GROUP BY FAK.DATUM
)
SELECT PLATBY.rownum,prev.DATUM AS OD,PLATBY.DATUM
as DO,PLATBY.CASTKA AS UHRADA
FROM PLATBY
LEFT JOIN PLATBY prev ON prev.rownum = PLATBY.rownum

  • 1
    LEFT JOIN PLATBY nex ON nex.rownum = PLATBY.rownum +
    1
    '
    );
2

I found that the IF EXISTS
can't be incorporated to CTE (or the dynamic CTE). How should I
systemically treat if the client fulfills certain conditions, at the end
of that construct, where systemic incoming payments are counted, two
fictitious rows with different among calculation should be inserted
(depending on variants, the total approx. 6 variants => 6 different
forms of the final SELECT That's why I wanted to use the IF EXISTS CTE)

I know I will handle the variants of fictional rows through SET, set the
value of text variables @ QUERY1 = '' SET @ QUERY2 = '' SET @ QUERY3 =
'' SET @ QUERY4 = '' and I will compose them through IF EXIST

3

I found that the IF EXISTS
can't be incorporated to CTE (or the dynamic CTE). How should I
systemically treat if the client fulfills certain conditions, at the end
of that construct, where systemic incoming payments are counted, two
fictitious rows with different among calculation should be inserted
(depending on variants, the total approx. 6 variants => 6 different
forms of the final SELECT That's why I wanted to use the IF EXISTS CTE)