Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting long time (Need perform sp) Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 10:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 .Output has more than 21000 records.Are there any good way/best practice to do this query .
Thanks


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
Posted Wednesday, August 29, 2012 11:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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
Post #1352011
Posted Thursday, August 30, 2012 12:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, August 30, 2012 1:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 5,142, Visits: 4,932
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
Posted Thursday, August 30, 2012 3:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Posted Thursday, August 30, 2012 5:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1352534
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse