|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2012-08-23 : 17:39:01
|
| Thanks!;WITH statisticlist (sourceid, foreignid, siteid, statdate, isshowroom, views, clicks) AS (SELECT statistic.sourceid, Cast(statistic.statkey AS VARCHAR(20)), statistic.siteid, statistic.statdate, definition.isshowroom, Sum(CASE WHEN definition.statgroupid = 1 THEN statistic.statvalue ELSE 0 END) AS Views, Sum(CASE WHEN definition.statgroupid = 2 THEN statistic.statvalue ELSE 0 END) AS Clicks FROM statistic.statistic.dbo.statistic WITH (readuncommitted) INNER JOIN statistic.statistic.dbo.definition WITH ( readuncommitted) ON definition.definitionid = statistic.definitionid WHERE statistic.statdate = NULL AND statistic.siteid = '01-01-2012' GROUP BY statistic.sourceid, statistic.statkey, statistic.siteid, statistic.statdate, definition.isshowroom) --INSERT INTO SiteStatsHoldingFinal SELECT Cast(statisticlist.sourceid AS VARCHAR(5)) + '-' + statisticlist.foreignid AS UniqueID, -- 3000 New Homes & Condos - 8 -- 3001 Property Rentals - 7 -- 3010 Resale - 57 CASE WHEN ad.category.exist('(//Categories/Category[@CategoryID=3001])') = 1 THEN '@ORIGIN_ID_RENTALS' WHEN ad.category.exist('(//Categories/Category[@CategoryID=3000])') = 1 THEN '@ORIGIN_ID_NHC' WHEN ad.category.exist('(//Categories/Category[@CategoryID=3010])') = 1 THEN '@ORIGIN_ID_RESALE' -- For old Categories WHEN ad.category.exist('(//Categories/Category[@CategoryID=7])') = 1 THEN '@ORIGIN_ID_RENTALS' WHEN ad.category.exist('(//Categories/Category[@CategoryID=8])') = 1 THEN '@ORIGIN_ID_NHC' WHEN ad.category.exist('(//Categories/Category[@CategoryID=57])') = 1 THEN '@ORIGIN_ID_RESALE' ELSE 9999 --09/17/2010 Michael - fix wrong data END AS OrigineID, COALESCE(company.foreignid, Substring( ad.contact.value('(Contacts/Contact[@Type="Main"]/Emails/Email[@Type="Main"]/@Value)[1]', 'varchar(50)'), 1, 50), 'Private') AS DealerID, CASE WHEN statisticlist.isshowroom = 1 THEN 1 ELSE 0 END AS ShowRoomID, NULL AS Make, NULL AS Model, NULL AS [Year], NULL AS KM, COALESCE(Round(ad.price.value('(Prices/Price[@Type="Rent"]/@Default)[1]', 'float'), 0), Round( ad.price.value('(Prices/Price[@Type="StartingPrice"]/@Default)[1]', 'float'), 0 ) ) AS Price, NULL AS StockNumber, statisticlist.statdate, statisticlist.statdate, statisticlist.views AS ListViews, statisticlist.clicks AS DetailViews, 0 AS SalesLeads, statisticlist.siteid, 1 AS Area, 'DSQLPR_RE' AS Server, 'adstat_realestate' AS DataTable, 1 AS AdID, 0 AS Status, -- Jan 20 2009 as per Mark's request, what's going to be returned in 'Description' will be the full address--dbo.regexp_replace( Coalesce( Ad.AdText.value('(//@Default)[1]', 'nvarchar(127)'), ''), '[\r\n]', '') As Description ad.location.value('(//Location/@Address)[1]', 'varchar(1000)') + ', ' + ad.location.value('(//Location/@City)[1]', 'varchar(500)') + ', ' + ad.location.value('(//Location/@Province)[1]', 'varchar(500)') AS Description, ad.contact.value('(//Contact[@Type="Agent"][@IndividualRank=1]/@IndividualForeignID)[1]', 'VARCHAR(100)') AS 'PrimaryAgentID', ad.contact.value('(//Contact[@Type="Agent"][@IndividualRank=2]/@IndividualForeignID)[1]', 'VARCHAR(100)') AS 'SecondaryAgentID', Isnull( company.relatedcompanies.value('(//Company[@Type="HeadOffice"]/@ForeignID)[1]', 'VARCHAR(50)'), company.foreignid) AS 'ParentCompanyId', ad.category.value('(//Categories/Category/@Default)[1]', 'varchar(1000)') AS 'Category', ad.location.value('(//Location/@Province)[1]', 'varchar(1000)') AS 'Province', ad.location.value('(//Location/@City)[1]', 'varchar(1000)') AS 'City' FROM statisticlist INNER JOIN dbo.ad WITH (readuncommitted) ON ad.sourceid = statisticlist.sourceid AND ad.foreignid = statisticlist.foreignid LEFT JOIN dbo.company WITH (readuncommitted) ON company.companyid = ad.companyid WHERE ad.category IS NOT NULL -- SiteStatsHoldingFinal --DELETE FROM -- dbo.SiteStatsHoldingFinal --WHERE -- OrigineID =9999 --09/17/2010 Michael - fix wrong data |
|
Improve speed in this query