June 10, 2008 at 1:39 am
Karl,
Me too did the same thing for taking current year and previous year.The problem is with performance.It takes nearly 5 mints if i gave the period 2 year or 3 year difference.How can we solve then?
This is my sample procedure
CREATE TABLE #temptable1 (
RDCCashInCur int,
RDCCashOutCur int,
RDCCashCur int,
Read_Games_BetCur int,
READ_COINS_DROPCur int,
Site_NameCur varchar(20) ,
Machine_Type_CodeCur varchar(20),
Bar_Position_IDCur int,
Machine_Type_IDCur int,
Bar_Position_NameCur varchar(20),
Installation_NoCur int,
Zone_NameCur varchar(20) ,
machine_name varchar(40),
Read_DateCur datetime,
RDCCashInPrev int,
RDCCashOutPrev int,
RDCCashPrev int,
Read_Games_BetPrev int,
READ_COINS_DROPPrev int,
Bar_Position_IDPrev int,
Installation_NoPrev int
)
CREATE TABLE #temptable2(
RDCCashInPrev int,
RDCCashOutPrev int,
RDCCashPrev int,
Read_Games_BetPrev int,
READ_COINS_DROPPrev int,
Bar_Position_IDPrev int,
Installation_NoPrev int
)
INSERT INTO #temptable1
(RDCCashInCur ,
RDCCashOutCur ,
RDCCashCur ,
Read_Games_BetCur ,
READ_COINS_DROPCur ,
Site_NameCur ,
Machine_Type_CodeCur ,
Bar_Position_IDCur ,
Machine_Type_IDCur ,
Bar_Position_NameCur ,
Installation_NoCur ,
Zone_NameCur ,
machine_name
)
SELECT
VW_ReadYearonYear.RDCCashIn ,
VW_ReadYearonYear.RDCCashOut,
VW_ReadYearonYear.RDCCash,
VW_ReadYearonYear.Read_Games_Bet ,
VW_ReadYearonYear.READ_COIN_DROP ,
Site.Site_Name ,
VW_ReadYearonYear.Machine_Type_Code ,
Bar_Position.Bar_Position_ID ,
VW_ReadYearonYear.Machine_Type_ID ,
Bar_Position.Bar_Position_Name,
VW_ReadYearonYear.Installation_No,
Zone.Zone_Name,
VW_ReadYearonYear.Machine_Name
FROM
dbo.VW_ReadYearonYear (nolock)
INNER JOIN dbo.Bar_Position (nolock) ON VW_ReadYearonYear.Bar_Position_ID = Bar_Position.Bar_Position_ID
INNER JOIN dbo.Site (nolock) ON Bar_Position.Site_ID = Site.Site_ID
LEFT OUTER JOIN dbo.Zone (nolock) ON Bar_Position.Zone_ID = Zone.Zone_ID
WHERE
cast ( VW_ReadYearonYear.Read_Date AS DATETIME )between convert ( datetime, @startdate, 106 )and convert( datetime, @enddate, 106 )
AND (( @subcompany IS NULL )OR( @subcompany IS NOT NULL AND site.sub_company_id = @subcompany))
AND (( @region IS NULL )OR ( @region IS NOT NULL AND site.sub_company_region_id = @region))
AND ( ( @area IS NULL )OR( @area IS NOT NULL AND site.sub_company_area_id = @area ))
AND ( ( @district IS NULL )OR( @district IS NOT NULL AND site.sub_company_district_id = @district))
AND ( ( @site IS NULL )OR( @site IS NOT NULL AND site.site_id = @site ))
AND
(convert(datetime,VW_ReadYearonYear.installation_Start_Date,103)<convert(datetime,@EndDate,103)
AND
(convert(datetime,VW_ReadYearonYear.installation_End_Date,103)>convert(datetime,@EndDate,103) OR convert(datetime,VW_ReadYearonYear.installation_End_Date,103) IS NULL))
INSERT INTO #temptable2
SELECT
VW_ReadYearonYear.RDCCashIn ,
VW_ReadYearonYear.RDCCashOut,
VW_ReadYearonYear.RDCCash,
VW_ReadYearonYear.Read_Games_Bet ,
VW_ReadYearonYear.READ_COIN_DROP ,
Bar_Position.Bar_Position_ID ,
VW_ReadYearonYear.Installation_No
FROM
dbo.VW_ReadYearonYear (nolock)
INNER JOIN dbo.Bar_Position (nolock) ON VW_ReadYearonYear.Bar_Position_ID = Bar_Position.Bar_Position_ID
INNER JOIN dbo.Site (nolock) ON Bar_Position.Site_ID = Site.Site_ID
INNER JOIN #temptable1 ON VW_ReadYearonYear.Installation_No=#temptable1.Installation_NoCur
LEFT OUTER JOIN dbo.Zone (nolock) ON Bar_Position.Zone_ID = Zone.Zone_ID
where
cast ( VW_ReadYearonYear.Read_Date AS DATETIME )between convert ( datetime, @startdateprev, 106 )and convert( datetime, @Enddateprev, 106 )
AND (( @subcompany IS NULL )OR( @subcompany IS NOT NULL AND site.sub_company_id = @subcompany))
AND (( @region IS NULL )OR ( @region IS NOT NULL AND site.sub_company_region_id = @region))
AND ( ( @area IS NULL )OR( @area IS NOT NULL AND site.sub_company_area_id = @area ))
AND ( ( @district IS NULL )OR( @district IS NOT NULL AND site.sub_company_district_id = @district))
AND ( ( @site IS NULL )OR( @site IS NOT NULL AND site.site_id = @site ))
AND
(convert(datetime,VW_ReadYearonYear.installation_Start_Date,103)<convert(datetime,@Enddateprev,103)
AND
(convert(datetime,VW_ReadYearonYear.installation_End_Date,103)>convert(datetime,@Enddateprev,103) OR convert(datetime,VW_ReadYearonYear.installation_End_Date,103) IS NULL))
UPDATE #temptable1
SET #temptable1.RDCCashInPrev=#temptable2.RDCCashInPrev,
#temptable1.RDCCashOutPrev=#temptable2.RDCCashOutPrev,
#temptable1.RDCCashPrev=#temptable2.RDCCashPrev,
#temptable1.Read_Games_BetPrev= #temptable2.Read_Games_BetPrev,
#temptable1.READ_COINS_DROPPrev= #temptable2.READ_COINS_DROPPrev,
#temptable1.Installation_NoPrev= #temptable2.Installation_NoPrev,
#temptable1.Bar_Position_IDPrev=#temptable2.Bar_Position_IDPrev
FROM #temptable2
LEFT JOIN #temptable1 ON
#temptable2.Bar_Position_IDPrev=#temptable1.Bar_Position_IDCur
AND #temptable2.Installation_NoPrev=#temptable1.Installation_NoCur
SELECT
CurYear.RDCCashInCur ,
CurYear.RDCCashOutCur,
CurYear.RDCCashCur ,
CurYear.Read_Games_BetCur ,
CurYear.READ_COINS_DROPCur ,
CurYear.Site_NameCur as Site_Name,
CurYear.Machine_Type_CodeCur as Machine_Type_Code,
CurYear.Bar_Position_IDCur as Bar_Position_ID,
CurYear.Machine_Type_IDCur as Machine_Type_ID,
CurYear.Bar_Position_NameCur as Bar_Position_Name,
CurYear.Installation_NoCur as Installation_No,
CurYear.Zone_NameCur as Zone_Name ,
CurYear.machine_name,
CurYear.RDCCashInPrev,
CurYear.RDCCashOutPrev,
CurYear.RDCCashPrev,
CurYear.Read_Games_BetPrev,
CurYear.READ_COINS_DROPPrev,
CurYear.Installation_NoPrev,
CurYear.Bar_Position_IDPrev
FROM #temptable1 AS CurYear
drop table #temptable1
drop table #temptable2
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply