processing - 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 2008 Forums

Transact-SQL (2008)

processing

Author

Topic

jeetu78
Starting Member

3 Posts

Posted - 2014-03-11 : 14:19:26

create table dbo.Procs
( ProcDate datetime , pid int )

create table dbo.rating
( agency varchar(200) ,
rating numeric ( 10, 2) ,
ratedate datetime )

create table summary ( agency varchar(20) ,
NowRating numeric(10, 2) ,
OldRating numeric(10, 2),
Change varchar( 5) ,
procStage int )

Day 1

insert into dbo.procs
select '02/01/2014', 1

insert into dbo.rating
select 'Haynes',10.1,'02/01/2014' union
select 'Moody',15.3,'02/01/2014' union
select 'Torrence',9.3,'02/01/2014' union
select 'Strike',20.0,'02/01/2014'

Summary
'Haynes',10.1,NULL,'New', 1
'Moody',15.3,NULL ,'New',1
'Torrence',9.3,NULL,'New',1
'Strike',20.0,NULL,'New',1

Day 2

insert into dbo.procs
select '03/01/2014', 2

insert into dbo.rating
select 'Haynes',15.0,'03/01/2014' union
select 'Moody',12.0,'03/01/2014' union
select 'Torrence',9.3,'03/01/2014' union
select 'Kings',2.5,'03/01/2014'

Summary should look like this.

'Haynes',15.0,10.1,'Upd', 2
'Moody',12.0,15.3,'Upd',2
'Torrence',9.3,9.3,'Upd',2
'Strike',NULL,20.0,'Del',2
'Kings',2.5,NULL,'new',2

Please help

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-11 : 18:03:43

If pid is serial,

DECLARE @PID int = 2;

WITH BASE AS (
SELECT
	*
FROM dbo.Rating R
INNER JOIN (SELECT * FROM dbo.Procs) P
	ON R.RateDate = P.ProcDate
)
SELECT
	LEFT(ISNULL(NEW.agency, OLD.agency), 20) agency,
	NEW.rating NewRating,
	OLD.rating OldRating,
	CASE
		WHEN NEW.rating IS NOT NULL AND OLD.rating IS NOT NULL THEN 'Upd'
		WHEN NEW.rating IS NOT NULL AND OLD.rating IS NULL THEN 'New'
		ELSE 'Del'
	END Change,
	@PID procStage
FROM (SELECT * FROM BASE WHERE pid = @PID) NEW
FULL JOIN (SELECT * FROM BASE WHERE pid = @PID - 1) OLD
	ON NEW.agency = OLD.agency

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources