Please Help with the 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 2008 Forums

Transact-SQL (2008)

Please Help with the Query.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-06 : 18:45:47

[code]Hi,
I need to update the second record take it from the first record.
Below is the business rule and desire output. SQL 2005

Thank you so much in advance.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsurInfo]') AND type in (N'U'))
DROP TABLE [dbo].[InsurInfo]
GO

CREATE TABLE [dbo].[InsurInfo](
[NewValidFrom] [datetime] NULL,
[IDPatInsur] [int] NULL,
[PID] [int] NULL,
[ValidFrom] [datetime] NULL,
[ValidTo] [datetime] NULL,
[GenInsurType] [varchar](9) NOT NULL,
[Inactive] [bit] NULL,
[rn] [bigint] NULL
);
go

INSERT INTO [dbo].[InsurInfo]([NewValidFrom], [IDPatInsur], [PID], [ValidFrom], [ValidTo], [GenInsurType], [Inactive], [rn])
SELECT '20100331 00:00:00.000', 1000, 100, '20100401 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
SELECT '20100215 00:00:00.000', 1003, 100, '20100216 00:00:00.000', NULL, N'Primary', 1, 2 UNION ALL
SELECT '20100930 00:00:00.000', 3106, 106, '20101001 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
SELECT '20080731 00:00:00.000', 1011, 106, '20080801 00:00:00.000', NULL, N'Primary', 1, 2 UNION ALL
SELECT '20101231 00:00:00.000', 3329, 144, '20110101 00:00:00.000', NULL, N'Primary', 0, 1 UNION ALL
SELECT '20090930 00:00:00.000', 2100, 144, '20091001 00:00:00.000', NULL, N'Primary', 1, 2
GO

SELECT *
FROM InsurInfo
GO

Business rules: Update the NewValidFrom field from the first record where Inactive = 0 update
to the second one and it must be within the same PID.

-- Result want:
NewValidFrom IDPatInsur PID ValidFrom ValidTo GenInsurType Inactive rn
----------------------- ----------- ----------- ----------------------- ----------------------- ------------ -------- --
2010-03-31 00:00:00.000 1000 100 2010-04-01 00:00:00.000 NULL Primary 0 1
2010-03-31 1003 100 2010-02-16 00:00:00.000 NULL Primary 1 2

2010-09-30 00:00:00.000 3106 106 2010-10-01 00:00:00.000 NULL Primary 0 1
2010-09-30 1011 106 2008-08-01 00:00:00.000 NULL Primary 1 2

2010-12-31 00:00:00.000 3329 144 2011-01-01 00:00:00.000 NULL Primary 0 1
2010-12-31 2100 144 2009-10-01 00:00:00.000 NULL Primary 1 2 [/code]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-06 : 19:05:36

I'm pretty sure that I have your requirements completely fouled up but...[CODE]update second
from dbo.InsurInfo first
set NewValidDate = first.NewValidDate
inner join
dbo.InsurInfo second
on first.PID = second=PID
and first.rn = 1
and second.rn = 2
and first.Inactive = 0[/CODE]If you'll try to clarify where I'm missing the boat, I'll try to get to the pier sooner...

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:42:52

isnt this same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166277

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-07 : 11:58:03

It's little bit different because I can't get it to work on one query.
Thanks so much for your help before.

Have a great weekend.

quote:
Originally posted by visakh16

isnt this same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166277

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources