please help with query

Question

please help with query

axes2000

SSC-Addicted

Points: 433
More actions
July 6, 2010 at 6:17 pm

#224553

hi all,
I am trying to come up with a clever query that would display the following result based on the table below:
Table_nameChange_Type# of occurences
TBL_USRUPDATE2
TBL_USRINSERT2
TBL_ACTIVITYINSERT2
TBL_ACT_DEFUPDATE1
TBL_ACT_DEFDELETE2
My TBL_CHANGE is defined as:
CREATE TABLE [dbo].[TBL_CHANGE](
[ID] [int] NOT NULL,
[CHANGE_ID] [int] NOT NULL,
[TBL_NAME] [varchar](50) NULL,
[PK_NAME] [varchar](50) NULL,
[PK_VALUE] [int] NULL,
[COL_NAME] [varchar](50) NULL,
[CHANGE_TYPE] [varchar](50) NULL,
[OLDVAL] [varchar](50) NULL,
[NEWVAL] [varchar](50) NULL,
[IDATE] [datetime] NULL
) ON [PRIMARY]
and holds the following data:
INSERT INTO TBL_CHANGE() VALUES (1,1000,'TBL_USR','ID',500,'LNAME','UPDATE','Paisley','Paisley-Jacobs','2010-01-03 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (2,1001,'TBL_USR','ID',700,'FNAME','UPDATE','Bob','Robert','2010-01-20 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (3,1002,'TBL_USR','ID',300,'LNAME','INSERT',NULL,'Zellerweg','2010-02-11 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (4,1002,'TBL_USR','ID',300,'FNAME','INSERT',NULL,'Siegfried','2010-02-11 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (5,1002,'TBL_USR','ID',300,'BDATE','INSERT',NULL,'1980-04-23 00:00:00.000','2010-02-11 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (6,1003,'TBL_USR','ID',400,'LNAME','INSERT',NULL,'Thornton','2010-01-20 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (7,1003,'TBL_USR','ID',400,'FNAME','INSERT',NULL,'Dwight','2010-01-20 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (8,1003,'TBL_USR','ID',400,'BDATE','INSERT',NULL,'1984-12-05 00:00:00.000','2010-01-20 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (9,1004,'TBL_ACTIVITY','ID',5,'USR_ID','INSERT',NULL,'800','2010-03-21 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (10,1004,'TBL_ACTIVITY','ID',5,'ACTIVITY_CODE','INSERT',NULL,'12','2010-03-21 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (11,1004,'TBL_ACTIVITY','ID',5,'ACTIVITY_HRS','INSERT',NULL,'6','2010-03-21 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (12,1005,'TBL_ACTIVITY','ID',6,'USR_ID','INSERT',NULL,'800','2010-03-22 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (13,1005,'TBL_ACTIVITY','ID',6,'ACTIVITY_CODE','INSERT',NULL,'10','2010-03-22 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (14,1005,'TBL_ACTIVITY','ID',6,'ACTIVITY_HRS','INSERT',NULL,'6','2010-03-22 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (15,1006,'TBL_ACT_DEF','ID',4,'ACTIVITY_NAME','UPDATE','OBSERVE','WATCH','2010-01-12 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (16,1007,'TBL_ACT_DEF','ID',5,'ACTIVITY_CODE','DELETE','15',NULL,'2010-01-16 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (17,1007,'TBL_ACT_DEF','ID',5,'ACTIVITY_NAME','DELETE','RUNNING',NULL,'2010-01-16 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (18,1008,'TBL_ACT_DEF','ID',7,'ACTIVITY_CODE','DELETE','18',NULL,'2010-01-16 00:00:00.000')
INSERT INTO TBL_CHANGE() VALUES (19,1008,'TBL_ACT_DEF','ID',7,'ACTIVITY_NAME','DELETE','SWIMMING',NULL,'2010-01-16 00:00:00.000')
The data above is appended to the TBL_CHANGE table from 3 other tables as follows:
1. if there is an update in any of the tables there is going to be one row entered into TBL_CHANGE (the values entered are self explanatory)
2. if there is a row insert into any of the tables there are going to be as many rows inserted into TBL_CHANGE as many columns are in the table where the insert has been made minus 1 (not counting the ID column)
3. if there is a row deleted from any of the tables the are going to be as many rows inserted into TBL_CHANGE as many columns are in the table where the delete has been made minus 1 (not counting the ID column)
Thanks a bunch,
kowalsky

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what you want to do is create a VIEW that gathers this information on demand, instead of trying to update a static table with the calculations

try this working snippet:

CREATE TABLE [dbo].[TBL_CHANGE](

[ID] [int] NOT NULL,

[CHANGE_ID] [int] NOT NULL,

[TBL_NAME] [varchar](50) NULL,

[PK_NAME] [varchar](50) NULL,

[PK_VALUE] [int] NULL,

[COL_NAME] [varchar](50) NULL,

[CHANGE_TYPE] [varchar](50) NULL,

[OLDVAL] [varchar](50) NULL,

[NEWVAL] [varchar](50) NULL,

[IDATE] [datetime] NULL

) ON [PRIMARY]

INSERT INTO TBL_CHANGE VALUES (1,1000,'TBL_USR','ID',500,'LNAME','UPDATE','Paisley','Paisley-Jacobs','2010-01-03 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (2,1001,'TBL_USR','ID',700,'FNAME','UPDATE','Bob','Robert','2010-01-20 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (3,1002,'TBL_USR','ID',300,'LNAME','INSERT',NULL,'Zellerweg','2010-02-11 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (4,1002,'TBL_USR','ID',300,'FNAME','INSERT',NULL,'Siegfried','2010-02-11 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (5,1002,'TBL_USR','ID',300,'BDATE','INSERT',NULL,'1980-04-23 00:00:00.000','2010-02-11 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (6,1003,'TBL_USR','ID',400,'LNAME','INSERT',NULL,'Thornton','2010-01-20 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (7,1003,'TBL_USR','ID',400,'FNAME','INSERT',NULL,'Dwight','2010-01-20 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (8,1003,'TBL_USR','ID',400,'BDATE','INSERT',NULL,'1984-12-05 00:00:00.000','2010-01-20 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (9,1004,'TBL_ACTIVITY','ID',5,'USR_ID','INSERT',NULL,'800','2010-03-21 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (10,1004,'TBL_ACTIVITY','ID',5,'ACTIVITY_CODE','INSERT',NULL,'12','2010-03-21 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (11,1004,'TBL_ACTIVITY','ID',5,'ACTIVITY_HRS','INSERT',NULL,'6','2010-03-21 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (12,1005,'TBL_ACTIVITY','ID',6,'USR_ID','INSERT',NULL,'800','2010-03-22 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (13,1005,'TBL_ACTIVITY','ID',6,'ACTIVITY_CODE','INSERT',NULL,'10','2010-03-22 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (14,1005,'TBL_ACTIVITY','ID',6,'ACTIVITY_HRS','INSERT',NULL,'6','2010-03-22 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (15,1006,'TBL_ACT_DEF','ID',4,'ACTIVITY_NAME','UPDATE','OBSERVE','WATCH','2010-01-12 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (16,1007,'TBL_ACT_DEF','ID',5,'ACTIVITY_CODE','DELETE','15',NULL,'2010-01-16 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (17,1007,'TBL_ACT_DEF','ID',5,'ACTIVITY_NAME','DELETE','RUNNING',NULL,'2010-01-16 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (18,1008,'TBL_ACT_DEF','ID',7,'ACTIVITY_CODE','DELETE','18',NULL,'2010-01-16 00:00:00.000')

INSERT INTO TBL_CHANGE VALUES (19,1008,'TBL_ACT_DEF','ID',7,'ACTIVITY_NAME','DELETE','SWIMMING',NULL,'2010-01-16 00:00:00.000')

GO

CREATE VIEW vw_MyTotals AS

select [TBL_NAME], Change_Type, count(*) AS Num_Of_Occurrences

from TBL_CHANGE

group by [TBL_NAME], Change_Type

order by [TBL_NAME],Change_Type

GO

SELECT * FROM vw_MyTotals

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

axes2000 SSC-Addicted Points: 433 More actions · Answer 2

axes2000

SSC-Addicted

Points: 433

July 7, 2010 at 12:25 pm

#1189801

Thanks a bunch,

kowalsky