Tema https://foros.consultoria-sap.com/t/40928

consulta1.sql

DECLARE @INI VARCHAR(10)=‘2019-08-01’, @FIN VARCHAR(10)=‘2019-08-31’,@CTA VARCHAR(30)=‘2150.0011.0001.0004’,@Subtotal NUMERIC(19,4),@Iva NUMERIC(19,4),@Total NUMERIC(19,4),@CDML NUMERIC(19,4),@Divi NUMERIC(19,4)
CREATE TABLE #General
(No int IDENTITY(1,1),Fecha DATE,TransId int,BaseRef int,
ContraAct varchar(50),LineMemo varchar(80),Segment_0 varchar(80),ML numeric(12,2),
CardCode VARCHAR(20),RFC varchar(20),Name VARCHAR(80),Divi numeric(19,2),CardCode2 VARCHAR(20),RFC2 varchar(20),Name2 VARCHAR(80))
INSERT INTO #General
SELECT T0.RefDate,T0.TransId,T0.BaseRef,T0.ContraAct,T0.LineMemo,T1.Segment_0,CAST((T0.Debit+(- T0.Credit)) AS NUMERIC(11,2)) AS ‘C/D (ML)’,
(SELECT TX.ShortName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ContraAct=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)CardCode,
(SELECT T2.LicTradNum
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)RFC,
(SELECT T2.CardName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)Name,
CAST(((T0.Debit+(- T0.Credit))/.16) AS NUMERIC(11,2)) AS Divi
,
---------------------------Clave proveedor------------------------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardCode2,

--------------------RFC-------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.LicTradNum
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.LicTradNum
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)RFC2
,

----------------Nombre Proveedor------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.CardName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.CardName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardName2


------------------------------Termino de seleccion e inicio de condicion------------------------------------------
FROM JDT1 T0
FULL OUTER JOIN OACT T1 ON T1.AcctCode=T0.Account
FULL OUTER JOIN OCRD T2 ON T0.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=T0.TransId
WHERE T0.RefDate between @INI and @FIN
AND T1.Segment_0=@CTA
----------Condicion de clave de proveedor no vacias-----------------------------
AND (SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)IS NOT NULL
ORDER BY T0.RefDate asc,T0.TransId

consulta2.sql

CREATE TABLE #General2
(No int IDENTITY(1,1),Fecha DATE,TransId int,BaseRef int,
ContraAct varchar(50),LineMemo varchar(80),Segment_0 varchar(80),ML numeric(12,2),
CardCode VARCHAR(20),RFC varchar(20),Name VARCHAR(80),Divi numeric(19,2),CardCode2 VARCHAR(20),RFC2 varchar(20),Name2 VARCHAR(80))
INSERT INTO #General2

SELECT T0.RefDate,T0.TransId,T0.BaseRef,T0.ContraAct,T0.LineMemo,T1.Segment_0,CAST((T0.Debit+(- T0.Credit)) AS NUMERIC(11,2)) AS ‘C/D (ML)’,
(SELECT TX.ShortName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ContraAct=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)CardCode,
(SELECT T2.LicTradNum
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)RFC,
(SELECT T2.CardName
FROM JDT1 TX
FULL OUTER JOIN OACT T1 ON T1.AcctCode=TX.Account
FULL OUTER JOIN OCRD T2 ON TX.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=TX.TransId
WHERE TX.RefDate between @INI and @FIN AND TX.TransId=T0.TransId AND TX.ShortName LIKE ‘[PC]%’
)Name,
CAST(((T0.Debit+(- T0.Credit))/.16) AS NUMERIC(11,2)) AS Divi
,
---------------------------Clave proveedor------------------------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardCode2,
--------------------RFC-------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.LicTradNum
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.LicTradNum
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)RFC2
,
----------------Nombre Proveedor------------------------
(SELECT TOP 1
COALESCE((
SELECT DISTINCT TT.CardName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON TX.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT TT.CardName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
INNER JOIN OCRD TT ON T0.ShortName=TT.CardCode
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)CardName2
------------------------------Termino de seleccion e inicio de condicion------------------------------------------
FROM JDT1 T0
FULL OUTER JOIN OACT T1 ON T1.AcctCode=T0.Account
FULL OUTER JOIN OCRD T2 ON T0.ShortName=T2.CardCode
INNER JOIN OJDT T3 ON T3.TransId=T0.TransId
WHERE T0.RefDate between @INI and @FIN
AND T1.Segment_0=@CTA
----------Condicion de clace de proveedor vacias-----------------------------
AND (SELECT TOP 1
COALESCE((
SELECT DISTINCT TX.ShortName
–,TX.TransId
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND TX.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
),(
SELECT DISTINCT T0.ShortName
FROM ITR1 T0
INNER JOIN OITR TB ON T0.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX ON T0.ReconNum=TX.BaseRef
WHERE TX.TransId=TX1.TransId AND T0.ShortName LIKE ‘[P]%’ AND TX.RefDate BETWEEN @INI and @FIN
))
FROM ITR1 T0X
INNER JOIN OITR TB ON T0X.ReconNum=TB.ReconNum
INNER JOIN JDT1 TX1 ON T0X.ReconNum=TX1.BaseRef
WHERE TX1.TransId=T0.TransId)IS NULL
ORDER BY T0.RefDate asc,T0.TransId
-------------Tabla de union de las dos consultas-----------------------------------------------------------------------------------------------
CREATE TABLE #General3 (No int IDENTITY(1,1),RFC varchar(50),Clave VARCHAR(20),Nombre varchar(80),CDML numeric(12,2),
Divi numeric(12,2),Subtotal numeric(12,4),Iva numeric(12,4),Total numeric(12,4))
INSERT INTO #General3

SELECT DISTINCT RFC2,CardCode2,Name2,NULL ‘C/D (ML)’,NULL Divi,
CAST(SUM(ML/.16)OVER (PARTITION BY RFC2) AS NUMERIC(12,4))Subtotal,
CAST((SUM(ML/.16) OVER (PARTITION BY RFC2).16)AS NUMERIC(12,4))Iva,
CAST((SUM(ML/.16)OVER (PARTITION BY RFC2)+(SUM(ML/.16) OVER (PARTITION BY RFC2).16))AS NUMERIC(12,4))Total
FROM #General
UNION
SELECT RFC2,CardCode2,LineMemo,ML,Divi,NULL,NULL,NULL FROM #General2
ORDER BY RFC2 ASC

----------Para totales-----------
SET @Subtotal=(SELECT SUM(Subtotal) FROM #General3)
SET @Iva=(SELECT SUM(Iva) FROM #General3)
SET @Total=(SELECT SUM(Total) FROM #General3)
SET @CDML=(SELECT SUM(ML) FROM #General2)
SET @Divi=(SELECT SUM(Divi) FROM #General2)

-----------Muestra consulta----------------
SELECT * FROM #General3
UNION
SELECT 0,‘Total’,’////////’,’//////////////////////////////////’,@CDML,@Divi,@Subtotal,@Iva,@Total

DROP TABLE #General
DROP TABLE #General2
DROP TABLE #General3