USE MyDataBase
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET FMTONLY OFF
GO
SET NOCOUNT ON
GO
PRINT 'Date: ' + CONVERT(VARCHAR(24),GETDATE(),13) + ' Server Name: ' + @@servername + ' Service Name: ' + @@servicename;
GO
IF EXISTS
(SELECT *
FROM sys.objects
WHERE name = 'Last_Period_Revenue_MA')
DROP PROC dbo.Last_Period_Revenue_MA;
PRINT 'DROP PROC dbo.Last_Period_Revenue_MA';
GO
PRINT 'CREATE PROC dbo.Last_Period_Revenue_MA';
GO
CREATE PROC dbo.Last_Period_Revenue_MA
/************************************************************************************************************************************
Description:
Change Log
# Date Who Change
*************************************************************************************************************************************/
@CustomerCode NVARCHAR(255)
,@Period INT = -12
AS
BEGIN
BEGIN TRY
DECLARE @StrMDX AS NVARCHAR(MAX)
,@BOMDate AS SMALLDATETIME
,@DateFr AS SMALLDATETIME
,@DateTo AS SMALLDATETIME
,@StrSQL AS NVARCHAR(MAX)
,@Amount FLOAT;
SET @BOMDate = DATEADD(d,-DAY(GETDATE())+1, GETDATE());
SET @DateFr = DATEADD(m, @Period, @BOMDate);
SET @DateTo = DATEADD (d, -1, @BOMDate);
CREATE TABLE #MSCRM_Roll_DataSet1 (Amount FLOAT NULL);
CREATE TABLE #MSCRM_Roll_DataSet2 ( CustomerType NVARCHAR(255) NULL
,CustomerName VARCHAR(255) NULL
,CustomerSite VARCHAR(255) NULL
,FinancialYearDaily NVARCHAR(255) NULL
,FinancialPeriodDaily NVARCHAR(255) NULL
,CalendarDateDaily NVARCHAR(255) NULL
,ExclAmount FLOAT NULL);
CREATE TABLE #MSCRM_Roll_DataSet3 ( MthName NVARCHAR(20) NULL
,YrMth NVARCHAR(10) NULL
,ExclAmount FLOAT NULL);
SET @StrSQL = '([Calendar].[' +
right('0'+CAST(DAY(@DateFr) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateFr) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateFr) AS nvarchar) + '] : [Calendar].['
+ right('0'+CAST(DAY(@DateTo) AS nvarchar),2)
+ '/'+ right('0'+CAST(MONTH(@DateTo) AS nvarchar),2)
+ '/'+ CAST(YEAR(@DateTo) AS nvarchar) + '])';
SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet1 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT { [Measures].[Exclusive Amount] } ON COLUMNS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';
EXEC sp_executesql @StrMDX;
SET @Amount = (SELECT Amount FROM #MSCRM_Roll_DataSet1)
SET @StrMDX = 'INSERT INTO #MSCRM_Roll_DataSet2 SELECT * FROM OPENQUERY ([PROD_AS], ' + CHAR(39) +
'SELECT NON EMPTY { [Measures].[Exclusive Amount] } ON COLUMNS
,NON EMPTY { ([Customer].[Customers].[Ref Customer Site].ALLMEMBERS
* [Calendar].[Calendar Date Daily].ALLMEMBERS ) } ON ROWS
FROM ( SELECT ( { [Customer].[Customers].[CUSTOMER NAME].&[' + @CustomerCode +'] } ) ON COLUMNS
FROM ( SELECT ( { ' + @StrSQL + ' } ) ON COLUMNS FROM [Sales])) ' + CHAR(39) + ')';
EXEC sp_executesql @StrMDX;
INSERT INTO #MSCRM_Roll_DataSet3
SELECT MAX(DATENAME(m, CAST(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2)
+ '-' + SUBSTRING(CalendarDateDaily,1,2) AS SMALLDATETIME)))
, MAX(SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2))
, SUM (ExclAmount) from #MSCRM_Roll_DataSet2
GROUP BY SUBSTRING(CalendarDateDaily,7,4)
+ '-' + SUBSTRING(CalendarDateDaily,4,2);
WHILE @DateFr < @DateTo
BEGIN
INSERT INTO #MSCRM_Roll_DataSet3
SELECT DATENAME(m,@DateFr)
, DATENAME(year,@DateFr)
+ '-' + right('0'+CAST(DATEPART(MONTH,@DateFr)as NVARCHAR(2)),2) , 0;
SET @DateFr = DATEADD(m,1,@DateFr);
END;
SELECT MthName
,MthName + ' ' + SUBSTRING(YrMth,3 , 2) MthYr
,YrMth
,ExclAmount
FROM #MSCRM_Roll_DataSet3;
DROP TABLE #MSCRM_Roll_DataSet1;
DROP TABLE #MSCRM_Roll_DataSet2;
DROP TABLE #MSCRM_Roll_DataSet3;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
GO
/***************************************************************************************
Call Sample:
EXEC dbo.Last_Period_Revenue_MA
'BAUL4021 - Baulderstone Queensland Pty Ltd'
,-24
****************************************************************************************/
If you find this script useful please donate generously.
No comments:
Post a Comment