Getting long time (Need perform sp)

Question

Post reply

Getting long time (Need perform sp)

sampathsoft

SSC Eights!

Points: 855
More actions
August 29, 2012 at 10:47 pm

#260720

Sp is getting more time(more than 7m) to generate report :w00t: .Output has more than 21000 records.Are there any good way/best practice to do this query .
Thanks
:Whistling:
USE [RGAM_SITE]
GO
/****** Object: StoredProcedure [RD].[usp_VRRPT127GetReportData] Script Date: 08/30/2012 09:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [RD].[usp_VRRPT127GetReportData]
@BusinessUnit char(4) = '',
@User varchar(40) = '',
@poweruser char(1) = '',
@GeoGroup varchar(50) = '',
@GeoValue varchar(100) = '',
@ExecutiveClassification varchar(100) = '',
@ExecutiveClassificationCode varchar(200) = '',
@ExecutiveClassCount int = 0,
@ExecutiveCodeFrom varchar(10) = '',
@ExecutiveCodeTo varchar(10) = '',
@RetailerClassification varchar(100) = '',
@RetailerClassificationCode varchar(200) = '',
@RetailerClassCount int = 0,
@RetailerCodeFrom varchar(15) = '',
@RetailerCodeTo varchar(15) = '',
@AsAtDate datetime = null,
@RepType char(1) = ''
AS
BEGIN
DECLARE @index int
DECLARE @SubClassification varchar(100)
DECLARE @SubClassificationCode varchar(200)
--Create temporary table for Executive
Create Table #VRRPT127_Executive
(
ExecutiveCode varchar(10) NOT NULL,
ExecutiveName varchar(50) NOT NULL
)
DECLARE @SQL VARCHAR(MAX)
IF (@ExecutiveClassification = '')
BEGIN
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_Executive SELECT Executive.ExecutiveCode,ExecutiveName FROM RD.Executive Executive'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_Executive SELECT Executive.ExecutiveCode,ExecutiveName FROM XA.fn_Executive(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Executive'
END
SET@SQL = @SQL + ' WHERE Executive.BusinessUnit = ''' + @BusinessUnit + ''''
IF @ExecutiveCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Executive.ExecutiveCode >= ''' + @ExecutiveCodeFrom + ''''
END
IF @ExecutiveCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Executive.ExecutiveCode <= ''' + @ExecutiveCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
ELSE
BEGIN
SET @index = 1
SET @SubClassification = @ExecutiveClassification
SET @SubClassificationCode = @ExecutiveClassificationCode
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_Executive SELECT Executive.ExecutiveCode,Executive.ExecutiveName FROM RD.Executive Executive'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_Executive SELECT Executive.ExecutiveCode,Executive.ExecutiveName FROM XA.fn_Executive(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Executive'
END
WHILE (@Index <= @ExecutiveClassCount)
BEGIN
IF (CHARINDEX(',',@SubClassificationCode) > 0 )
BEGIN
SET@SQL = @SQL + ' CROSS APPLY (SELECT ExecutiveCode FROM RD.ExecutiveClassification
WHERE RD.ExecutiveClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.ExecutiveClassification.ExecutiveCode = Executive.ExecutiveCode
AND RD.ExecutiveClassification.MasterGroup = ''' + substring(@SubClassification,0,CHARINDEX(',',@SubClassification))+'''
AND RD.ExecutiveClassification.MasterGroupValue = ''' + substring(@SubClassificationCode,0,CHARINDEX(',',@SubClassificationCode))+'''
) AS EXE_TBL' + CAST(@Index as varchar) + ''
SET @index = @index + 1
SET @SubClassificationCode = SUBSTRING(@SubClassificationCode,CHARINDEX(',',@SubClassificationCode)+1,LEN(@SubClassificationCode)-CHARINDEX(',',@SubClassificationCode))
SET @SubClassification = SUBSTRING(@SubClassification,CHARINDEX(',',@SubClassification)+1,LEN(@SubClassification)-CHARINDEX(',',@SubClassification))
END
ELSE
BEGIN
SET @SQL = @SQL + ' CROSS APPLY (SELECT ExecutiveCode FROM RD.ExecutiveClassification
WHERE RD.ExecutiveClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.ExecutiveClassification.ExecutiveCode = Executive.ExecutiveCode
AND RD.ExecutiveClassification.MasterGroup = ''' + @SubClassification + '''
AND RD.ExecutiveClassification.MasterGroupValue = ''' + @SubClassificationCode + '''
) AS EXE_TBL' + CAST(@Index as varchar) + ' '
SET @index = @index + 1
END
END
SET@SQL = @SQL + ' WHERE Executive.BusinessUnit = ''' + @BusinessUnit + ''''
IF @ExecutiveCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Executive.ExecutiveCode >= ''' + @ExecutiveCodeFrom + ''''
END
IF @ExecutiveCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Executive.ExecutiveCode <= ''' + @ExecutiveCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
Create Table #VRRPT127_RETAILER
(
RetailerCode varchar(50) NOT NULL,
RetailerName varchar (50) NOT NULL,
)
--Create temporary table for Retailers
IF (@GeoGroup='' AND @RetailerClassification = '')
BEGIN
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM RD.Retailer Retailer'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM XA.fn_Retailer(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Retailer'
END
SET@SQL = @SQL + ' WHERE Retailer.BusinessUnit = ''' + @BusinessUnit + ''''
IF @RetailerCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode >= ''' + @RetailerCodeFrom + ''''
END
IF @RetailerCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode <= ''' + @RetailerCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
ELSE IF (@GeoGroup <>'' AND @RetailerClassification = '')
BEGIN
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM RD.Retailer Retailer'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM XA.fn_Retailer(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Retailer'
END
SET@SQL = @SQL + ' INNER JOIN (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RetailerClassification.MasterGroup='''+ @GeoGroup +'''
AND RetailerClassification.MasterGroupValue='''+ @GeoValue +''') AS GeoClassification
ON Retailer.RetailerCode = GeoClassification.RetailerCode'
SET@SQL = @SQL + ' WHERE Retailer.BusinessUnit = ''' + @BusinessUnit + ''''
IF @RetailerCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode >= ''' + @RetailerCodeFrom + ''''
END
IF @RetailerCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode <= ''' + @RetailerCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
ELSE IF (@GeoGroup ='' AND @RetailerClassification <> '')
BEGIN
SET @index = 1
SET @SubClassification = @RetailerClassification
SET @SubClassificationCode = @RetailerClassificationCode
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM RD.Retailer Retailer'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM XA.fn_Retailer(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Retailer'
END
WHILE (@Index <= @RetailerClassCount)
BEGIN
IF (CHARINDEX(',',@SubClassificationCode) > 0 )
BEGIN
SET@SQL = @SQL + ' CROSS APPLY (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RD.RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.RetailerClassification.RetailerCode = Retailer.RetailerCode
AND RD.RetailerClassification.MasterGroup = ''' + substring(@SubClassification,0,CHARINDEX(',',@SubClassification))+'''
AND RD.RetailerClassification.MasterGroupValue = ''' + substring(@SubClassificationCode,0,CHARINDEX(',',@SubClassificationCode))+'''
) AS RET_TBL' + CAST(@Index as varchar) + ''
SET @index = @index + 1
SET @SubClassificationCode = SUBSTRING(@SubClassificationCode,CHARINDEX(',',@SubClassificationCode)+1,LEN(@SubClassificationCode)-CHARINDEX(',',@SubClassificationCode))
SET @SubClassification = SUBSTRING(@SubClassification,CHARINDEX(',',@SubClassification)+1,LEN(@SubClassification)-CHARINDEX(',',@SubClassification))
END
ELSE
BEGIN
SET @SQL = @SQL + ' CROSS APPLY (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RD.RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.RetailerClassification.RetailerCode = Retailer.RetailerCode
AND RD.RetailerClassification.MasterGroup = ''' + @SubClassification + '''
AND RD.RetailerClassification.MasterGroupValue = ''' + @SubClassificationCode + '''
) AS RET_TBL' + CAST(@Index as varchar) + ' '
SET @index = @index + 1
END
END
SET@SQL = @SQL + ' WHERE Retailer.BusinessUnit = ''' + @BusinessUnit + ''''
IF @RetailerCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode >= ''' + @RetailerCodeFrom + ''''
END
IF @RetailerCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode <= ''' + @RetailerCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
ELSE IF (@GeoGroup <>'' AND @RetailerClassification <> '')
BEGIN
SET @index = 1
SET @SubClassification = @RetailerClassification
SET @SubClassificationCode = @RetailerClassificationCode
IF @poweruser = '1'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM RD.Retailer Retailer'
END
ELSE IF @poweruser = '0'
BEGIN
SET @SQL = 'INSERT INTO #VRRPT127_RETAILER SELECT Retailer.RetailerCode, Retailer.RetailerName FROM XA.fn_Retailer(''' + @BusinessUnit + ''',''' + @User + ''',''' + @poweruser + ''') Retailer'
END
SET@SQL = @SQL + ' INNER JOIN (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RetailerClassification.MasterGroup='''+ @GeoGroup +'''
AND RetailerClassification.MasterGroupValue='''+ @GeoValue +''') AS GeoClassification
ON Retailer.RetailerCode = GeoClassification.RetailerCode'
WHILE (@Index <= @RetailerClassCount)
BEGIN
IF (CHARINDEX(',',@SubClassificationCode) > 0 )
BEGIN
SET@SQL = @SQL + ' CROSS APPLY (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RD.RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.RetailerClassification.RetailerCode = Retailer.RetailerCode
AND RD.RetailerClassification.MasterGroup = ''' + substring(@SubClassification,0,CHARINDEX(',',@SubClassification))+'''
AND RD.RetailerClassification.MasterGroupValue = ''' + substring(@SubClassificationCode,0,CHARINDEX(',',@SubClassificationCode))+'''
) AS RET_TBL' + CAST(@Index as varchar) + ''
SET @index = @index + 1
SET @SubClassificationCode = SUBSTRING(@SubClassificationCode,CHARINDEX(',',@SubClassificationCode)+1,LEN(@SubClassificationCode)-CHARINDEX(',',@SubClassificationCode))
SET @SubClassification = SUBSTRING(@SubClassification,CHARINDEX(',',@SubClassification)+1,LEN(@SubClassification)-CHARINDEX(',',@SubClassification))
END
ELSE
BEGIN
SET @SQL = @SQL + ' CROSS APPLY (SELECT RetailerCode FROM RD.RetailerClassification
WHERE RD.RetailerClassification.BusinessUnit = ''' + @BusinessUnit + '''
AND RD.RetailerClassification.RetailerCode = Retailer.RetailerCode
AND RD.RetailerClassification.MasterGroup = ''' + @SubClassification + '''
AND RD.RetailerClassification.MasterGroupValue = ''' + @SubClassificationCode + '''
) AS RET_TBL' + CAST(@Index as varchar) + ' '
SET @index = @index + 1
END
END
SET@SQL = @SQL + ' WHERE Retailer.BusinessUnit = ''' + @BusinessUnit + ''''
IF @RetailerCodeFrom <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode >= ''' + @RetailerCodeFrom + ''''
END
IF @RetailerCodeTo <> ''
BEGIN
SET@SQL = @SQL + ' AND Retailer.RetailerCode <= ''' + @RetailerCodeTo + ''''
END
PRINT @SQL
EXEC (@SQL)
END
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
IF @RepType = '1' --Qty in Product Wise
BEGIN
SELECT VisitInfo.ExecutiveCode,Executive.ExecutiveName,
VisitInfo.RetailerCode ,
Retailer.RetailerName ,
ISNULL(InvoiceInfo.ProductCode, '') AS ProductCode ,
ISNULL(Product.Description2, '') AS ProductName ,
ISNULL(SUM(U1MovementQuantity), 0) AS Qty ,
VisitInfo.VisitNumber ,
'Last Four Visit Details' AS NewCOl,
VisitInfo.VisitDate,
'1' AS ColGroup
FROM ( SELECT A.BusinessUnit ,
A.RetailerCode ,
A.VisitNumber ,
A.ExecutiveCode,
A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
-- INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
-- AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <= @AsAtDate
) AS AA
ORDER BY RetailerCode ,VisitDate DESC,
VisitNumber DESC
) AS A
) AS VisitInfo
OUTER APPLY ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.ExecutiveCode ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceLine.ProductCode ,
SalesInvoiceLine.U1MovementQuantity AS U1MovementQuantity
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
INNER JOIN RD.SalesInvoiceLine ON SalesInvoiceHeader.RecID = SalesInvoiceLine.RecID
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.Status = '1'
AND SalesInvoiceHeader.BusinessUnit = VisitInfo.BusinessUnit
AND SalesInvoiceHeader.RetailerCode = VisitInfo.RetailerCode
AND SalesInvoiceHeader.VisitNumber = VisitInfo.VisitNumber
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) = VisitInfo.VisitDate
) AS InvoiceInfo
LEFT OUTER JOIN RD.Product ON VisitInfo.BusinessUnit = Product.BusinessUnit
AND InvoiceInfo.ProductCode = Product.ProductCode
INNER JOIN #VRRPT127_Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode
INNER JOIN #VRRPT127_RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode
WHERE VisitInfo.BusinessUnit = @BusinessUnit
GROUP BY VisitInfo.ExecutiveCode,Executive.ExecutiveName,
VisitInfo.RetailerCode ,
InvoiceInfo.ProductCode ,
VisitInfo.VisitNumber ,
Retailer.RetailerName ,
Product.Description2, VisitInfo.VisitDate
UNION ALL
SELECT ExecutiveCode,ExecutiveName,RetailerCode,RetailerName,ProductCode,ProductName,
SUM(Qty)/CountVisit AS Qty,'-1' AS VisitNumber ,'Average Quantity' AS NewCOl
,NULL AS VisitDate
,'2' AS ColGroup
FROM(SELECT VisitInfo.ExecutiveCode,Executive.ExecutiveName,
VisitInfo.RetailerCode ,
Retailer.RetailerName ,
ISNULL(InvoiceInfo.ProductCode, '') AS ProductCode ,
ISNULL(Product.Description2, '') AS ProductName ,
ISNULL(SUM(U1MovementQuantity), 0) Qty ,
VisitInfo.VisitNumber ,
VisitInfo.VisitDate
,VisitCount.CountVisit
FROM ( SELECT A.BusinessUnit ,
A.RetailerCode ,
A.VisitNumber ,
A.ExecutiveCode,
A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
-- AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <= @AsAtDate
) AS AA
ORDER BY RetailerCode ,VisitDate DESC,
VisitNumber DESC
) AS A
) AS VisitInfo
OUTER APPLY ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.ExecutiveCode ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceLine.ProductCode ,
SalesInvoiceLine.U1MovementQuantity AS U1MovementQuantity
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
INNER JOIN RD.SalesInvoiceLine ON SalesInvoiceHeader.RecID = SalesInvoiceLine.RecID
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.Status = '1'
AND SalesInvoiceHeader.BusinessUnit = VisitInfo.BusinessUnit
AND SalesInvoiceHeader.RetailerCode = VisitInfo.RetailerCode
AND SalesInvoiceHeader.VisitNumber = VisitInfo.VisitNumber
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) = VisitInfo.VisitDate
) AS InvoiceInfo
LEFT OUTER JOIN RD.Product ON VisitInfo.BusinessUnit = Product.BusinessUnit
AND InvoiceInfo.ProductCode = Product.ProductCode
INNER JOIN
(SELECT A.BusinessUnit ,
A.RetailerCode ,
count(A.VisitNumber) AS CountVisit ,
A.ExecutiveCode
--A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
-- AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <= @AsAtDate
) AS AA
ORDER BY RetailerCode ,VisitDate DESC,
VisitNumber DESC
) AS A
GROUP BY A.BusinessUnit ,
A.RetailerCode ,
A.ExecutiveCode) AS VisitCount
ON VisitInfo.BusinessUnit=VisitCount.BusinessUnit
AND VisitInfo.RetailerCode=VisitCount.RetailerCode
AND VisitInfo.ExecutiveCode=VisitCount.ExecutiveCode
INNER JOIN #VRRPT127_Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode
INNER JOIN #VRRPT127_RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode
WHERE VisitInfo.BusinessUnit = @BusinessUnit
GROUP BY VisitInfo.ExecutiveCode,Executive.ExecutiveName,
VisitInfo.RetailerCode ,
InvoiceInfo.ProductCode ,
VisitInfo.VisitNumber ,
Retailer.RetailerName ,
Product.Description2, VisitInfo.VisitDate
,VisitCount.CountVisit
) AS SummaryInfo
GROUP BY ExecutiveCode,ExecutiveName,RetailerCode,RetailerName,ProductCode,ProductName,CountVisit
ORDER BY VisitInfo.VisitNumber
END
ELSE IF @RepType = '2'
BEGIN
SELECT VisitInfo.ExecutiveCode ,
VisitInfo.RetailerCode ,
VisitInfo.VisitNumber ,
ISNULL(SUM(InvoiceValue), 0) AS InvoiceValue ,
ISNULL(SUM(CRNValue), 0) AS CRNValue ,
ISNULL(SUM(DiscountValue), 0) AS DiscountValue ,
VisitInfo.VisitDate,'1' AS GroupType
FROM ( SELECT A.BusinessUnit ,
A.RetailerCode ,
A.VisitNumber ,
A.ExecutiveCode ,
A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode ,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode ,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
--@User,
--@PowerUser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
--AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <= @AsAtDate
) AS AA
ORDER BY RetailerCode ,
VisitDate DESC,
VisitNumber DESC
) AS A
) AS VisitInfo
OUTER APPLY ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.ExecutiveCode ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.TotalGoodsValue AS InvoiceValue ,
ISNULL(CreditNoteHeader.GoodsValue, 0)
- ISNULL(LineDiscountTotal, 0)
- ISNULL(HeaderDiscountTotal, 0) AS CRNValue ,
TotalHeaderDiscountValue
+ TotalLineDiscountValue
+ TotalSpecialDiscountValue
+ TotalOtherDiscountValue AS DiscountValue
FROM (SELECT SalesInvoiceHeader.BusinessUnit,SalesInvoiceHeader.TerritoryCode
,SalesInvoiceHeader.SalesCategoryCode,ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS OrderDate,SalesInvoiceHeader.RetailerCode
,SalesInvoiceHeader.ExecutiveCode,SalesInvoiceHeader.VisitNumber
,SUM(SalesInvoiceHeader.TotalGoodsValue) AS TotalGoodsValue
,SUM(SalesInvoiceHeader.TotalHeaderDiscountValue) AS TotalHeaderDiscountValue
,SUM(SalesInvoiceHeader.TotalLineDiscountValue) AS TotalLineDiscountValue
,SUM(SalesInvoiceHeader.TotalSpecialDiscountValue) AS TotalSpecialDiscountValue
,SUM(SalesInvoiceHeader.TotalOtherDiscountValue) AS TotalOtherDiscountValue
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.Status = '1'
GROUP BY SalesInvoiceHeader.BusinessUnit,SalesInvoiceHeader.TerritoryCode,SalesInvoiceHeader.SalesCategoryCode
,ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate),SalesInvoiceHeader.RetailerCode,SalesInvoiceHeader.ExecutiveCode,SalesInvoiceHeader.VisitNumber
) AS SalesInvoiceHeader
LEFT OUTER JOIN ( SELECT BusinessUnit,TerritoryCode,SalesCategoryCode,CreditDate,RetailerCode
,SUM(CreditNoteHeader.GoodsValue) AS GoodsValue
,SUM(LineDiscountTotal) AS LineDiscountTotal
,SUM(HeaderDiscountTotal) AS HeaderDiscountTotal
FROM RD.CreditNoteHeader WITH(NOLOCK)
WHERE Status = '1'
GROUP BY BusinessUnit,TerritoryCode,SalesCategoryCode,CreditDate,RetailerCode
) AS CreditNoteHeader ON SalesInvoiceHeader.BusinessUnit = CreditNoteHeader.BusinessUnit
AND SalesInvoiceHeader.TerritoryCode = CreditNoteHeader.TerritoryCode
AND SalesInvoiceHeader.SalesCategoryCode = CreditNoteHeader.SalesCategoryCode
AND SalesInvoiceHeader.OrderDate = CreditNoteHeader.CreditDate
AND SalesInvoiceHeader.RetailerCode = CreditNoteHeader.RetailerCode
WHERE VisitInfo.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND VisitInfo.RetailerCode = SalesInvoiceHeader.RetailerCode
AND VisitInfo.VisitNumber = SalesInvoiceHeader.VisitNumber
AND VisitInfo.VisitDate = SalesInvoiceHeader.OrderDate
) AS TotalInfo
INNER JOIN #VRRPT127_Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode
INNER JOIN #VRRPT127_RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode
WHERE VisitInfo.BusinessUnit = @BusinessUnit
GROUP BY
VisitInfo.ExecutiveCode,
VisitInfo.RetailerCode ,
VisitInfo.VisitNumber ,
VisitInfo.VisitDate
UNION ALL
SELECT ExecutiveCode,RetailerCode,'-1' AS VisitNumber
,SUM(InvoiceValue)/CountVisit AS InvoiceValue
,SUM(CRNValue)/CountVisit AS CRNValue
,SUM(DiscountValue)/CountVisit AS DiscountValue
,NULL AS VisitDate,'2' AS GroupType
FROM(
SELECT VisitInfo.ExecutiveCode ,
VisitInfo.RetailerCode ,
VisitInfo.VisitNumber ,
ISNULL(SUM(InvoiceValue), 0) AS InvoiceValue ,
ISNULL(SUM(CRNValue), 0) AS CRNValue ,
ISNULL(SUM(DiscountValue), 0) AS DiscountValue ,
VisitInfo.VisitDate
,VisitCount.CountVisit
FROM ( SELECT A.BusinessUnit ,
A.RetailerCode ,
A.VisitNumber ,
A.ExecutiveCode ,
A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode ,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <=@AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode ,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
--@User,
--@PowerUser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
--AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <=@AsAtDate
) AS AA
ORDER BY RetailerCode ,
VisitDate DESC,
VisitNumber DESC
) AS A
) AS VisitInfo
OUTER APPLY ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.ExecutiveCode ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.TotalGoodsValue AS InvoiceValue ,
ISNULL(CreditNoteHeader.GoodsValue, 0)
- ISNULL(LineDiscountTotal, 0)
- ISNULL(HeaderDiscountTotal, 0) AS CRNValue ,
TotalHeaderDiscountValue
+ TotalLineDiscountValue
+ TotalSpecialDiscountValue
+ TotalOtherDiscountValue AS DiscountValue
FROM ( SELECT SalesInvoiceHeader.BusinessUnit,SalesInvoiceHeader.TerritoryCode,SalesInvoiceHeader.SalesCategoryCode
,ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) as OrderDate,SalesInvoiceHeader.RetailerCode
,SalesInvoiceHeader.ExecutiveCode,SalesInvoiceHeader.VisitNumber
,SUM(SalesInvoiceHeader.TotalGoodsValue) AS TotalGoodsValue
,SUM(SalesInvoiceHeader.TotalHeaderDiscountValue) AS TotalHeaderDiscountValue
,SUM(SalesInvoiceHeader.TotalLineDiscountValue) AS TotalLineDiscountValue
,SUM(SalesInvoiceHeader.TotalSpecialDiscountValue) AS TotalSpecialDiscountValue
,SUM(SalesInvoiceHeader.TotalOtherDiscountValue) AS TotalOtherDiscountValue
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
WHERE SalesInvoiceHeader.Status = '1'
GROUP BY SalesInvoiceHeader.BusinessUnit,SalesInvoiceHeader.TerritoryCode,SalesInvoiceHeader.SalesCategoryCode
,ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate)
,SalesInvoiceHeader.RetailerCode,SalesInvoiceHeader.ExecutiveCode,SalesInvoiceHeader.VisitNumber
) AS SalesInvoiceHeader
LEFT OUTER JOIN ( SELECT BusinessUnit,TerritoryCode,SalesCategoryCode,CreditDate,RetailerCode
,SUM(CreditNoteHeader.GoodsValue) AS GoodsValue
,SUM(LineDiscountTotal) AS LineDiscountTotal
,SUM(HeaderDiscountTotal) AS HeaderDiscountTotal
FROM RD.CreditNoteHeader WITH(NOLOCK)
WHERE Status = '1'
GROUP BY BusinessUnit,TerritoryCode,SalesCategoryCode,CreditDate,RetailerCode
) AS CreditNoteHeader ON SalesInvoiceHeader.BusinessUnit = CreditNoteHeader.BusinessUnit
AND SalesInvoiceHeader.TerritoryCode = CreditNoteHeader.TerritoryCode
AND SalesInvoiceHeader.SalesCategoryCode = CreditNoteHeader.SalesCategoryCode
AND SalesInvoiceHeader.OrderDate = CreditNoteHeader.CreditDate
AND SalesInvoiceHeader.RetailerCode = CreditNoteHeader.RetailerCode
WHERE VisitInfo.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND VisitInfo.RetailerCode = SalesInvoiceHeader.RetailerCode
AND VisitInfo.VisitNumber = SalesInvoiceHeader.VisitNumber
AND VisitInfo.VisitDate = SalesInvoiceHeader.OrderDate
) AS TotalInfo
INNER JOIN
(SELECT A.BusinessUnit ,
A.RetailerCode ,
count(A.VisitNumber) AS CountVisit ,
A.ExecutiveCode
--A.VisitDate
FROM RD.Retailer
OUTER APPLY ( SELECT TOP 4
*
FROM ( SELECT SalesInvoiceHeader.BusinessUnit ,
SalesInvoiceHeader.RetailerCode ,
SalesInvoiceHeader.VisitNumber ,
SalesInvoiceHeader.ExecutiveCode,
ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate
FROM RD.SalesInvoiceHeader WITH(NOLOCK)
LEFT OUTER JOIN RD.SalesOrderHeader
ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit
AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode
AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode
AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode
AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode
AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit
-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue
WHERE SalesInvoiceHeader.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = SalesInvoiceHeader.BusinessUnit
AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode
AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <=@AsAtDate
UNION
SELECT VisitDetail.BusinessUnit ,
RetailerCode ,
VisitDetail.VisitNumber ,
VisitDetail.ExecutiveCode,
VisitHeader.VisitDate
FROM RD.VisitDetail WITH(NOLOCK)
INNER JOIN RD.VisitHeader ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit
AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode
AND VisitDetail.VisitNumber = VisitHeader.VisitNumber
--INNER JOIN XA.fn_Territories(@BusinessUnit,
-- @User,
-- @poweruser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit
-- AND VisitDetail.TerritoryCode = Territory.MasterGroupValue
WHERE VisitDetail.BusinessUnit = @BusinessUnit
AND Retailer.BusinessUnit = VisitDetail.BusinessUnit
AND Retailer.RetailerCode = VisitDetail.RetailerCode
AND VisitDetail.Status <> '2'
AND VisitDate <=@AsAtDate
) AS AA
ORDER BY RetailerCode ,VisitDate DESC,
VisitNumber DESC
) AS A
GROUP BY A.BusinessUnit ,
A.RetailerCode ,
A.ExecutiveCode) AS VisitCount
ON VisitInfo.BusinessUnit=VisitCount.BusinessUnit
AND VisitInfo.RetailerCode=VisitCount.RetailerCode
AND VisitInfo.ExecutiveCode=VisitCount.ExecutiveCode
INNER JOIN RD.Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode
INNER JOIN RD.RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode
WHERE VisitInfo.BusinessUnit = @BusinessUnit
GROUP BY
VisitInfo.ExecutiveCode,
VisitInfo.RetailerCode ,
VisitInfo.VisitNumber ,
VisitInfo.VisitDate
,VisitCount.CountVisit
) AS SummaryInfo
GROUP BY ExecutiveCode,RetailerCode,CountVisit
END
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

demonfox SSCertifiable Points: 6289 More actions · Answer 1

sorry, couldn't go through the whole code ..it's too big for a quick look;

was this initially taking less time , and now you are facing the issue with sp or it's the first run ??

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

sampathsoft SSC Eights! Points: 855 More actions · Answer 2

sorry, couldn't go through the whole code ..it's too big for a quick look;

1)was this initially taking less time = If data is less , report generating . But with large data in tables it took more time ,

2) and now you are facing the issue with sp or it's the first run ?? = When i call to this sp from code behind it working . but getting time. If table data is less , then report generating( sp running quickly). But i don't no any other way to code without join table like this.

pls reply

thanks

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Can you attach the execution plan for the procedure, there might be something obivous in the plan.

As a side note, have all statistics and indexes been checked for being up to date and defragmented?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Pick a chunk of the query at the bottom of your stored procedure, run it in a query window with a couple of sensible parameters, and have a look at the execution plan. Since there are a few joins involving five or six different columns, and similarly complex WHERE clauses, it would seem likely that the existing indexing strategy isn't helping this query.

Not much else springs to mind except that the derived table SalesInvoiceHeader appears to be aggregated to the same level of granularity as the salesorderheader part of visitinfo. If you were to run the results of derived table SalesInvoiceHeader into a #temp table, then extracting the rows for the salesorderheader part of visitinfo from this #temp table would be a lot cheaper than reading the base tables. Performance (and readability=maintainability) can be significantly improved by breaking up a large query into smaller chunks using indexed #temp tables

Here's what looks to me to be a suitable chunk, reformatted for readability (please read up on table aliases and use them). It's from @RepType = '2', and it's the first query in the UNIONed query set.

-- @RepType = '2', first query in UNIONed query set

SELECT -- #1

VisitInfo.ExecutiveCode ,

VisitInfo.RetailerCode ,

VisitInfo.VisitNumber ,

ISNULL(SUM(InvoiceValue), 0) AS InvoiceValue ,

ISNULL(SUM(CRNValue), 0) AS CRNValue ,

ISNULL(SUM(DiscountValue), 0) AS DiscountValue ,

VisitInfo.VisitDate,'1' AS GroupType

FROM (

SELECT -- #2 -- Derived table visitinfo

A.BusinessUnit ,

A.RetailerCode ,

A.VisitNumber ,

A.ExecutiveCode ,

A.VisitDate

FROM RD.Retailer

OUTER APPLY (

SELECT TOP 4 * -- #3 Derived table A

FROM (

SELECT -- #4 Derived table AA

SalesInvoiceHeader.BusinessUnit ,

SalesInvoiceHeader.RetailerCode ,

SalesInvoiceHeader.VisitNumber ,

SalesInvoiceHeader.ExecutiveCode ,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS VisitDate

FROM RD.SalesInvoiceHeader WITH (NOLOCK)

--INNER JOIN XA.fn_Territories(@BusinessUnit,

-- @User,

-- @poweruser) AS Territory ON SalesInvoiceHeader.BusinessUnit = Territory.BusinessUnit

-- AND SalesInvoiceHeader.TerritoryCode = Territory.MasterGroupValue

LEFT OUTER JOIN RD.SalesOrderHeader

ON RD.SalesInvoiceHeader.BusinessUnit=RD.SalesOrderHeader.BusinessUnit

AND RD.SalesInvoiceHeader.TerritoryCode=RD.SalesOrderHeader.TerritoryCode

AND RD.SalesInvoiceHeader.ExecutiveCode=RD.SalesOrderHeader.ExecutiveCode

AND RD.SalesInvoiceHeader.SalesCategoryCode=RD.SalesOrderHeader.SalesCategoryCode

AND RD.SalesInvoiceHeader.RetailerCode=RD.SalesOrderHeader.RetailerCode

AND RD.SalesInvoiceHeader.VisitNumber=RD.SalesOrderHeader.VisitNumber

WHERE

SalesInvoiceHeader.BusinessUnit = @BusinessUnit -- ### parameter

AND Retailer.RetailerCode = SalesInvoiceHeader.RetailerCode

AND ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) <= @AsAtDate -- ### parameter

UNION

SELECT -- #4 Derived table AA

VisitDetail.BusinessUnit ,

RetailerCode,

VisitDetail.VisitNumber ,

VisitDetail.ExecutiveCode ,

VisitHeader.VisitDate

FROM RD.VisitDetail WITH(NOLOCK)

INNER JOIN RD.VisitHeader

ON VisitDetail.BusinessUnit = VisitHeader.BusinessUnit

AND VisitDetail.TerritoryCode = VisitHeader.TerritoryCode

AND VisitDetail.VisitNumber = VisitHeader.VisitNumber

--INNER JOIN XA.fn_Territories(@BusinessUnit,

--@User,

--@PowerUser) AS Territory ON VisitDetail.BusinessUnit = Territory.BusinessUnit

--AND VisitDetail.TerritoryCode = Territory.MasterGroupValue

WHERE

VisitDetail.BusinessUnit = @BusinessUnit -- ### parameter

AND Retailer.BusinessUnit = VisitDetail.BusinessUnit

AND Retailer.RetailerCode = VisitDetail.RetailerCode

AND VisitDetail.Status <> '2'

AND VisitDate <= @AsAtDate -- ### parameter

) AS AA

ORDER BY

RetailerCode,

VisitDate DESC,

VisitNumber DESC

) AS A

) AS VisitInfo

OUTER APPLY (

SELECT -- #2 Derived table TotalInfo

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.ExecutiveCode ,

SalesInvoiceHeader.RetailerCode ,

SalesInvoiceHeader.VisitNumber ,

SalesInvoiceHeader.TotalGoodsValue AS InvoiceValue ,

ISNULL(CreditNoteHeader.GoodsValue, 0)

- ISNULL(LineDiscountTotal, 0)

- ISNULL(HeaderDiscountTotal, 0) AS CRNValue ,

TotalHeaderDiscountValue

+ TotalLineDiscountValue

+ TotalSpecialDiscountValue

+ TotalOtherDiscountValue AS DiscountValue

FROM (

SELECT -- #3 Derived table SalesInvoiceHeader

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.TerritoryCode,

SalesInvoiceHeader.SalesCategoryCode,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate) AS OrderDate,

SalesInvoiceHeader.RetailerCode,

SalesInvoiceHeader.ExecutiveCode,

SalesInvoiceHeader.VisitNumber,

SUM(SalesInvoiceHeader.TotalGoodsValue) AS TotalGoodsValue,

SUM(SalesInvoiceHeader.TotalHeaderDiscountValue) AS TotalHeaderDiscountValue,

SUM(SalesInvoiceHeader.TotalLineDiscountValue) AS TotalLineDiscountValue,

SUM(SalesInvoiceHeader.TotalSpecialDiscountValue) AS TotalSpecialDiscountValue,

SUM(SalesInvoiceHeader.TotalOtherDiscountValue) AS TotalOtherDiscountValue

FROM RD.SalesInvoiceHeader WITH(NOLOCK)

LEFT OUTER JOIN RD.SalesOrderHeader soh

ON RD.SalesInvoiceHeader.BusinessUnit = RD.SalesOrderHeader.BusinessUnit

AND RD.SalesInvoiceHeader.TerritoryCode = RD.SalesOrderHeader.TerritoryCode

AND RD.SalesInvoiceHeader.ExecutiveCode = RD.SalesOrderHeader.ExecutiveCode

AND RD.SalesInvoiceHeader.SalesCategoryCode = RD.SalesOrderHeader.SalesCategoryCode

AND RD.SalesInvoiceHeader.RetailerCode = RD.SalesOrderHeader.RetailerCode

AND RD.SalesInvoiceHeader.VisitNumber = RD.SalesOrderHeader.VisitNumber

WHERE SalesInvoiceHeader.Status = '1' -- why is this filter missing from VisitInfo?

GROUP BY

SalesInvoiceHeader.BusinessUnit,

SalesInvoiceHeader.TerritoryCode,

SalesInvoiceHeader.SalesCategoryCode,

ISNULL(SalesOrderHeader.OrderDate,SalesInvoiceHeader.InvoiceDate),

SalesInvoiceHeader.RetailerCode,

SalesInvoiceHeader.ExecutiveCode,

SalesInvoiceHeader.VisitNumber

) AS SalesInvoiceHeader

LEFT OUTER JOIN (

SELECT -- #3 Derived table CreditNoteHeader

BusinessUnit,

TerritoryCode,

SalesCategoryCode,

CreditDate,

RetailerCode,

SUM(CreditNoteHeader.GoodsValue) AS GoodsValue,

SUM(LineDiscountTotal) AS LineDiscountTotal,

SUM(HeaderDiscountTotal) AS HeaderDiscountTotal

FROM RD.CreditNoteHeader WITH(NOLOCK)

WHERE Status = '1'

GROUP BY BusinessUnit, TerritoryCode, SalesCategoryCode, CreditDate, RetailerCode

) AS CreditNoteHeader

ON SalesInvoiceHeader.BusinessUnit = CreditNoteHeader.BusinessUnit

AND SalesInvoiceHeader.TerritoryCode = CreditNoteHeader.TerritoryCode

AND SalesInvoiceHeader.SalesCategoryCode = CreditNoteHeader.SalesCategoryCode

AND SalesInvoiceHeader.OrderDate = CreditNoteHeader.CreditDate

AND SalesInvoiceHeader.RetailerCode = CreditNoteHeader.RetailerCode

WHERE VisitInfo.BusinessUnit = SalesInvoiceHeader.BusinessUnit

AND VisitInfo.RetailerCode = SalesInvoiceHeader.RetailerCode

AND VisitInfo.VisitNumber = SalesInvoiceHeader.VisitNumber

AND VisitInfo.VisitDate = SalesInvoiceHeader.OrderDate

) AS TotalInfo

INNER JOIN #VRRPT127_Executive AS Executive ON VisitInfo.ExecutiveCode = Executive.ExecutiveCode

INNER JOIN #VRRPT127_RETAILER AS Retailer ON VisitInfo.RetailerCode = Retailer.RetailerCode

WHERE VisitInfo.BusinessUnit = @BusinessUnit

GROUP BY

VisitInfo.ExecutiveCode,

VisitInfo.RetailerCode ,

VisitInfo.VisitNumber ,

VisitInfo.VisitDate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

Including the White Space, you have 918 lines of code and none of it is documented.

Steps 1 and 2 would be to document the code and add print statements to each section of the code to identify the section and how long that section of code actually took to run. "Divide'n'Conquer" is one of the main keys here because as the old saying goes, "Correct identification of the problem is 90% of the solution".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)