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-22 : 10:55:00
|
Hey guys I really need your help and i have been looking at examples on line and haven’t progressed any further in the last 2 hours Aim – to get the values in the temp table (Mca2) column MCA to update the values within table #build2 column MCA, if certain criteria is met.For eg table #build2 column MCA if the values = 'FD MCA' or 'MCA - External party' then don’t update, however if value = 'Potential Opportunity' then use the values in table #mca2--- build 2table -- 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,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 #build2from #clientline right join #final on #final.FDMSAccountNo = #clientline.FDMSAccountNoorder by #final.MCA Desc--- -- MCA2update -- 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 #mca2FROM Fact_Financial_History_2 full outer join Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9GROUP BY Dim_Outlet.FDMSAccountNo |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-22 : 11:36:34
|
[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 #Build2FROM #Final AS fLEFT JOIN #ClientLine AS cl ON cl..FDMSAccountNo = f.FDMSAccountNo;-- MCA2updateSELECT 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 #Mca2FROM dbo.Fact_Financial_History_2 AS fhINNER JOIN dbo.Dim_Outlet AS o ON o.FDMSAccountNo_First9 = fh.hst_merchnumGROUP BY o.FDMSAccountNo;-- SwePesoUPDATE bSET b.MCA = w.MCAFROM #Build2 AS bINNER JOIN #Mca2 AS w ON ON w.FDMSAccountNo = b.FDMSAccountNoWHERE b.MCA = 'Potential Opportunity';[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|