automate procedure for row wise comparison

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

automate procedure for row wise comparison

Author

Topic

satishk
Starting Member

39 Posts

Posted - 2007-09-14 : 02:24:52

DECLARE sitecode_cursor CURSOR FOR
SELECT distinct userid from gen..site where len(userid)=8 ORDER BY userid
OPEN sitecode_cursor
FETCH NEXT FROM sitecode_cursor INTO @sitecode
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table1
select * from table2 where sitecode=@sitecode
insert into table3
select * from table4 where sitecode=@sitecode
select *,(Gen_Electricity_Usage-Gmers_Electricity_Usage)as Diff from table1,table3
where table1.sitecode=table3.sitecode and (Gen_Electricity_Usage-Gmers_Electricity_Usage) <>0
and table1.year=table3.year and table1.month=table3.month
end

suppose I have a table1 with records as below

sitecode year month utilityusage
1001 1991 2 10
1001 1991 3 12
1002 1993 4 15
1003 1993 7 7
1004 1993 8 10
suppose I have a table3 with records as below

sitecode year month utilityusage
1001 1991 2 15
1001 1991 3 12
1002 1993 4 15
1003 1993 7 7
1004 1993 8 15
I want to automate the procedure of passing the sitecode,year,month coulmn to another stored for updating purpose in case
difference of utilityusage coulmn among both tables (table1,table2)is not equal to 0 for same site.Or in other words in case utility usage column does not match for same sitecode,year,month then we have to pass the sitecode,year,month to another stored procedure that takes these as parameters and then updates.
The reason why I am inserting into table1 is bcoz in actual scenario I want to get desired coulmn results from more then one table which are joined
Same reason applies for using table3 inserting into table1 is bcoz in actual scenario I want to get desired coulmn results from more then one table which are joined

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-14 : 12:43:55

Maybe the "other" joins are giving you grief, but You sould be able to directly update the data. Here is a sample for doing a direct update and looping.

DECLARE @Table1 TABLE(sitecode int, year int, month int, utilityusage int)
DECLARE @Table2 TABLE (sitecode int, year int, month int, utilityusage int)
DECLARE @Table3 TABLE (id int IDENTITY(1,1), sitecode int, year int, month int, utilityusage int)

INSERT @Table1
SELECT 1001, 1991, 2, 10
UNION ALL SELECT 1001, 1991, 3, 12
UNION ALL SELECT 1002, 1993, 4, 15
UNION ALL SELECT 1003, 1993, 7, 7 
UNION ALL SELECT 1004, 1993, 8, 10


INSERT @Table2
SELECT 1001, 1991, 2, 15
UNION ALL SELECT 1001, 1991, 3, 12
UNION ALL SELECT 1002, 1993, 4, 15
UNION ALL SELECT 1003, 1993, 7, 7
UNION ALL SELECT 1004, 1993, 8, 15 

---- Direct Update
UPDATE
	T1
SET
	T1.utilityusage = T2.utilityusage
FROM
	@Table1 AS T1
INNER JOIN
	@Table2 AS T2
	ON T1.sitecode = T2.sitecode
	AND T1.year = T2.year
	AND T1.month = T2.month
WHERE
	T1.utilityusage <> T2.utilityusage


-- Looping Method 

-- Load values that are different
INSERT 
	@Table3
SELECT 
	T2.*
FROM
	@Table1 AS T1
INNER JOIN
	@Table2 AS T2
	ON T1.sitecode = T2.sitecode
	AND T1.year = T2.year
	AND T1.month = T2.month
WHERE
	T1.utilityusage <> T2.utilityusage

DECLARE @ID INT
DECLARE @SiteCode INT
DECLARE @Year INT
DECLARE @Month INT

-- Get the first item to work on
SELECT 
	@ID = MIN(id)
FROM 
	@Table3

WHILE @ID IS NOT NULL
BEGIN
	-- Get the data for calling teh stored procedure
	SELECT 
		@SiteCode = sitecode,
		@Year = year,
		@Month = month
	FROM 
		@Table3
	WHERE
		id = @ID

	-- Call the stored procedure to do the work.
	-- EXEC MyStoredProcedure (Params go here)

	-- Remove the item just processed.
	DELETE @Table3 WHERE ID = @ID

	-- Get next item
	SELECT 
		@ID = MIN(id)
	FROM 
		@Table3
END

EDIT: Added some more comments

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources