March 5, 2013 at 7:57 am
Is there a better way of doing this?
IF object_id('tempdb..#Temp1') IS NOT NULL
BEGIN
DROP TABLE #Temp1
END
CREATE TABLE #temp1
(
Source VARCHAR(5),
MPANcore BIGINT,
EffectiveFromDate DATETIME,
DatafileID bigint,
DataSiteConfigATableName VARCHAR(60)
)
Insert Into #temp1
Select Distinct Source, MPANCore,EffectiveFromSettlementDate, DataFileID, 'DataSiteConfigAMPANConfig' From DataSiteConfigAMPANConfig
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataAggregator' From DataSiteConfigADataAggregator
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataCollector' From DataSiteConfigADataCollector
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAEnergisationStatus' From DataSiteConfigAEnergisationStatus
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAGSPGroup' From DataSiteConfigAGSPGroup
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigALineLossfactorClass' From DataSiteConfigALineLossfactorClass
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeasurementClass' From DataSiteConfigAMeasurementClass
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromsettlementDate, DataFileID, 'DataSiteConfigAMeteringAddress' From DataSiteConfigAMeteringAddress
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterOperator' From DataSiteConfigAMeterOperator
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterTimeSwitchCode' From DataSiteConfigAMeterTimeSwitchCode
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAProfileClass' From DataSiteConfigAProfileClass
Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAStandardSettlementConfiguration' From DataSiteConfigAStandardSettlementConfiguration
-- 19 seconds
/*Need to use DataSettlementBData At this point as well*/
CREATE TABLE [dbo].[DataSiteConfigBMPANConfiguration](
[DataSiteConfigBMPANConfigurationID] [bigint] IDENTITY(1,1) NOT NULL,
[Source] [varchar](5) NULL,
[MPANCore] [bigint] NULL,
[DataSiteMPANID] [bigint] NULL,
[CreationTime] [datetime] NULL,
[EffectiveFromSettlementDate] [datetime] NULL,
[EffectiveToSettlementDate] [datetime] NULL,
[EnergisationStatus] [varchar](2) NULL,
[MeasurementClassRef] [varchar](2) NULL,
[ProfileClass] [varchar](2) NULL,
[StandardSettlementConfigurationRef] [int] NULL,
)
INSERT INTO dbo.DatasiteConfigBMPANConfiguration
(MpanCore, EffectiveFromSettlementDate)
SELECT DISTINCT MPANCore, EffectiveFromDate
FROM #temp1 WHERE EffectiveFromDate IS NOT NULL AND MPANcore IS NOT null
ORDER BY MPANCore, EffectiveFromDate ASC
----
--Create Unique set of data
IF object_id('tempdb..#Temp2') IS NOT NULL
BEGIN
DROP TABLE #Temp2
END
Select MPANCore, EffectiveFromDate, DataSiteConfigATableName, Max(DataFileID) as maxfileID
Into #temp2
from #temp1
Group by MPANCore, EffectiveFromDate, DataSiteConfigATableName
----
UPDATE dbo.DatasiteConfigBMPANConfiguration
SET EnergisationStatus = b.EnergisationStatus
FROM dbo.DatasiteConfigBMPANConfiguration
inner JOIN DataSiteConfigAEnergisationStatus b ON
b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore
AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDate
Inner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileID
Where c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus'
----
UPDATE dbo.DatasiteConfigBMPANConfiguration
SET MeasurementClassRef= b.measurementClassRef
FROM dbo.DatasiteConfigBMPANConfiguration
inner JOIN DataSiteConfigAMeasurementClass b
ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore
AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDate
Inner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileID
Where c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass'
UPDATE dbo.DatasiteConfigBMPANConfiguration
SET ProfileClass = b.ProfileClass
FROM dbo.DatasiteConfigBMPANConfiguration
INNER JOIN dbo.DataSiteConfigAProfileClass b ON
b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore
AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDate
Inner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileID
Where c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass'
any help is really appreciated.
Thanks
March 5, 2013 at 8:17 am
You could do this in a single update statement instead of 3.
UPDATE dbo.DatasiteConfigBMPANConfiguration
SET EnergisationStatus = case when c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus' then b.EnergisationStatus else DatasiteConfigBMPANConfiguration.EnergisationStatus end
, MeasurementClassRef = case when c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass' then b.measurementClassRef else DatasiteConfigBMPANConfiguration.MeasurementClassRef end
, ProfileClass = case when c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass' then b.ProfileClass else DatasiteConfigBMPANConfiguration.ProfileClass
FROM dbo.DatasiteConfigBMPANConfiguration
inner JOIN DataSiteConfigAEnergisationStatus b ON
b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore
AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDate
Inner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileID
Not sure this is better but it is a different approach. Depending on the size of the table this might be faster, but it could also be slower. You will have to test it out on your system and test it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2013 at 8:19 am
UPDATE d SET
EnergisationStatus = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus' THEN b.EnergisationStatus
ELSE EnergisationStatus END,
MeasurementClassRef = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass' THEN b.measurementClassRef
ELSE MeasurementClassRef END,
ProfileClass = CASE
WHEN c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass' THEN b.ProfileClass
ELSE ProfileClass END
FROM dbo.DatasiteConfigBMPANConfiguration d
INNER JOIN DataSiteConfigAEnergisationStatus b
ON b.MPANCore = d.MPANCore
AND b.EffectiveFromDate = d.EffectiveFromSettlementDate
INNER JOIN #Temp2 c
on b.MPANCore = c.MPANcore
and B.DataFileID = c.maxFileID
WHERE c.DataSiteConfigATableName IN
('DataSiteConfigAEnergisationStatus', 'DataSiteConfigAMeasurementClass', 'DataSiteConfigAProfileClass')
Edit: Oops - Sean beat me to it again.
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
March 5, 2013 at 8:21 am
Thanks for the update.
But joins in the 3 update statements are different.
Not sure of a way to bind them into one statement.
Deepthy
March 5, 2013 at 8:55 am
Deepthy (3/5/2013)
Thanks for the update.But joins in the 3 update statements are different.
Not sure of a way to bind them into one statement.
Deepthy
You got me.
Try this:
UPDATE dbo.DatasiteConfigBMPANConfiguration SET
EnergisationStatus = ISNULL(
SELECT b.EnergisationStatus
FROM DataSiteConfigAEnergisationStatus b
INNER JOIN #Temp2 c
ON b.MPANCore = c.MPANcore
AND B.DataFileID = c.maxFileID
AND c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus'
WHERE b.MPANCore = dbo.DatasiteConfigBMPANConfiguration.MPANCore
AND b.EffectiveFromDate = dbo.DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDate,
EnergisationStatus),
MeasurementClassRef = ISNULL(
SELECT b.measurementClassRef
FROM DataSiteConfigAMeasurementClass b
INNER JOIN #Temp2 c
ON b.MPANCore = c.MPANcore
AND B.DataFileID = c.maxFileID
AND c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass'
WHERE b.MPANCore = d.MPANCore
AND b.EffectiveFromDate = d.EffectiveFromSettlementDate,
MeasurementClassRef),
ProfileClass = ISNULL(
SELECT b.ProfileClass
FROM dbo.DataSiteConfigAProfileClass b
INNER JOIN #Temp2 c
ON b.MPANCore = c.MPANcore
AND B.DataFileID = c.maxFileID
AND c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass'
WHERE b.MPANCore = d.MPANCore
AND b.EffectiveFromDate = d.EffectiveFromSettlementDate,
ProfileClass)
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
March 5, 2013 at 8:59 am
Looks like Chris beat me this time. 😀
It was hard to see that the tables were different because the formatting was non-existent and the tables all have crazy long names that all start with the same 15 or characters. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2013 at 9:00 am
Chris,
Thanks very much.
Deepthy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply