Updating aggrigate field from temp table

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

Updating aggrigate field from temp table

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-09-03 : 10:07:27

I have a script that takes the the first and second max dates from a table, creates a temp table and updates the first max date (in the temp table) with the second max date plus one year if it is less than a year apart. All is working well. When I run the sc ript below I get the following returned:

ID MaxDate SecondMaxDate
51 2010-09-02 NULL
47 2010-09-02 NULL
36 2011-09-30 2010-09-22
13 2011-12-31 2010-12-31
35 2012-08-31 2011-08-30

DECLARE @compareDates TABLE
(
ID TINYINT,
Thru_DATE DATETIME,
)

INSERT @compareDates
select .ID, THRU_DATE
FROM A
WHERE A_TYPE = 'SUB'
GROUP BY A.ID,THRU_DATE

SELECT ID,
MAX(CASE WHEN recID = 1 THEN [Thru_DATE] ELSE NULL END) AS MaxDate,
MAX(CASE WHEN recID = 2 THEN [Thru_DATE] ELSE NULL END) AS SecondMaxDate,

Into #Temp
FROM (
SELECT ID,
[Thru_DATE],
(SELECT COUNT(*) FROM @compareDates WHERE ID = s1.ID AND [Thru_DATE] >= s1.[Thru_DATE]) AS recID,
FROM @compareDates as s1
) AS d
WHERE recID <= 2

GROUP BY ID

UPDATE T
SET T.MaxDate = DATEADD(D, - 1, DATEADD(mm, DATEDIFF(mm, 0, T.SecondMaxDate) + 13, 0))
from #Temp T
where DateDiff(DD,T.MaxDate, T.SecondMaxDate) * -1 < 365

Here is where I cannot figure out the syntax. I need to take the updated T.MaxDate and update the Max A date. how can I accomplish this?

To get the max A date from the db I use the following:

select max (a.thru_date)as MaxADate, id
from a
where A_TYPE = 'SUB'
group by id

I tried to add the following to my script right after the temp table was created, but keep getting syntax errors:

UPDATE A
SET (select max (a.thru_date)as MaxADate, id
from a
where A_TYPE = 'SUB'
group by id)
= T.SecondMaxDate
group by a.id) = T.MaxDate
from #Temp T
group by aa.id
where DateDiff(DD,T.MaxDate, T.SecondMaxDate) * -1 < 365

Thanks!
dz

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-03 : 12:09:07

Perhaps some sample data and expeceted output would be in order here. Here is a link about how to prepare such data:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

After we have that we can probalby help you better, but here is a guess for fun:

UPDATE 
	Target
SET
	thru_date = T.SecondMaxDate
FROM
	A AS Target 
INNER JOIN
	#Temp AS T
	ON A.ID = T.ID
	AND A.thru_date = T.MaxDate
WHERE 
	A.A_TYPE = 'SUB'

dzabor
Posting Yak Master

138 Posts

Posted - 2010-09-03 : 12:16:40

I need to update the Max(a.thru_date) where type = 'sub'. this is where I am having trouble with the syntax.

dzabor
Posting Yak Master

138 Posts

Posted - 2010-09-03 : 13:10:49

This is what I have so far:

UPDATE A
SET select max (a.thru_date)
from a
where A_TYPE = 'SUB'
group by id = T.SecondMaxDate
from #Temp T
group by a.id
where DateDiff(DD,T.MaxDate, T.SecondMaxDate) * -1 < 365

Shole I be looking for a diffeten way to do this? I thought it would be easy to update a field from a temp table, but the aggregate is throwing me.

Thanks,
dz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources