Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Agenteusa
Starting Member
11 Posts |
 Posted - 2015-01-28 : 07:14:42
|
Hi,
I have 4 cursors that are inserting into a tabel and after an update command that will update 12 columns on the same table. Here is the code:
DELETE FROM a_UserTable_CuboVendasVsObjectivosMercados
--CURSOR MARKET/INDUSTRIA
DECLARE @market nvarchar(max)
DECLARE @industria nvarchar(max)
DECLARE cursorIndustriasMarkets CURSOR FOR
SELECT esri_marketidname, esri_name
FROM FilteredESRI_industria
where statecode = 0
--CURSOR CONSULTORES
DECLARE @consultorid uniqueidentifier
DECLARE @consultor nvarchar(max)
DECLARE @un nvarchar(max)
DECLARE cursorConsultor CURSOR FOR
SELECT distinct systemuserid, fullname, businessunitidname
FROM FilteredSystemUser
WHERE systemuserid IN (SELECT distinct esri_consultorid FROM a_UserTable_CuboObjectivos_UnidadeNegocio where Ano = YEAR(GETDATE()))
--CURSOR PAISES
DECLARE @pais nvarchar(max)
DECLARE cursorPaises CURSOR FOR
SELECT DISTINCT
case ESRI_name
when 'Portugal' then 'Nacional'
when 'Angola' then ESRI_name
when 'Cabo Verde' then ESRI_name
when 'Moçambique' then ESRI_name
when 'São Tomé e Príncipe' then ESRI_name
when 'Guiné' then ESRI_name
else 'Outros'
end name
FROM ESRI_pais
OPEN cursorIndustriasMarkets
FETCH NEXT FROM cursorIndustriasMarkets INTO @market, @industria
WHILE @@FETCH_STATUS = 0
BEGIN
OPEN cursorPaises
FETCH NEXT FROM cursorPaises INTO @pais
WHILE @@FETCH_STATUS = 0
BEGIN
OPEN cursorConsultor
FETCH NEXT FROM cursorConsultor INTO @consultorid, @consultor, @un
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO a_UserTable_CuboVendasVsObjectivosMercados (CONSULTORID, CONSULTOR, UNIDADENEGOCIO, MARKET, INDUSTRIA,PAIS)
VALUES(@consultorid, @consultor, @un, @market, @industria, @pais)
UPDATE a_UserTable_CuboVendasVsObjectivosMercados
SET REALIZADOMES = uf.realizadomes,
REALIZADOTRIMESTRE = uf.realizadotrimestre,
REALIZADOYEARTOMONTH = uf.realizadoytd,
REALIZADOANUAL = uf.realizadoano,
BACKLOGMES = uf.backmes,
BACKLOGTRIMESTRE = uf.backtrimestre,
BACKLOGYEARTOMONTH = uf.backytd,
BACKLOGANUAL = uf.backano,
PIPELINE75MES = uf.pipemes,
PIPELINE75TRIMESTRE = uf.pipetrimestre,
PIPELINE75YEARTOMONTH = uf.pipeytd,
PIPELINE75ANUAL = uf.pipeano
FROM [a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados] (@consultorid, @market, @industria, @pais) uf
WHERE CONSULTORID = @consultorid AND UNIDADENEGOCIO = @un AND MARKET = @market
AND INDUSTRIA = @industria AND PAIS = @pais
FETCH NEXT FROM cursorConsultor INTO @consultorid, @consultor, @un
END
CLOSE cursorConsultor
FETCH NEXT FROM cursorPaises INTO @pais
END
CLOSE cursorPaises
FETCH NEXT FROM cursorIndustriasMarkets INTO @market, @industria
END
CLOSE cursorIndustriasMarkets
DEALLOCATE cursorIndustriasMarkets
DEALLOCATE cursorPaises
DEALLOCATE cursorConsultor
That is the SP that will generate a table.
Now the code for the th UDF called inside the cursor is :
ALTER FUNCTION [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados]
(
@consultorid uniqueidentifier,
@market nvarchar(max),
@industria nvarchar(max),
@pais nvarchar(max)
)
RETURNS @ValuesTable TABLE (realizadomes DECIMAL(12,2), realizadotrimestre DECIMAL(12,2), realizadoytd DECIMAL(12,2), realizadoano DECIMAL(12,2),
backmes DECIMAL(12,2), backtrimestre DECIMAL(12,2), backytd DECIMAL(12,2), backano DECIMAL(12,2),
--objmes DECIMAL(12,2), objtrimestre DECIMAL(12,2), objytd DECIMAL(12,2), objano DECIMAL(12,2),
pipemes DECIMAL(12,2), pipetrimestre DECIMAL(12,2), pipeytd DECIMAL(12,2), pipeano DECIMAL(12,2))
AS
BEGIN
IF @pais = 'Nacional'
BEGIN
SET @pais = 'Portugal'
END
--- REALIZADOMES
DECLARE @realizadomes DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadomes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadomes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- REALIZADOTRIMESTRE
DECLARE @realizadotrimestre DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadotrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadotrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- REALIZADOYTD
DECLARE @realizadoytd DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadoytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadoytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- REALIZADOANO
DECLARE @realizadoano DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadoano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadoano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- BACKLOGMES
DECLARE @backlogmes DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @backlogmes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @backlogmes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- BACKLOGTRIMESTRE
DECLARE @backlogtrimestre DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @backlogtrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @backlogtrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- BACKLOGYTD
DECLARE @backlogytd DECIMAL(12,2)
SET @backlogytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
----------------
--- BACKLOGANO
DECLARE @backlogano DECIMAL(12,2)
SET @backlogano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
----------------
--- OBJETIVOMES
--DECLARE @objetivomes DECIMAL(12,2)
--SET @objetivomes = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE (Mes = MONTH(GETDATE()) AND Ano = YEAR(GETDATE())) AND esri_consultorid = @consultorid)
------------------
----- OBJETIVOTRIMESTRE
--DECLARE @objetivotrimestre DECIMAL(12,2)
--SET @objetivotrimestre = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE DATEPART(QUARTER, DataObjectivo) = DATEPART(QUARTER, GETDATE()) AND YEAR(DataObjectivo) = YEAR(GETDATE())
-- AND esri_consultorid = @consultorid)
------------------
----- OBJETIVOYTD
--DECLARE @objetivoytd DECIMAL(12,2)
--SET @objetivoytd = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE (DataObjectivo >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND DataObjectivo <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
-- AND esri_consultorid = @consultorid)
------------------
----- OBJETIVOANO
--DECLARE @objetivoano DECIMAL(12,2)
--SET @objetivoano = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE Ano = YEAR(GETDATE())
-- AND esri_consultorid = @consultorid)
----------------
--- PIPELINE75MES
DECLARE @pipeline75mes DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75mes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75mes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- PIPELINE75TRIMESTRE
DECLARE @pipeline75trimestre DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75trimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75trimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- PIPELINE75YTD
DECLARE @pipeline75ytd DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75ytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75ytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
--- PIPELINE75ANO
DECLARE @pipeline75ano DECIMAL(12,2)
IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75ano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75ano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------
INSERT INTO @ValuesTable(realizadomes, realizadotrimestre, realizadoytd, realizadoano, backmes, backtrimestre, backytd, backano,
--objmes, objtrimestre, objytd, objano,
pipemes, pipetrimestre, pipeytd, pipeano)
values (@realizadomes, @realizadotrimestre, @realizadoytd, @realizadoano,
@backlogmes, @backlogtrimestre, @backlogytd, @backlogano,
--@objetivomes, @objetivotrimestre, @objetivoytd, @objetivoano,
@pipeline75mes, @pipeline75trimestre, @pipeline75ytd, @pipeline75ano)
RETURN
END
So obviously this will give me very poor performance.
Can someone help me and try to turn this or give me some hints on how to change this into a set based approach or at least make it run faster. It takes 15 mins to run.
Thanks |
|
|
viggneshwar
Yak Posting Veteran
86 Posts |
Posted - 2015-01-28 : 09:31:02
|
It seems same query in running more than once in IF and Else condition. Change the code to reduce it. If possible insert into #table and reuse it multiple times in the below codes.
Regards
Viggneshwar A |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-01-28 : 09:40:11
|
Cursors are about the worst way to code anything. They should only be used as a last resort when all other avenues have been explored. They almost always perform very badly
FWIW the only reason I can see in your code for using a cursor is that in the inner loop you have an INSERT followed by and UPDATE. Why not put a trigger on the table that is the object of the INSERT and in the trigger do the update logic? If you can do that, you should be able to replace the cursors with set-based logic.
another option for your use case is composable DML. Using the SQL OUTPUT clause, you can do INSERT the values based on the UPDATE, Here's an example:
declare @a table (a int)
declare @b table (b int)
insert into @a values (1),(2),(3)
insert into @b
select * from (
update @a
set a += 1
output inserted.*
) q
Logically, the UPDATE happens first, but the whole operation is atomic, so if the INSERT fails, the UPDATE would be rolled back anyway |
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-01-28 : 13:34:40
|
You should be able to get rid of the cursors by JOINing FilteredESRI_industria, FilteredSystemUser, ESRI_pais and CROSS APPLYing to a inline version of your function.
The function should look something like:
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados]
(
@consultorid uniqueidentifier,
@market nvarchar(max),
@industria nvarchar(max),
@pais nvarchar(max)
)
RETURNS table
AS
RETURN
(
SELECT
SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadomes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadotrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadoytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadoano
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backmes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backtrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backano
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipemes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipetrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipeytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipeano
FROM FilteredInvoice I
OUTER APPLY
(
SELECT TOP (1) O.opportunityid
FROM FilteredOpportunity O
WHERE O.opportunityid = I.opportunityid
AND O.opportunityratingcodename IN ('75', '90', '100')
) D
WHERE esri_dataprevistadefacturacao >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND ownerid = @consultorid
AND statecode <> 3
AND
(
salesorderid IS NOT NULL
OR
(
salesorderid IS NULL
AND opportunityid IS NULL
)
)
AND EXISTS
(
SELECT 1
FROM FilteredAccount A
WHERE A.accountid = I.accountid
AND esri_marketidname = @market
AND esri_industriaidname = @industria
AND
(
@pais <> 'Outros'
AND esri_paisidname = CASE WHEN @pais = 'Nacional' THEN 'Portugal' ELSE @pais END
OR NOT esri_paisidname IN ('Angola', 'Cabo Verde', 'Moçambique', 'São Tomé e Príncipe', 'Guiné')
)
)
);
GO
|
|
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-01-28 : 17:06:20
|
Your INSERT should look something like:
WITH IndustriasMarkets
AS
(
SELECT esri_marketidname AS market
,esri_name AS industria
FROM FilteredESRI_industria
WHERE statecode = 0
)
,Consultor
AS
(
SELECT DISTINCT systemuserid AS consultorid
,fullname AS consultor
,businessunitidname AS un
FROM FilteredSystemUser S
WHERE EXISTS
(
SELECT 1
FROM esri_consultorid C
WHERE C.esri_consultorid = S.systemuserid
AND C.Ano = YEAR(CURRENT_TIMESTAMP)
)
)
,Paises
AS
(
SELECT DISTINCT
case ESRI_name
when 'Portugal' then 'Nacional'
when 'Angola' then ESRI_name
when 'Cabo Verde' then ESRI_name
when 'Moçambique' then ESRI_name
when 'São Tomé e Príncipe' then ESRI_name
when 'Guiné' then ESRI_name
else 'Outros'
end AS pais
FROM ESRI_pais
)
INSERT INTO a_UserTable_CuboVendasVsObjectivosMercados
(
CONSULTORID, CONSULTOR, UNIDADENEGOCIO, MARKET, INDUSTRIA, PAIS
,REALIZADOMES, REALIZADOTRIMESTRE, REALIZADOYEARTOMONTH, REALIZADOANUAL
,BACKLOGMES, BACKLOGTRIMESTRE, BACKLOGYEARTOMONTH, BACKLOGANUAL
,PIPELINE75MES, PIPELINE75TRIMESTRE, PIPELINE75YEARTOMONTH, PIPELINE75ANUAL
)
SELECT C.consultorid, C.consultor, C.un, I.market, I.industria, P.pais
,F.realizadomes, F.realizadotrimestre, F.realizadoytd, F.realizadoano
,F.backmes, F.backtrimestre, F.backytd, F.backano
,F.pipemes, F.pipetrimestre, F.pipeytd, F.pipeano
FROM IndustriasMarkets I, Consultor C, Paises P
OUTER APPLY [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados] (C.consultorid, I.market, I.industria, P.pais) F;
|
|
|
|
|
|
|
|
|
Help with nested cursors and bad performance