March 6, 2008 at 3:37 pm
I have the following table and have included some sample data. Basically, the records are grouped by opid and it shows ownership changes for the opids. It only shows the owner change date which is the date that the owner was assigned to the opid. What I want to figure out is the date that the owner ceased to be assigned to the opid as well. If it is still the current owner then the end date should just be the current date.
CREATE TABLE #op_owner_history(
[opshid] [int] IDENTITY(1,1) NOT NULL,
[opid] [int] NOT NULL,
[role] [varchar](30) NOT NULL,
[rep] [char](20) NOT NULL,
[percentage] [int] NOT NULL,
[ownerchg_date] [datetime] NOT NULL,
[ownerchg_rep] [char](20) NOT NULL,
CONSTRAINT [PK_#op_oppshare_history] PRIMARY KEY CLUSTERED
(
[opshid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT #op_owner_history ON
GO
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4653, 23795, 'Sales','KOHLENBERG', 100, '9/26/2006 3:57:22 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8576, 23795, 'Sales', 'Haber', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (9363, 23795, 'Sales', 'KOHLENBERG', 100, '1/4/2007 12:40:14 PM', 'Kohlenberg')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (2919, 24623, 'Sales', 'COLEMAN', 100, '9/1/2006 12:02:03 PM', 'Dunkel')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (15475, 24623, 'Sales', 'COLEMAN', 100, '5/8/2007 8:10:51 AM', 'Woodruff')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (8580, 24623, 'Sales', 'Woodruff', 100, '1/3/2007 10:40:00 AM', 'ADMIN')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (4310, 5548, 'Sales', 'KOHLENBERG', 100, '9/19/2006 1:53:47 PM', 'KOHLENBERG')
INSERT into #op_owner_history (opshid,opid, role,rep,percentage,ownerchg_date,ownerchg_rep) VALUES (10649, 5548, 'Sales', 'HABER', 100, '2/7/2007 3:50:40 PM', 'Kohlenberg')
Based on this sample data, here is what I want to see in the results:
opshidopidrolereppercentageownerchg_dateEnd Dateownerchg_rep
43105548SalesKOHLENBERG 1009/19/2006 1:53:47 PM2/7/2007 3:50:40 PMKOHLENBERG
106495548SalesHABER 1002/7/2007 3:50:40 PMGETDATE()Kohlenberg
465323795SalesKOHLENBERG 1009/26/2006 3:57:22 PM1/3/2007 10:40:00 AMKohlenberg
857623795SalesHaber 1001/3/2007 10:40:00 AM1/4/2007 12:40:14 PMADMIN
936323795SalesKOHLENBERG 1001/4/2007 12:40:14 PMGETDATE()Kohlenberg
291924623SalesCOLEMAN 1009/1/2006 12:02:03 PM1/3/2007 10:40:00 AMDunkel
858024623SalesWoodruff 1001/3/2007 10:40:00 AM5/8/2007 8:10:51 AMADMIN
1547524623SalesCOLEMAN 1005/8/2007 8:10:51 AMGETDATE()Woodruff
March 6, 2008 at 6:36 pm
here's an old school alternative with another temp table:
select row_number() over (order by opid asc, ownerchg_date asc) as seq, *
into #op_hist
from #op_owner_history
select C.*, isnull(N.ownerchg_date,getdate()) as end_date
from #op_hist as C left outer join #op_hist as N
on N.opid = C.opid and N.seq = C.seq + 1
and the newer way with a common table expression:
with OwnerHist
( seq, opshid, opid, [role], rep, percentage, [ownerchg_date], [ownerchg_rep] )
as
( select row_number() over (order by opid asc, ownerchg_date asc) as seq, *
from #op_owner_history )
select C.*, isnull(N.ownerchg_date,getdate()) as end_date
from OwnerHist as C left outer join OwnerHist as N
on N.opid = C.opid and N.seq = C.seq + 1
March 10, 2008 at 8:19 am
Thanks. This did the trick.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply