help with query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

help with query

Author

Topic

kowalsky
Starting Member

29 Posts

Posted - 2010-07-06 : 20:09:28

hi all,
I am trying to come up with a clever query that would display the following result based on the table below:

Table_name Change_Type # of occurences
TBL_USR UPDATE 2
TBL_USR INSERT 2
TBL_ACTIVITY INSERT 2
TBL_ACT_DEF UPDATE 1
TBL_ACT_DEF DELETE 2

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)

Thank you very much,
kowalsky

kowalsky

kowalsky
Starting Member

29 Posts

Posted - 2010-07-06 : 20:13:36

[code]
Table_name Change_Type # of occurences
TBL_USR UPDATE 2
TBL_USR INSERT 2
TBL_ACTIVITY INSERT 2
TBL_ACT_DEF UPDATE 1
TBL_ACT_DEF DELETE 2
[/code]

kowalsky

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-06 : 22:08:27

[code]
select CHANGE_ID, CHANGE_TYPE, count(*)
from TBL_CHANGE
group by CHANGE_ID, CHANGE_TYPE
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources