Update SQL Field with Declared Where Clause Sum and inner join

Question

I am trying to make this select statement into an update statement that will take the 'Sum(PRSummaryMain.Revenue)' and update a the field in the Database 'ClientCusttabfields.Cust12MonthRev'

Everything I see to try fails?

declare @Now int, @12Month int, @lastMonth int, @day int
set @DAY = DATEPART(dd,getdate())
set @Now = LEFT(CONVERT(varchar, GetDate(),112),6)
set @12Month = iif(@day<20, @Now -102, @now-101)
set @lastmonth = iif(@day<20,@Now - 2,@now-1)

    select cl.Client, cl.ClientID, cl.Name, @12Month as 'Period-12months', @lastMonth as 'Period-lastmonth',
SUM(PRSummaryMain.Revenue) as '12monthrev'

From PRSummaryMain
inner join PR on (PRSummaryMain.WBS1 = PR.WBS1 and PRSummaryMain.WBS2=PR.WBS2 and PRSummaryMain.WBS3=PR.WBS3)

inner join CL on  (PR.ClientID = CL.ClientID) 
inner join ClientCustomTabFields on (cl.ClientID = ClientCustomTabFields.ClientID)

Where  PRSummaryMain.WBS1 not like 'P%'and PRSummaryMain.WBS1 not like '%i%' and PRSummaryMain.Period >=@12Month and PRSummaryMain.period <=@lastMonth and ClientCustomTabFields.CustStrategicClient like 'y'

group by cl.Client, CL.clientid, cl.Name
order by cl.Name

Can you add an example of what you tried, and how it failed? — Tab Alleman, Oct 10 '17 at 17:32
Possible duplicate of [How do I UPDATE from a SELECT in SQL Server?](https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server) — Tab Alleman, Oct 10 '17 at 17:33

score 0 · Answer 1 · answered Oct 10 '17 at 18:36

you can try the below query:

declare @Now int, @12Month int, @lastMonth int, @day int
set @DAY = DATEPART(dd,getdate())
set @Now = LEFT(CONVERT(varchar, GetDate(),112),6)
set @12Month = iif(@day<20, @Now -102, @now-101)
set @lastmonth = iif(@day<20,@Now - 2,@now-1)

select @Now , @12Month , @lastMonth , @day 

update c1
set Cust12MonthRev= SUM(PRSummaryMain.Revenue)
from
    ClientCustomTabFields c1 
inner join CL 
    on (cl.ClientID = c1.ClientID)
inner join PR 
    on  (PR.ClientID = CL.ClientID) 
inner join PRSummaryMain
    on (PRSummaryMain.WBS1 = PR.WBS1 and PRSummaryMain.WBS2=PR.WBS2 and PRSummaryMain.WBS3=PR.WBS3)
Where  
    PRSummaryMain.WBS1 not like 'P%'
    and PRSummaryMain.WBS1 not like '%i%' 
    and PRSummaryMain.Period >=@12Month 
    and PRSummaryMain.period <=@lastMonth 
    and c1.CustStrategicClient like 'y'
group by cl.Client, CL.clientid, cl.Name

Update SQL Field with Declared Where Clause Sum and inner join

1 Answers1