Getting long time (Need perform sp)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Getting long time (Need perform sp)

Getting long time (Need perform sp)

Rate Topic

Display Mode

Topic Options

Author

Message

sampathsoft

Posted Wednesday, August 29, 2012 10:47 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, August 30, 2012 1:40 AM
Points: 47, Visits: 150

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

Post #1352000

demonfox

Posted Wednesday, August 29, 2012 11:44 PM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 6:46 PM
Points: 1,074, Visits: 1,076

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

Post #1352011

sampathsoft

Posted Thursday, August 30, 2012 12:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, August 30, 2012 1:40 AM
Points: 47, Visits: 150

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

Post #1352019

anthony.green

Posted Thursday, August 30, 2012 1:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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?

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1352040

ChrisM@Work

Posted Thursday, August 30, 2012 3:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 5,618, Visits: 10,990

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1352075

Jeff Moden

Posted Thursday, August 30, 2012 5:26 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1352534

« Prev Topic | Next Topic »

Permissions