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.
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 tablesselect #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,casewhen 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 VTinto #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 MCAinto #mca2 FROM Fact_Financial_History_2 full outer join Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9GROUP BY Dim_Outlet.FDMSAccountNoUPDATE bSET b.MCA = w.MCAFROM #Build2 AS bINNER JOIN #Mca2 AS w ON w.FDMSAccountNo = b.FDMSAccountNoWHERE 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 #OmnipaydccFROM stg_Fact_Omnipay_Profitability INNER JOINDim_Outlet ON stg_Fact_Omnipay_Profitability.Our_Reference = Dim_Outlet.Omnipay_AccountWHERE (CONVERT(datetime, Period, 103) BETWEEN @RFrom AND @Rto) GROUP BY Dim_Outlet.FDMSAccountNoorder by new desc |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
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 bSET b.DCC = o.DccFROM #Build2 AS bINNER 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 VTINTO #Build2 FROM #Final AS fLEFT 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 MCAINTO #Mca2 FROM dbo.Fact_Financial_History_2 AS fhINNER JOIN dbo.Dim_Outlet AS o ON o.FDMSAccountNo_First9 = fh.hst_merchnumGROUP BY o.FDMSAccountNo;UPDATE bSET b.MCA = w.MCAFROM #Build2 AS bINNER JOIN #Mca2 AS w ON w.FDMSAccountNo = b.FDMSAccountNoWHERE 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 DCCINTO #OmnipaydccFROM dbo.stg_Fact_Omnipay_Profitability AS pINNER JOIN dbo.Dim_Outlet AS o ON o.Omnipay_Account = p.Our_ReferenceWHERE CONVERT(DATETIME, p.Period, 103) BETWEEN @RFrom AND @RtoGROUP BY o.FDMSAccountNo;UPDATE bSET b.DCC = w.DCCFROM #Build2 AS bINNER 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 |
|
|
|
|
|
|
|