Update 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

General SQL Server Forums

New to SQL Server Programming

Update Query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-24 : 06:21:56

Hey guys

I need some help
And not sure of the best way to go around this .

Aim – Update the DCC column from table #build2 with the values from column DCC from table Omnipaydcc. the inner join would be on the FDMSaccountno on both tables

select
#final.FDMSAccountNo,
#final.Account_Status,
#final.Amex,
#final.Omnipay,
CASE
WHEN Clientline IS NULL THEN 'No' WHEN Clientline is not null THEN 'Yes' ELSE 'Check'END as Clientline,

case
when DCC IS NULL THEN 'No R12 Financials' when dcc = 'No DCC Opportunity' then 'No DCC Opportunity' else 'DCC Opportunity' end as DCC,

case
when mca IS NULL THEN 'Potential Opportunity' when mca = 'FD' then 'FD MCA' else 'MCA - External party' end as MCA,

CASE
WHEN VT IS NULL THEN 'No' WHEN VT is not null THEN 'Yes' ELSE 'Check'END as VT
into #build2
from #clientline right join #final on #final.FDMSAccountNo = #clientline.FDMSAccountNo
--where #final.FDMSAccountNo = '878756020888'
order by #final.MCA Desc

-- MCA update --
SELECT
FDMSAccountNo,
SUM(Fact_Financial_History_2.hst_sales_amt_R12) / 12 AS SalesAvg,
CASE WHEN SUM([hst_sales_amt_R12]) / 12 IS NULL THEN 'No R12 Financials'
WHEN SUM([hst_sales_amt_R12]) / 12 BETWEEN 0 AND 999.99 THEN 'No MCA Opportunity'
WHEN SUM([hst_sales_amt_R12]) / 12 >= 1000 THEN 'Potential Opportunity' ELSE 'Check' END AS MCA
into #mca2
FROM Fact_Financial_History_2
full outer join Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
GROUP BY Dim_Outlet.FDMSAccountNo

UPDATE b
SET b.MCA = w.MCA
FROM #Build2 AS b
INNER JOIN #Mca2 AS w ON w.FDMSAccountNo = b.FDMSAccountNo
WHERE b.MCA = 'Potential Opportunity'

select * from #build2 b2

--Calculating Dcc Opps On omnipay --
SELECT
Dim_Outlet.FDMSAccountNo,
SUM(stg_Fact_Omnipay_Profitability.Tot_Purch_Amt) AS Gross,
SUM(stg_Fact_Omnipay_Profitability.Tot_Purch_Amt) / 12 AS GrossAvg,
SUM(stg_Fact_Omnipay_Profitability.DCC_Purch_Amt) AS Dcc,
SUM(stg_Fact_Omnipay_Profitability.DCC_Purch_Amt) / 12 AS DCCAvg,
sum([Merch_Commiss]) as Commision,
case
when SUM(stg_Fact_Omnipay_Profitability.DCC_Purch_Amt) <0.1 then 'DCC Opp - Not processing Trans'
when SUM(stg_Fact_Omnipay_Profitability.DCC_Purch_Amt) > 0.1 then 'Processing Dcc Transactions' else 'Check' end as [DCC]
into #Omnipaydcc
FROM stg_Fact_Omnipay_Profitability INNER JOIN
Dim_Outlet ON stg_Fact_Omnipay_Profitability.Our_Reference = Dim_Outlet.Omnipay_Account
WHERE (CONVERT(datetime, Period, 103) BETWEEN @RFrom AND @Rto)
GROUP BY Dim_Outlet.FDMSAccountNo
order by new desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 06:24:09

Did you try the suggestion I posted here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187004

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-24 : 06:26:46

I have indeed, that works for one of the updates i need, but this is for another update .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 06:29:05

Can't you use the same technique displayed in the posted suggestion?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-07-24 : 06:36:13

Hi Swepeso

is it as simple as
UPDATE b
SET b.DCC = o.Dcc
FROM #Build2 AS b
INNER JOIN #Omnipaydcc AS o ON o.FDMSAccountNo = b.FDMSAccountNo

? Would that mean any value within column DCC on omnipaydcc table will update column DCC in the build2 table ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-24 : 06:38:23

[code]SELECT f.FDMSAccountNo,
f.Account_Status,
f.Amex,
f.Omnipay,
CASE
WHEN cl.Clientline IS NULL THEN 'No'
WHEN cl.Clientline IS NOT NULL THEN 'Yes'
ELSE 'Check'
END AS Clientline,
CASE
WHEN cl.DCC IS NULL THEN 'No R12 Financials'
WHEN cl.DCC = 'No DCC Opportunity' THEN 'No DCC Opportunity'
ELSE 'DCC Opportunity'
END AS DCC,
CASE
WHEN cl.MCA IS NULL THEN 'Potential Opportunity'
WHEN cl.MCA = 'FD' THEN 'FD MCA'
ELSE 'MCA - External party'
END AS MCA,
CASE
WHEN cl.VT IS NULL THEN 'No'
WHEN cl.VT IS NOT NULL THEN 'Yes'
ELSE 'Check'
END AS VT
INTO #Build2
FROM #Final AS f
LEFT JOIN #ClientLine AS cl on cl.FDMSAccountNo = f.FDMSAccountNo;

-- MCA update --
SELECT o.FDMSAccountNo,
SUM(fh.hst_sales_amt_R12) / 12E AS SalesAvg,
CASE
WHEN SUM(fh.hst_sales_amt_R12) IS NULL THEN 'No R12 Financials'
WHEN SUM(fh.hst_sales_amt_R12) / 12E BETWEEN 0 AND 999.99 THEN 'No MCA Opportunity'
WHEN SUM(fh.hst_sales_amt_R12) / 12E >= 1000 THEN 'Potential Opportunity' ELSE 'Check'
END AS MCA
INTO #Mca2
FROM dbo.Fact_Financial_History_2 AS fh
INNER JOIN dbo.Dim_Outlet AS o ON o.FDMSAccountNo_First9 = fh.hst_merchnum
GROUP BY o.FDMSAccountNo;

UPDATE b
SET b.MCA = w.MCA
FROM #Build2 AS b
INNER JOIN #Mca2 AS w ON w.FDMSAccountNo = b.FDMSAccountNo
WHERE b.MCA = 'Potential Opportunity';

--Calculating Dcc Opps On omnipay --
SELECT o.FDMSAccountNo,
SUM(p.Tot_Purch_Amt) AS Gross,
SUM(p.Tot_Purch_Amt) / 12 AS GrossAvg,
SUM(p.DCC_Purch_Amt) AS Dcc,
SUM(p.DCC_Purch_Amt) / 12E AS DCCAvg,
SUM(p.Merch_Commiss) AS Commision,
CASE
WHEN SUM(p.DCC_Purch_Amt) < 0.1 THEN 'DCC Opp - Not processing Trans'
WHEN SUM(p.DCC_Purch_Amt) > 0.1 THEN 'Processing Dcc Transactions'
ELSE 'Check'
END AS DCC
INTO #Omnipaydcc
FROM dbo.stg_Fact_Omnipay_Profitability AS p
INNER JOIN dbo.Dim_Outlet AS o ON o.Omnipay_Account = p.Our_Reference
WHERE CONVERT(DATETIME, p.Period, 103) BETWEEN @RFrom AND @Rto
GROUP BY o.FDMSAccountNo;

UPDATE b
SET b.DCC = w.DCC
FROM #Build2 AS b
INNER JOIN #Omnipaydcc AS w ON w.FDMSAccountNo = b.FDMSAccountNo
--WHERE b.MCA = 'Potential Opportunity';[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources