December 6, 2011 at 9:17 am
Lynn Pettis (12/6/2011)
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
There are others out here who would be willing to help you if you posted the requested information on the thread. If you are concerned about security, then obfusicate the data, change column names so that they don't match your live system (keep a map of the changes so you know what needs to be changed back), and modify your code to match the new column names.
We are volunteers, not paid help, on this site. Please help us help you.
+1 - Please provide the scripts on this thread.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 6, 2011 at 9:18 am
SQLRNNR (12/6/2011)
Lynn Pettis (12/6/2011)
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
There are others out here who would be willing to help you if you posted the requested information on the thread. If you are concerned about security, then obfusicate the data, change column names so that they don't match your live system (keep a map of the changes so you know what needs to be changed back), and modify your code to match the new column names.
We are volunteers, not paid help, on this site. Please help us help you.
+1 - Please provide the scripts on this thread.
Please ignore the duplicate post 😀
December 6, 2011 at 9:23 am
Ninja's_RGR'us (12/6/2011)
SQLRNNR (12/6/2011)
Lynn Pettis (12/6/2011)
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
There are others out here who would be willing to help you if you posted the requested information on the thread. If you are concerned about security, then obfusicate the data, change column names so that they don't match your live system (keep a map of the changes so you know what needs to be changed back), and modify your code to match the new column names.
We are volunteers, not paid help, on this site. Please help us help you.
+1 - Please provide the scripts on this thread.
Please ignore the duplicate post 😀
Echo
Echoo
Echoooo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 6, 2011 at 9:25 am
See all the free time we have to goof around. Too bad we don't have what we need to work on your issue.
December 6, 2011 at 9:43 am
It appears he's not worried about the data, but the code itself. Which is silly. All T-SQL can be replicated whether the script is shared or not. It's T-SQL. There's only so much that can be done with it.
Usually formulas and data are proprietary company information, both of which can be easily changed around to prevent people from getting private company information.
December 6, 2011 at 9:59 am
First of all, thank you all very much for your responses. Yes, I am not worried about the data as I am only worried about the better performance of my SPROC. Please find the SP below:
CREATE PROCEDURE [dbo].[MyTestSPROC]
@Category NVARCHAR(MAX),
@Program NVARCHAR(MAX)='',
@AName NVARCHAR(MAX) = '',
@Country NVARCHAR(MAX)=''
WITH RECOMPILE
AS
BEGIN
--@Category Filter
SET @Category = '''' + REPLACE(@Category, ',', ''',''') + ''''
DECLARE @tblCategory AS TABLE (FilterValue VARCHAR(100))
DECLARE @spFilterSQL1 AS VARCHAR(MAX)
SET @spFilterSQL1 = 'SELECT ' + REPLACE(@Category, ',', ' AS FilterValue UNION SELECT ')
INSERT INTO @tblCategory EXEC (@spFilterSQL1)
--Country Filter
SET @Country = '''' + REPLACE(@Country, ',', ''',''') + ''''
DECLARE @tblCountry AS TABLE (FilterValue VARCHAR(100))
DECLARE @spFilterSQL2 AS VARCHAR(MAX)
SET @spFilterSQL2 = 'SELECT ' + REPLACE(@Country, ',', ' AS FilterValue UNION SELECT ')
INSERT INTO @tblCountry EXEC (@spFilterSQL2)
--Program Filter
SET @Program = '''' + REPLACE(@Program, ',', ''',''') + ''''
DECLARE @tblProgram AS TABLE (FilterValue VARCHAR(100))
DECLARE @spFilterSQL3 AS VARCHAR(MAX)
SET @spFilterSQL3 = 'SELECT ' + REPLACE(@Program, ',', ' AS FilterValue UNION SELECT ')
INSERT INTO @tblProgram EXEC (@spFilterSQL3)
--AName Filter
SET @AName = '''' + REPLACE(@AName, ',', ''',''') + ''''
DECLARE @tblAName AS TABLE (FilterValue VARCHAR(100))
DECLARE @spFilterSQL4 AS VARCHAR(MAX)
SET @spFilterSQL4 = 'SELECT ' + REPLACE(@AName, ',', ' AS FilterValue UNION SELECT ')
INSERT INTO @tblAName EXEC (@spFilterSQL4)
DECLARE @YTDStartDate DATETIME,@YTDEndDate DATETIME
DECLARE @PDQUARTER VARCHAR(25)
DECLARE @PDMONTH1 VARCHAR(25)
DECLARE @PDMONTH2 VARCHAR(25)
DECLARE @PDMONTH3 VARCHAR(25)
DECLARE @PreviousQuarterStartDate DATETIME,@PreviousQuarterEndDate DATETIME
DECLARE @PreviousM1StartDate DATETIME,@PreviousM1EndDate DATETIME
DECLARE @PreviousM2StartDate DATETIME,@PreviousM2EndDate DATETIME
DECLARE @PreviousM3StartDate DATETIME,@PreviousM3EndDate DATETIME
DECLARE @currentYear INT, @currentMonth INT , @currentDay INT
--- getting current date
SELECT @YTDEndDate =convert(date,getdate())
SELECT @currentYear=datepart(yy,@YTDEndDate ),
@currentMonth=datepart(mm,@YTDEndDate ),
@currentDay=datepart(dd,@YTDEndDate )
/*****select YTD start date and end date*********/
SELECT @YTDStartDate =CASE WHEN @YTDEndDate >=convert(datetime, convert(varchar,@currentYear)+'-07-01')
THEN convert(datetime, convert(varchar,@currentYear)+'-07-01')
ELSE convert(datetime, convert(varchar,@currentYear-1)+'-07-01')
END
--SELECT @YTDStartDate , @YTDEndDate
/*****select Previous Quarter start date and end date*********/
SELECT @PreviousQuarterStartDate=
CASE WHEN ((@currentMonth+5)%12)/3=0 THEN convert(datetime, convert(varchar,@currentYear)+'-04-01')
WHEN ((@currentMonth+5)%12)/3=1 THEN convert(datetime, convert(varchar,@currentYear)+'-07-01')
WHEN ((@currentMonth+5)%12)/3=2 THEN convert(datetime, convert(varchar,@currentYear-1)+'-10-01')
ELSE convert(datetime, convert(varchar,@currentYear)+'-01-01')
END
SELECT @PreviousQuarterEndDate=dateadd(mm,3,@PreviousQuarterStartDate)
SELECT @PDQUARTER = CASE WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 4 AND 6 THEN 'QUARTER 4'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 7 AND 9 THEN 'QUARTER 1'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 10 AND 12 THEN 'QUARTER 2'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 1 AND 3 THEN 'QUARTER 3'
END
/*****select Previous three months start date and end date*********/
SELECT @PreviousM1EndDate=dateadd(dd,1-@currentDay,@YTDEndDate)
SELECT @PreviousM1StartDate=dateadd(mm,-1,@PreviousM1EndDate)
SELECT @PDMONTH1 = LEFT(DATENAME(MM,@PreviousM1StartDate),3) +'-'+ RIGHT(YEAR(@PreviousM1StartDate),2)
SELECT @PreviousM2StartDate=dateadd(mm,-1,@PreviousM1StartDate),@PreviousM2EndDate=@PreviousM1StartDate
SELECT @PDMONTH2 = LEFT(DATENAME(MM,@PreviousM2StartDate),3) +'-'+ RIGHT(YEAR(@PreviousM2StartDate),2)
SELECT @PreviousM3StartDate=dateadd(mm,-1,@PreviousM2StartDate),@PreviousM3EndDate=@PreviousM2StartDate
SELECT @PDMONTH3 = LEFT(DATENAME(MM,@PreviousM3StartDate),3) +'-'+ RIGHT(YEAR(@PreviousM3StartDate),2)
/*********actual query starts here**/
DECLARE @MinStartDate Date,@MaxEndDate Date
SELECT @MinStartDate=CASE WHEN @PreviousM3StartDate < @YTDStartDate
THEN @PreviousM3StartDate
ELSE @YTDStartDate END,
@MaxEndDate= @YTDEndDate
SELECT RMD.Product,RMD.Program,RMD.Category,RM.EventType,RM.EventDate,RM.SubscriberID ,RMD.ProgramOrderID
INTO #StagingResult
FROM dbo.RMEvnetData RM with (nolock)
JOIN dbo.RMProgramMetaData RMD with (nolock) ON RMD.Id = RM.ProgramMetadataID
JOIN @tblCountry AS C ON C.FilterValue=RM.Country
JOIN @tblCategory AS PC ON PC.FilterValue=RMD.Category
JOIN @tblProgram AS P ON P.FilterValue=RMD.Program
JOIN @tblAName AS A ON A.FilterValue = RM.AName
WHERE EventDate between @MinStartDate AND @MaxEndDate
--AND Program IN (SELECT * FROM @tblProgramName)
--AND RM.Country IN (SELECT * FROM @tblCountry)
--AND Category IN (SELECT * FROM @tblCategory)
CREATE INDEX IX_TEMPINDEX_StagingResult1 ON #StagingResult(EventDate) INCLUDE (Category,Product,Program,EventType,SubscriberID)
CREATE NONCLUSTERED INDEX IX_TEMPINDEX_StagingResult2 ON #StagingResult ([EventType],[EventDate])INCLUDE ([Category],[Program],[Product],[SubscriberID])
CREATE TABLE #DateRanges
(
id int not null identity(1,1),
PeriodDesc varchar(100),
StartDate datetime,
EndDate datetime
)
INSERT into #DateRanges
SELECT 'YTD', @YTDStartDate, @YTDEndDate
INSERT INTO #DateRanges
SELECT @PDQUARTER, @PreviousQuarterStartDate, @PreviousQuarterEndDate
INSERT INTO #DateRanges
SELECT @PDMONTH3, @PreviousM3StartDate, @PreviousM3EndDate
INSERT INTO #DateRanges
SELECT @PDMONTH2, @PreviousM2StartDate, @PreviousM2EndDate
INSERT INTO #DateRanges
SELECT @PDMONTH1, @PreviousM1StartDate, @PreviousM1EndDate
CREATE TABLE #ResultSet_TD
(
Category nvarchar(100),Program nvarchar(100),PeriodDesc varchar(100),TD_Impressions bigint,TD_UniqueVisitors bigint,[TD_VisitsPerVisitors] decimal(18,4)
)
Create Table #ResultSet_IPP
(
Category nvarchar(4000),Program nvarchar(4000),PeriodDesc nvarchar(100),
TotalClicks bigint,UniqueClicks bigint
)
CREATE TABLE #ResultSet
(
Product nvarchar(500), Program nvarchar(500),Category nvarchar(250),ID INT,ProgramOrder INT,PeriodDesc nvarchar(50),
TotalSends bigint, TotalBounce bigint,TotalDelivered bigint,TotalOpens bigint,
UniqueOpens bigint,TotalClick bigint,UniqueClick bigint,Unsubscribe bigint,[DeliveryRate] DECIMAL(18,4),
[TotalOpenRate] DECIMAL(18,4),[UniqueOpenRate] DECIMAL(18,4),[TotalClickToOpenRate] DECIMAL(18,4),
[UniqueClickToOpenRate] DECIMAL(18,4),[TotalCTR] DECIMAL(18,4),[UniqueCTR] DECIMAL(18,4)
)
DECLARE @id INT
DECLARE @PeriodDesc varchar(100)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE RM_CURSOR CURSOR FOR
SELECT D.id,D.PeriodDesc,D.StartDate,D.EndDate
FROM #DateRanges D
ORDER BY D.id
OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
--EMAIL data
WITH cte AS
(
SELECT Product,Program,Category,ProgramOrderID
,SUM(CASE WHEN EventType='sent' THEN 1 ELSE 0 END) AS TotalSends
,SUM(CASE WHEN EventType='Bounce' THEN 1 ELSE 0 END) AS TotalBounce
,SUM(CASE WHEN EventType='Open' THEN 1 ELSE 0 END) AS TotalOpens
,SUM(CASE WHEN EventType='Click' THEN 1 ELSE 0 END) AS TotalClick
,SUM(CASE WHEN EventType='sent' THEN 1 ELSE 0 END) - SUM(CASE WHEN EventType='Bounce' THEN 1 ELSE 0 END) AS TotalDelivered
,(SELECT COUNT(DISTINCT SubscriberID) FROM #StagingResult RM2
WHERE RM2.EventType='Open' AND RM2.EventDate between @StartDate AND @EndDate
AND RM2.Product=RM.Product AND RM2.Program =RM.Program AND RM2.Category =RM.Category
) AS UniqueOpens
,(SELECT COUNT(DISTINCT SubscriberID) FROM #StagingResult RM2
WHERE RM2.EventType='Click' AND RM2.EventDate between @StartDate AND @EndDate
AND RM2.Product=RM.Product AND RM2.Program =RM.Program AND RM2.Category =RM.Category
) AS UniqueClick
,SUM(CASE WHEN EventType='Unsubscribe' THEN 1 ELSE 0 END) as Unsubscribe
FROM #StagingResult RM with (nolock)
WHERE EventDate between @StartDate AND @EndDate
group by Product,Program,Category,ProgramOrderID
)
INSERT INTO #ResultSet
SELECT Product , Program,Category
,@id AS ID,ProgramOrderID,@PeriodDesc AS PeriodDesc,TotalSends, TotalBounce,TotalDelivered,TotalOpens,UniqueOpens,TotalClick,UniqueClick,Unsubscribe
,(CASE WHEN TotalSends=0 THEN 0.00 ELSE (TotalBounce*1.0)/(TotalSends*1.0) END) AS [DeliveryRate]
,(CASE WHEN TotalDelivered=0 THEN 0.00 ELSE (TotalOpens*1.0)/ (TotalDelivered*1.0) END) AS [TotalOpenRate]
,(CASE WHEN TotalDelivered=0 THEN 0.00 ELSE (UniqueOpens*1.0)/ (TotalDelivered*1.0) END) AS [UniqueOpenRate]
,(CASE WHEN TotalOpens=0 THEN 0.00 ELSE (TotalClick*1.0)/ (TotalOpens*1.0) END) as [TotalClickToOpenRate]
,(CASE WHEN UniqueOpens=0 THEN 0.00 ELSE (UniqueClick*1.0)/ (UniqueOpens*1.0) END) AS [UniqueClickToOpenRate]
,(CASE WHEN TotalDelivered=0 THEN 0.00 ELSE (TotalClick*1.0)/ (TotalDelivered*1.0) END) AS [TotalCTR]
,(CASE WHEN TotalDelivered=0 THEN 0.00 ELSE (UniqueClick*1.0)/ (TotalDelivered*1.0) END) AS [UniqueCTR]
FROM cte ;
-- INProduct Panel data
WITH cte AS
(
SELECT PM.Category AS Category,
PM.Program as Program,
SUM(CAST(RM.page_views AS BIGINT)) AS TotalClicks,
SUM(CAST(RM.visits AS BIGINT)) AS UniqueClicks
FROM dbo.ProgramMetaData_URLMapping PM
JOIN dbo.[mVidVKGyeP6] RM ON
PM.URL = RM.url
WHERE RM.time BETWEEN @StartDate AND @EndDate
AND PM.Category IN (SELECT PC.FilterValue FROM @tblCategory PC)
AND PM.Program IN (SELECT P.FilterValue FROM @tblProgram P)
GROUP BY PM.Category,PM.Program
)
INSERT INTO #ResultSet_IPP
SELECT Category,Program,@PeriodDesc as PeriodDesc,TotalClicks,UniqueClicks
FROM cte
---Trial and deploy data.
;WITH CTE AS
(
SELECT SUBSTRING(pages,29,2) AS LocalID
,CASE WHEN pages LIKE '%midsize-enterprise%' THEN 'Midsize-Enterprise'
WHEN pages LIKE '%small-business%' THEN 'Small-Business'
WHEN pages LIKE '%deployment-support%' THEN 'Deployment-Support'
WHEN pages LIKE '%education%' THEN 'Education'
END AS Category
--,CASE WHEN url LIKE '%Free-Trial%' THEN 'Trial'
-- WHEN url LIKE '%deployment-support%' THEN 'Deployment'
-- END AS Program
,CASE
WHEN url LIKE '%Free-Trial%' AND pages LIKE '%midsize-enterprise%' THEN 'Ent Trial'
WHEN url LIKE '%Free-Trial%' AND pages LIKE '%small-business%' THEN 'SB Trial'
WHEN url LIKE '%deployment-support%' AND pages LIKE '%midsize-enterprise%' THEN 'Ent Deployment'
WHEN url LIKE '%deployment-support%' AND pages LIKE '%small-business%' THEN 'SB Deployment'
END AS Program
,* FROM dbo.[g6kSdD8tFq6] AS RM
WHERE url LIKE '%deployment-support%' OR url LIKE '%Free-Trial%'
AND url NOT LIKE '%Free-Trial/try-%' AND url NOT LIKE '%Partner-deployment-support%'
AND RM.time BETWEEN @StartDate AND @EndDate
)
INSERT INTO #ResultSet_TD
SELECT c.Category,C.Program,@PeriodDesc,SUM(convert(int,C.visits)) AS TD_Impressions,SUM(convert(int,C.page_views)) AS TD_UniqueVisitors,
CASE WHEN SUM(cast(c.visits as decimal(18,4)))=0 THEN 0.00
ELSE (SUM(cast (c.page_views as decimal(18,4)))/ SUM(cast(c.visits as decimal(18,4)))) END as [TD_VisitsPerVisitors]
FROM CTE C
LEFT OUTER JOIN dbo.vwSecuredSalesGeographyDim GD
ON C.LocalID=ISOCountryCode
--JOIN @tblAName A ON A.FilterValue = GD.AName
JOIN @tblCountry CO ON CO.FilterValue = GD.Country
--JOIN @tblCategory PC ON PC.FilterValue = C.Category
JOIN @tblProgram PR ON PR.FilterValue = C.Program
GROUP BY c.Category,C.Program
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
END
CLOSE RM_CURSOR
DEALLOCATE RM_CURSOR
SELECT * INTO #FinalResulSet FROM
(
SELECT
Product ,
'EMAIL' AS DataSource,
Program,
Category,
ID,
ProgramOrder,
PeriodDesc,
TotalSends,
TotalBounce,
TotalDelivered,
TotalOpens,
UniqueOpens,
TotalClick,
UniqueClick,
Unsubscribe,
[DeliveryRate],
[TotalOpenRate],
[UniqueOpenRate],
[TotalClickToOpenRate],
[UniqueClickToOpenRate],
[TotalCTR],
[UniqueCTR]
FROM #ResultSet
UNION
SELECT DISTINCT
R.Product
,'EMAIL' AS DataSource
,R.Program,
R.Category
,D1.id
,R.ProgramOrder
,D1.PeriodDesc
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
FROM #ResultSet AS R
JOIN #DateRanges as D1
ON R.PeriodDesc<>D1.PeriodDesc
LEFT JOIN #ResultSet as R2
ON R.Program=R2.Program AND R.Category=R2.Category
AND R2.PeriodDesc =D1.PeriodDesc AND R.Product=R2.Product
WHERE R2.PeriodDesc IS NULL
) AS TX1
--SELECT * INTO ##FinalResultSet2 FROM #ResultSet_IPP
--SELECT * INTO ##FinalResultSet3 FROM #ResultSet_TD
SELECT A.*,
ISNULL(B.TotalClicks,0) AS InProductPanel_TotalClicks,
ISNULL(B.UniqueClicks ,0) AS InProductPanel_UniqueClicks,
ISNULL(C.TD_Impressions,0) as TD_Impressions,
ISNULL(TD_UniqueVisitors,0) as TD_UniqueVisitors,
ISNULL([TD_VisitsPerVisitors],0) as [TD_VisitsPerVisitors]
from #FinalResulSet as A
LEFT JOIN #ResultSet_IPP AS B
ON A.Program=B.Program AND A.PeriodDesc=B.PeriodDesc
LEFT JOIN #ResultSet_TD AS C
ON A.Program=C.Program AND A.PeriodDesc=C.PeriodDesc
END
December 6, 2011 at 10:01 am
If you worry about posting the code in public but you're looking for help then you might consider to hire a consultant.
Of course, this would involve money.
But the "major (and only?) disadvantage" of this site simply is to discuss the issue in public which means to post the requested code and obfuscated date. If that's beyond the stuff you're allowed to do, then there's little we can do to help you.
With the money you spend on a consultant you don't only pay for his/her knowledge but also for his/her confidentiality.
December 6, 2011 at 10:03 am
Reformated for Sanity :
CREATE PROCEDURE [dbo].[MyTestSPROC]
@Category NVARCHAR(MAX)
, @Program NVARCHAR(MAX) = ''
, @AName NVARCHAR(MAX) = ''
, @Country NVARCHAR(MAX) = ''
WITH RECOMPILE
AS
BEGIN
--@Category Filter
SET @Category = '''' + REPLACE(@Category , ',' , ''',''') + ''''
DECLARE @tblCategory AS TABLE
(
FilterValue VARCHAR(100)
)
DECLARE @spFilterSQL1 AS VARCHAR(MAX)
SET @spFilterSQL1 = 'SELECT ' + REPLACE(@Category , ',' ,
' AS FilterValue UNION SELECT ')
INSERT INTO
@tblCategory
EXEC (
@spFilterSQL1
)
--Country Filter
SET @Country = '''' + REPLACE(@Country , ',' , ''',''') + ''''
DECLARE @tblCountry AS TABLE
(
FilterValue VARCHAR(100)
)
DECLARE @spFilterSQL2 AS VARCHAR(MAX)
SET @spFilterSQL2 = 'SELECT ' + REPLACE(@Country , ',' ,
' AS FilterValue UNION SELECT ')
INSERT INTO
@tblCountry
EXEC (
@spFilterSQL2
)
--Program Filter
SET @Program = '''' + REPLACE(@Program , ',' , ''',''') + ''''
DECLARE @tblProgram AS TABLE
(
FilterValue VARCHAR(100)
)
DECLARE @spFilterSQL3 AS VARCHAR(MAX)
SET @spFilterSQL3 = 'SELECT ' + REPLACE(@Program , ',' ,
' AS FilterValue UNION SELECT ')
INSERT INTO
@tblProgram
EXEC (
@spFilterSQL3
)
--AName Filter
SET @AName = '''' + REPLACE(@AName , ',' , ''',''') + ''''
DECLARE @tblAName AS TABLE
(
FilterValue VARCHAR(100)
)
DECLARE @spFilterSQL4 AS VARCHAR(MAX)
SET @spFilterSQL4 = 'SELECT ' + REPLACE(@AName , ',' ,
' AS FilterValue UNION SELECT ')
INSERT INTO
@tblAName
EXEC (
@spFilterSQL4
)
DECLARE
@YTDStartDate DATETIME
, @YTDEndDate DATETIME
DECLARE @PDQUARTER VARCHAR(25)
DECLARE @PDMONTH1 VARCHAR(25)
DECLARE @PDMONTH2 VARCHAR(25)
DECLARE @PDMONTH3 VARCHAR(25)
DECLARE
@PreviousQuarterStartDate DATETIME
, @PreviousQuarterEndDate DATETIME
DECLARE
@PreviousM1StartDate DATETIME
, @PreviousM1EndDate DATETIME
DECLARE
@PreviousM2StartDate DATETIME
, @PreviousM2EndDate DATETIME
DECLARE
@PreviousM3StartDate DATETIME
, @PreviousM3EndDate DATETIME
DECLARE
@currentYear INT
, @currentMonth INT
, @currentDay INT
--- getting current date
SELECT
@YTDEndDate = convert(date , getdate())
SELECT
@currentYear = datepart(yy , @YTDEndDate)
, @currentMonth = datepart(mm , @YTDEndDate)
, @currentDay = datepart(dd , @YTDEndDate)
/*****select YTD start date and end date*********/
SELECT
@YTDStartDate = CASE WHEN @YTDEndDate >= convert(datetime , convert(varchar , @currentYear)
+ '-07-01')
THEN convert(datetime , convert(varchar , @currentYear)
+ '-07-01')
ELSE convert(datetime , convert(varchar , @currentYear
- 1) + '-07-01')
END
--SELECT @YTDStartDate , @YTDEndDate
/*****select Previous Quarter start date and end date*********/
SELECT
@PreviousQuarterStartDate = CASE WHEN ( ( @currentMonth + 5 ) % 12 )
/ 3 = 0
THEN convert(datetime , convert(varchar , @currentYear)
+ '-04-01')
WHEN ( ( @currentMonth + 5 ) % 12 )
/ 3 = 1
THEN convert(datetime , convert(varchar , @currentYear)
+ '-07-01')
WHEN ( ( @currentMonth + 5 ) % 12 )
/ 3 = 2
THEN convert(datetime , convert(varchar , @currentYear
- 1) + '-10-01')
ELSE convert(datetime , convert(varchar , @currentYear)
+ '-01-01')
END
SELECT
@PreviousQuarterEndDate = dateadd(mm , 3 ,
@PreviousQuarterStartDate)
SELECT
@PDQUARTER = CASE WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 4 AND 6
THEN 'QUARTER 4'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 7 AND 9
THEN 'QUARTER 1'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 10 AND 12
THEN 'QUARTER 2'
WHEN MONTH(@PreviousQuarterStartDate) BETWEEN 1 AND 3
THEN 'QUARTER 3'
END
/*****select Previous three months start date and end date*********/
SELECT
@PreviousM1EndDate = dateadd(dd , 1 - @currentDay , @YTDEndDate)
SELECT
@PreviousM1StartDate = dateadd(mm , -1 , @PreviousM1EndDate)
SELECT
@PDMONTH1 = LEFT(DATENAME(MM , @PreviousM1StartDate) , 3) + '-'
+ RIGHT(YEAR(@PreviousM1StartDate) , 2)
SELECT
@PreviousM2StartDate = dateadd(mm , -1 , @PreviousM1StartDate)
, @PreviousM2EndDate = @PreviousM1StartDate
SELECT
@PDMONTH2 = LEFT(DATENAME(MM , @PreviousM2StartDate) , 3) + '-'
+ RIGHT(YEAR(@PreviousM2StartDate) , 2)
SELECT
@PreviousM3StartDate = dateadd(mm , -1 , @PreviousM2StartDate)
, @PreviousM3EndDate = @PreviousM2StartDate
SELECT
@PDMONTH3 = LEFT(DATENAME(MM , @PreviousM3StartDate) , 3) + '-'
+ RIGHT(YEAR(@PreviousM3StartDate) , 2)
/*********actual query starts here**/
DECLARE
@MinStartDate Date
, @MaxEndDate Date
SELECT
@MinStartDate = CASE WHEN @PreviousM3StartDate < @YTDStartDate
THEN @PreviousM3StartDate
ELSE @YTDStartDate
END
, @MaxEndDate = @YTDEndDate
SELECT
RMD.Product
, RMD.Program
, RMD.Category
, RM.EventType
, RM.EventDate
, RM.SubscriberID
, RMD.ProgramOrderID
INTO
#StagingResult
FROM
dbo.RMEvnetData RM with ( nolock )
JOIN dbo.RMProgramMetaData RMD with ( nolock )
ON RMD.Id = RM.ProgramMetadataID
JOIN @tblCountry AS C
ON C.FilterValue = RM.Country
JOIN @tblCategory AS PC
ON PC.FilterValue = RMD.Category
JOIN @tblProgram AS P
ON P.FilterValue = RMD.Program
JOIN @tblAName AS A
ON A.FilterValue = RM.AName
WHERE
EventDate between @MinStartDate AND @MaxEndDate
--AND Program IN (SELECT * FROM @tblProgramName)
--AND RM.Country IN (SELECT * FROM @tblCountry)
--AND Category IN (SELECT * FROM @tblCategory)
CREATE INDEX IX_TEMPINDEX_StagingResult1 ON #StagingResult ( EventDate )
INCLUDE ( Category , Product , Program , EventType , SubscriberID )
CREATE NONCLUSTERED INDEX IX_TEMPINDEX_StagingResult2 ON #StagingResult ( [EventType] , [EventDate] )
INCLUDE ( [Category] , [Program] , [Product] , [SubscriberID] )
CREATE TABLE #DateRanges
(
id int not null
identity(1 , 1)
, PeriodDesc varchar(100)
, StartDate datetime
, EndDate datetime
)
INSERT into
#DateRanges
SELECT
'YTD'
, @YTDStartDate
, @YTDEndDate
INSERT INTO
#DateRanges
SELECT
@PDQUARTER
, @PreviousQuarterStartDate
, @PreviousQuarterEndDate
INSERT INTO
#DateRanges
SELECT
@PDMONTH3
, @PreviousM3StartDate
, @PreviousM3EndDate
INSERT INTO
#DateRanges
SELECT
@PDMONTH2
, @PreviousM2StartDate
, @PreviousM2EndDate
INSERT INTO
#DateRanges
SELECT
@PDMONTH1
, @PreviousM1StartDate
, @PreviousM1EndDate
CREATE TABLE #ResultSet_TD
(
Category nvarchar(100)
, Program nvarchar(100)
, PeriodDesc varchar(100)
, TD_Impressions bigint
, TD_UniqueVisitors bigint
, [TD_VisitsPerVisitors] decimal(18 , 4)
)
Create Table #ResultSet_IPP
(
Category nvarchar(4000)
, Program nvarchar(4000)
, PeriodDesc nvarchar(100)
, TotalClicks bigint
, UniqueClicks bigint
)
CREATE TABLE #ResultSet
(
Product nvarchar(500)
, Program nvarchar(500)
, Category nvarchar(250)
, ID INT
, ProgramOrder INT
, PeriodDesc nvarchar(50)
, TotalSends bigint
, TotalBounce bigint
, TotalDelivered bigint
, TotalOpens bigint
, UniqueOpens bigint
, TotalClick bigint
, UniqueClick bigint
, Unsubscribe bigint
, [DeliveryRate] DECIMAL(18 , 4)
, [TotalOpenRate] DECIMAL(18 , 4)
, [UniqueOpenRate] DECIMAL(18 , 4)
, [TotalClickToOpenRate] DECIMAL(18 , 4)
, [UniqueClickToOpenRate] DECIMAL(18 , 4)
, [TotalCTR] DECIMAL(18 , 4)
, [UniqueCTR] DECIMAL(18 , 4)
)
DECLARE @id INT
DECLARE @PeriodDesc varchar(100)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE RM_CURSOR CURSOR
FOR SELECT
D.id
, D.PeriodDesc
, D.StartDate
, D.EndDate
FROM
#DateRanges D
ORDER BY
D.id
OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id , @PeriodDesc , @StartDate ,
@EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
--EMAIL data
WITH cte
AS (
SELECT
Product
, Program
, Category
, ProgramOrderID
, SUM(CASE WHEN EventType = 'sent' THEN 1
ELSE 0
END) AS TotalSends
, SUM(CASE WHEN EventType = 'Bounce' THEN 1
ELSE 0
END) AS TotalBounce
, SUM(CASE WHEN EventType = 'Open' THEN 1
ELSE 0
END) AS TotalOpens
, SUM(CASE WHEN EventType = 'Click' THEN 1
ELSE 0
END) AS TotalClick
, SUM(CASE WHEN EventType = 'sent' THEN 1
ELSE 0
END)
- SUM(CASE WHEN EventType = 'Bounce' THEN 1
ELSE 0
END) AS TotalDelivered
, (
SELECT
COUNT(DISTINCT SubscriberID)
FROM
#StagingResult RM2
WHERE
RM2.EventType = 'Open'
AND RM2.EventDate between @StartDate AND @EndDate
AND RM2.Product = RM.Product
AND RM2.Program = RM.Program
AND RM2.Category = RM.Category
) AS UniqueOpens
, (
SELECT
COUNT(DISTINCT SubscriberID)
FROM
#StagingResult RM2
WHERE
RM2.EventType = 'Click'
AND RM2.EventDate between @StartDate AND @EndDate
AND RM2.Product = RM.Product
AND RM2.Program = RM.Program
AND RM2.Category = RM.Category
) AS UniqueClick
, SUM(CASE WHEN EventType = 'Unsubscribe' THEN 1
ELSE 0
END) as Unsubscribe
FROM
#StagingResult RM with ( nolock )
WHERE
EventDate between @StartDate AND @EndDate
group by
Product
, Program
, Category
, ProgramOrderID
)
INSERT INTO
#ResultSet
SELECT
Product
, Program
, Category
, @id AS ID
, ProgramOrderID
, @PeriodDesc AS PeriodDesc
, TotalSends
, TotalBounce
, TotalDelivered
, TotalOpens
, UniqueOpens
, TotalClick
, UniqueClick
, Unsubscribe
, ( CASE WHEN TotalSends = 0 THEN 0.00
ELSE ( TotalBounce * 1.0 ) / ( TotalSends * 1.0 )
END ) AS [DeliveryRate]
, ( CASE WHEN TotalDelivered = 0 THEN 0.00
ELSE ( TotalOpens * 1.0 )
/ ( TotalDelivered * 1.0 )
END ) AS [TotalOpenRate]
, ( CASE WHEN TotalDelivered = 0 THEN 0.00
ELSE ( UniqueOpens * 1.0 )
/ ( TotalDelivered * 1.0 )
END ) AS [UniqueOpenRate]
, ( CASE WHEN TotalOpens = 0 THEN 0.00
ELSE ( TotalClick * 1.0 ) / ( TotalOpens * 1.0 )
END ) as [TotalClickToOpenRate]
, ( CASE WHEN UniqueOpens = 0 THEN 0.00
ELSE ( UniqueClick * 1.0 ) / ( UniqueOpens * 1.0 )
END ) AS [UniqueClickToOpenRate]
, ( CASE WHEN TotalDelivered = 0 THEN 0.00
ELSE ( TotalClick * 1.0 )
/ ( TotalDelivered * 1.0 )
END ) AS [TotalCTR]
, ( CASE WHEN TotalDelivered = 0 THEN 0.00
ELSE ( UniqueClick * 1.0 )
/ ( TotalDelivered * 1.0 )
END ) AS [UniqueCTR]
FROM
cte ;
-- INProduct Panel data
WITH cte
AS (
SELECT
PM.Category AS Category
, PM.Program as Program
, SUM(CAST(RM.page_views AS BIGINT)) AS TotalClicks
, SUM(CAST(RM.visits AS BIGINT)) AS UniqueClicks
FROM
dbo.ProgramMetaData_URLMapping PM
JOIN dbo.[mVidVKGyeP6] RM
ON PM.URL = RM.url
WHERE
RM.time BETWEEN @StartDate AND @EndDate
AND PM.Category IN ( SELECT
PC.FilterValue
FROM
@tblCategory PC )
AND PM.Program IN ( SELECT
P.FilterValue
FROM
@tblProgram P )
GROUP BY
PM.Category
, PM.Program
)
INSERT INTO
#ResultSet_IPP
SELECT
Category
, Program
, @PeriodDesc as PeriodDesc
, TotalClicks
, UniqueClicks
FROM
cte
---Trial and deploy data.
;
WITH CTE
AS (
SELECT
SUBSTRING(pages , 29 , 2) AS LocalID
, CASE WHEN pages LIKE '%midsize-enterprise%'
THEN 'Midsize-Enterprise'
WHEN pages LIKE '%small-business%'
THEN 'Small-Business'
WHEN pages LIKE '%deployment-support%'
THEN 'Deployment-Support'
WHEN pages LIKE '%education%'
THEN 'Education'
END AS Category
--,CASE WHEN url LIKE '%Free-Trial%' THEN 'Trial'
-- WHEN url LIKE '%deployment-support%' THEN 'Deployment'
-- END AS Program
, CASE WHEN url LIKE '%Free-Trial%'
AND pages LIKE '%midsize-enterprise%'
THEN 'Ent Trial'
WHEN url LIKE '%Free-Trial%'
AND pages LIKE '%small-business%'
THEN 'SB Trial'
WHEN url LIKE '%deployment-support%'
AND pages LIKE '%midsize-enterprise%'
THEN 'Ent Deployment'
WHEN url LIKE '%deployment-support%'
AND pages LIKE '%small-business%'
THEN 'SB Deployment'
END AS Program
, *
FROM
dbo.[g6kSdD8tFq6] AS RM
WHERE
url LIKE '%deployment-support%'
OR url LIKE '%Free-Trial%'
AND url NOT LIKE '%Free-Trial/try-%'
AND url NOT LIKE '%Partner-deployment-support%'
AND RM.time BETWEEN @StartDate AND @EndDate
)
INSERT INTO
#ResultSet_TD
SELECT
c.Category
, C.Program
, @PeriodDesc
, SUM(convert(int , C.visits)) AS TD_Impressions
, SUM(convert(int , C.page_views)) AS TD_UniqueVisitors
, CASE WHEN SUM(cast(c.visits as decimal(18 , 4))) = 0
THEN 0.00
ELSE ( SUM(cast(c.page_views as decimal(18 , 4)))
/ SUM(cast(c.visits as decimal(18 , 4))) )
END as [TD_VisitsPerVisitors]
FROM
CTE C
LEFT OUTER JOIN dbo.vwSecuredSalesGeographyDim GD
ON C.LocalID = ISOCountryCode
--JOIN @tblAName A ON A.FilterValue = GD.AName
JOIN @tblCountry CO
ON CO.FilterValue = GD.Country
--JOIN @tblCategory PC ON PC.FilterValue = C.Category
JOIN @tblProgram PR
ON PR.FilterValue = C.Program
GROUP BY
c.Category
, C.Program
FETCH NEXT FROM RM_CURSOR INTO @id , @PeriodDesc , @StartDate ,
@EndDate
END
CLOSE RM_CURSOR
DEALLOCATE RM_CURSOR
SELECT
*
INTO
#FinalResulSet
FROM
(
SELECT
Product
, 'EMAIL' AS DataSource
, Program
, Category
, ID
, ProgramOrder
, PeriodDesc
, TotalSends
, TotalBounce
, TotalDelivered
, TotalOpens
, UniqueOpens
, TotalClick
, UniqueClick
, Unsubscribe
, [DeliveryRate]
, [TotalOpenRate]
, [UniqueOpenRate]
, [TotalClickToOpenRate]
, [UniqueClickToOpenRate]
, [TotalCTR]
, [UniqueCTR]
FROM
#ResultSet
UNION
SELECT DISTINCT
R.Product
, 'EMAIL' AS DataSource
, R.Program
, R.Category
, D1.id
, R.ProgramOrder
, D1.PeriodDesc
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM
#ResultSet AS R
JOIN #DateRanges as D1
ON R.PeriodDesc <> D1.PeriodDesc
LEFT JOIN #ResultSet as R2
ON R.Program = R2.Program
AND R.Category = R2.Category
AND R2.PeriodDesc = D1.PeriodDesc
AND R.Product = R2.Product
WHERE
R2.PeriodDesc IS NULL
) AS TX1
--SELECT * INTO ##FinalResultSet2 FROM #ResultSet_IPP
--SELECT * INTO ##FinalResultSet3 FROM #ResultSet_TD
SELECT
A.*
, ISNULL(B.TotalClicks , 0) AS InProductPanel_TotalClicks
, ISNULL(B.UniqueClicks , 0) AS InProductPanel_UniqueClicks
, ISNULL(C.TD_Impressions , 0) as TD_Impressions
, ISNULL(TD_UniqueVisitors , 0) as TD_UniqueVisitors
, ISNULL([TD_VisitsPerVisitors] , 0) as [TD_VisitsPerVisitors]
from
#FinalResulSet as A
LEFT JOIN #ResultSet_IPP AS B
ON A.Program = B.Program
AND A.PeriodDesc = B.PeriodDesc
LEFT JOIN #ResultSet_TD AS C
ON A.Program = C.Program
AND A.PeriodDesc = C.PeriodDesc
END
December 6, 2011 at 10:09 am
Still missing requested information: DDL for the table(s) involved in the query, sample data for the table(s), expected results based on the sample data provided. As this is a stored procedure, this also includes telling us the input parameters used to get the requested expected results from the sample data.
December 6, 2011 at 10:10 am
My first question, before anything else, is why are you using WITH RECOMPILE in the sproc?
December 6, 2011 at 10:13 am
Brandie Tarvin (12/6/2011)
My first question, before anything else, is why are you using WITH RECOMPILE in the sproc?
4 multiple value parameters. Good idea, but useles with @t.
@OP, can you post the actual execution plan? Got a lot of things to look for.
December 6, 2011 at 10:23 am
I would start with replacing the table variables with indexed temp tables.
Then I would replace the c.u.r.s.o.r. *cough* by adding the #DateRanges table using CROSS APPLY to each of the insert statements inside the loop.
Add appropriate indexes as needed. 😎
December 7, 2011 at 2:29 am
Use Row_Number OVER(ORDER BY (Select 0)) and based on row_number run query. It is very faster than Cursor.
December 7, 2011 at 10:41 pm
CELKO (12/7/2011)
I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
Glad to know you like MySQL, too bad this is a Microsoft SQL Server forum and what you like isn't available here.
December 8, 2011 at 4:32 am
CELKO (12/7/2011)
Oh, there is no such thing in RDBMS as that silly, generic “id”;
If it didn't exist, SQL wouldn't allow people to use it as the name of a column. Unfortunately, it does exist and people do use it.
But I totally agree that they shouldn't. Calling the identifying column of every table "ID" is such a headache and hard to keep track of when some of the tables join on ID and other IDs are not set up to provide referential integrity.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply