Can't update using aggregates - 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

General SQL Server Forums

New to SQL Server Programming

Can't update using aggregates

Author

Topic

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-07-27 : 14:47:40

Hi there, I'm having trouble doing an update. Let's say I have:


location    client       insp_date     insp_date_end
----------------------------------------------------
   1           7         11/17/2010     12/31/9999
   1           7         5/4/2011       12/31/9999
   2           7         12/16/2010     12/31/9999
   3           8         8/25/2010      12/31/9999
   3           8         2/1/2011       12/31/9999
   3           8         2/7/2011       12/31/9999

And what I'm trying to do get is:


location    client       insp_date     insp_date_end
----------------------------------------------------
   1           7         11/17/2010     5/4/2011
   1           7         5/4/2011       12/31/9999
   2           7         12/16/2010     12/31/9999
   3           8         8/25/2010      2/1/2011
   3           8         2/1/2011       2/7/2011
   3           8         2/7/2011       12/31/9999

I'm able to view the results I want (the ones I want to change, that is) with this:


SELECT
	i1.location
	, i1.client
	, i1.insp_date
	, [insp_date_end] = MIN(i2.insp_date)
--UPDATE i1 SET
--      [insp_date_end] = MIN(i2.insp_date)      --doesn't work!
FROM #Inspections i1
	INNER JOIN #Inspections i2
		ON i1.location = i2.location
		AND i1.client = i2.client
		AND i1.insp_date < i2.insp_date
GROUP BY
	i1.location
	, i1.client
	, i1.insp_date

I should mention that I'm not a programmer, I didn't set up the database, and I have limited permissions to change anything.

Thanks for your help!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-27 : 15:00:11

[code]
Create Table #Inspections (
location int,
client int,
insp_date datetime,
insp_date_end datetime
)

Insert Into #Inspections Select 1, 7, '11/17/2010', '12/31/9999'
Insert Into #Inspections Select 1, 7, '05/04/2011', '12/31/9999'
Insert Into #Inspections Select 2, 7, '12/16/2010', '12/31/9999'
Insert Into #Inspections Select 3, 8, '08/25/2010', '12/31/9999'
Insert Into #Inspections Select 3, 8, '02/01/2011', '12/31/9999'
Insert Into #Inspections Select 3, 8, '02/07/2011', '12/31/9999'

Select * From #Inspections

;with i as (
Select
*,
r = ROW_NUMBER() Over(Partition By Location, client Order By insp_date)
From #Inspections
)

Update a
Set insp_date_end = b.insp_date
From i a
Inner Join i b
On a.location = b.location
and a.client = b.client
and a.r = b.r-1

Select * From #Inspections

Drop Table #Inspections
[/code]

EDIT: b.r+1 should have been b.r-1

Corey