April 3, 2008 at 8:27 am
I need to be able to UPdate a temp table based on the the consultant having and DeactivationDate between a certain DateRange and and being Inactive (Active=0) and SUM(PurchaseAMount) over 1000 (this comes from the volume table). THis blows up on the Select statement in the WHERE clause. ANy ideas?
Update #Temp6
SET points = points + 1
FROM #Temp6 t
LEFT OUTER JOIN volume v ON t.ConsultantID = v.ConsultantID
where t.DeactivationDate Between @StartDate and @EndDate
and t.active = 0 --AND (Select SUM(v1.PurchaseAmount) From Volume v1
--WHERE v1.ConsultantID = t.ConsultantID
--GROUP BY t1.ConsultantID
--HAVING Sum(v1.PurchaseAmount) > 1000)
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 3, 2008 at 8:37 am
Your aggregate calculation will need to be a separate sub-query. The aggregation operation itself can't be visible at the top level, or the data set will not be updateable and will start giving you complaints.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 8:40 am
Can you give me an example? That sort of confused me.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
April 3, 2008 at 8:45 am
meaning....
update tableA
Set colA=Summary.Total
from TableA
inner join (select grpID, sum(amount) Total from TableB group by Grpid) Summary
where
TableA.ID=Summary.GrpID
and Summary.Total>400
As in - do the sub-total in a sub-query, then do the update in an outer query.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 8:49 am
Does this do what you need?
;with CTE (ConsultantID) as
(select consultantid
from volume
where deactivationdate between @startdate and @enddate
and active = 0
group by consultantid
having sum(purchaseamount) > 1000)
update #Temp6
set points = points + 1
grom cte
where #temp6.consultantid = cte.consultantid
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 8:50 am
Try this:
UPDATE t
SET points = points + 1
FROM #Temp6 t
INNER JOIN (SELECT ConsultantId
FROM volume
GROUP BY ConsultantId
HAVING SUM(PurchaseAmount) > 1000) v
ON t.ConsultantId = v.ConsultantId
WHERE DeactivationDate BETWEEN @StartDate AND @EndDate
AND Active = 0
Dave Novak
April 3, 2008 at 12:29 pm
That worked great. The final code was:
UPDATE t
SET points = points + 1
FROM #Temp6 t
INNER JOIN (SELECT ConsultantId
FROM volume
GROUP BY ConsultantId
HAVING SUM(PurchaseAmount) >= 1000) v
ON t.ConsultantId = v.ConsultantId
WHERE DeactivationDate BETWEEN @StartDate AND @EndDate
AND Active = 0
Thanks to everybody for there input.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply