Urgent help with T-SQL - good test!
-
Hello everyone,
I am working on a stored procedure which will be executed by SQL job on a scheduled basis. I have my solution but I am not at all satisfied with it.
This proc will update 2 particular columns in 2 tables based on a few conditions.Column 'chqValue' in table "changequestions" and column 'processed' in table "changes" will have to be updated for the following.
In table "changequestions", 'chqID' work in pairs. For every 'cgId' there will be a 'chqId' of 5. Along with 'chqId' 5 other 'chqId's' are available such as 403,400 etc.. Whenever the proc is executed, in table "changequestions" whereever the 'chqValue' is NULL for 'chqId' 403, 'chqValue' should be updated by the corresponding 'chqvalue' of 403 where the 'chqValue' value for chqId = 5 matches.
chqId = 5 will have the same 'chqValue' for a set. please observe the data in the tables.
When the 'chqvalue' is updated in "changequestions" table, in the root table "changes" for the 'chId' whose 'chType' is Add the 'processed' column should be updated to 0 if 1.
I know it looks complicated. I had a tough time to understand the requirement. Please feel free to ask questions.
--Tables
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[changes]') AND type in (N'U'))
DROP TABLE [dbo].[changes]
GO
CREATE TABLE [changes](
chId int not null,
chpId int not null,
chType varchar(50),
processed bit not null,
constraint [PK_changes] primary key clustered(chId ASC)
)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[changegroups]') AND type in (N'U'))
DROP TABLE [dbo].[changegroups]
GO
CREATE TABLE changegroups(
chId int not null,
cgId int not null,
constraint [PK_changegroups] primary key clustered(cgId ASC)
)
GO
ALTER TABLE [dbo].changegroups WITH CHECK ADD CONSTRAINT [FK_changegroups_changes] FOREIGN KEY([chId])
REFERENCES [dbo].[changes] ([chId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].changegroups CHECK CONSTRAINT [FK_changegroups_changes]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[changequestions]') AND type in (N'U'))
DROP TABLE [dbo].[changequestions]
GO
CREATE TABLE changequestions(
cgId int not null,
cqId int not null,
chqId int not null,
chqValue varchar(50) null,
chqOldValue varchar(50) null,
constraint [PK_changequestions] primary key clustered(cqId ASC)
)
GO
ALTER TABLE [dbo].[changequestions] WITH CHECK ADD CONSTRAINT [FK_changequestions_changegroups] FOREIGN KEY([cgId])
REFERENCES [dbo].[changegroups] ([cgId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[changequestions] CHECK CONSTRAINT [FK_changequestions_changegroups]
-- Data
INSERT INTO [changes]
VALUES (1,6600,'Add',1)
,(2,6600,'Update',0)
,(3,6600,'Add',1)
,(4,6600,'Update',0)
,(5,6600,'Add',0)
,(6,6600,'Update',0)
,(2785074,6600, 'Add',1)
,(3364200,6600,'Update',0)
INSERT INTO changegroups
VALUES (1,101)
,(1,102)
,(2,202)
,(2,203)
,(3,606)
,(3,607)
,(2785074,8291482)
,(2785074,8291483)
,(3364200,10018476)
,(3364200,10018480)
INSERT INTO changequestions
VALUES (101,10001,403,NULL,NULL)
,(101,10002,5,'1111','1111')
,(202,66666,400,'4444','3333')
,(202,66667,403,'updated',NULL)
,(202,66669,5,'1111',NULL)
,(606,9001,403,'aaaa','1a2b3c')
,(606,9002,5,'2222','2222')
,(606,9003,400,'a1a2',NULL)
,(606,9004,2901,'2901','2901')
,(8291482,108168449,403,NULL,NULL)
,(8291482,108168450,2903,'M',NULL)
,(8291482,108168451,5,'4352452','4352452')
,(10018476,130692492,403,'657169',NULL)
,(10018476,130692504,5,'4352452','4352452')
Expected results:
Table changes:
chId chpIdchTypeprocessed
1 6600 Add***0***
2 6600 Update 0
3 6600 Add 1
4 6600 Update 0
5 6600 Add 0
6 6600 Update 0
2785074 6600 Add ***0***
3364200 6600 Update 0
Table changequestions:
cgId cqId chqId chqValue chqOldValue
606 9001 403 aaaa 1a2b3c
606 9002 5 2222 2222
606 9003 400 a1a2 NULL
606 9004 2901 2901 2901
101 10001 403 ***updated*** NULL
101 10002 5 1111 1111
202 66666 400 4444 3333
202 66667 403 updated NULL
202 66669 5 1111 NULL
8291482 108168449 403 ***657169*** NULL
8291482 108168450 2903 M NULL
8291482 1081684515 4352452 4352452
10018476 130692492403 657169 NULL
10018476 1306925045 4352452 4352452
-
Not sure that I'm missing something, but I'm not seeing your solution to the problem that you say you don't like. I know I don't really want to code from scratch on this.
But really, I think you should take a step back and explain what it is this database is supposed to be doing. I think the problem may very well lie with the structure you have. Generally (and there are exceptions), massaging data through extra processes is indicative of poor relational design.
-
Hi
Please give requirement in a simple english or otherwise a screenshot would be a better choice to make visual relationship amongst tables. I'm not very sure that i understand correctly or not and not sure about your question indeed.
Thanks
Vinoth
-
Thanks for your replies...
Vinoth - In simple english the requirement is to update the chqValue column with NULL for chqId 403. The way it works is, a webservice sends a ADD record to our database initially with all the required data. But it so happened that chqValue is missed during that process for chqId of 403 which is essential. In order to correct it, webservice sends UPDATE record with correct value for chqId 403. 403 will have a pair value with chqId 5 which never has the NULL value for chqValue column and remains the same for both ADD and UPDATE records sent.
Below are the screenshots.
DB diagram of the tables
Actual Table Data
Expected table data
@BT - Database was designed with a Tree view in mind rather than relational modelling. Cant change the model now! My solution uses 7 temp tables to acheive the results. 7 temp tables is too bad right!
-
missed the screen caps with last reply. please find them attached with this reply
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply