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-10 : 09:04:13
|
Hi Guys and GirlsThis seems such a daft question, but this is my query (see below), all i want to do is order grand total desc SELECT ParentID,Parent_Name,[EU],[INT], [UK], ISNULL([EU],0)+ISNULL([INT],0)+ISNULL([UK],0) as 'Grand Total'FROM(SELECT ParentID,Parent_Name,Dim_Interchange_Tier_2.Region,SUM(Fact_Financial_History_2.hst_sales_amt_R12) AS [Turnover]FROM fdms.dbo.Fact_Financial_History_2 INNER JOIN fdms.dbo.Dim_Interchange_Tier_2ON Fact_Financial_History_2.hst_prod_code + '-' + Fact_Financial_History_2.hst_plan_code = Dim_Interchange_Tier_2.Plan_Codeinner join fdms.dbo.Dim_Outlet on hst_merchnum =FDMSAccountNo_First9where ParentID in (select ParentID from #top)GROUP BY Dim_Outlet.ParentID,Dim_Outlet.Parent_Name,Dim_Interchange_Tier_2.region)as pPIVOT( MAX([Turnover]) FOR Region IN ([EU],[INT],[UK])) as pvt |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-10 : 09:14:26
|
[code]SELECT ParentID,Parent_Name,[EU],[INT], [UK], ISNULL([EU],0)+ISNULL([INT],0)+ISNULL([UK],0) as 'Grand Total'FROM(SELECT ParentID,Parent_Name,Dim_Interchange_Tier_2.Region,SUM(Fact_Financial_History_2.hst_sales_amt_R12) AS [Turnover]FROM fdms.dbo.Fact_Financial_History_2 INNER JOIN fdms.dbo.Dim_Interchange_Tier_2ON Fact_Financial_History_2.hst_prod_code + '-' + Fact_Financial_History_2.hst_plan_code = Dim_Interchange_Tier_2.Plan_Codeinner join fdms.dbo.Dim_Outlet on hst_merchnum =FDMSAccountNo_First9where ParentID in (select ParentID from #top)GROUP BY Dim_Outlet.ParentID,Dim_Outlet.Parent_Name,Dim_Interchange_Tier_2.region)as pPIVOT( MAX([Turnover]) FOR Region IN ([EU],[INT],[UK])) as pvtORDER BY [Grand Total] DESC[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-07-10 : 09:16:58
|
visakh16 Your knowledge astonishes me |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-10 : 09:19:27
|
quote: Originally posted by masond visakh16 Your knowledge astonishes me
I'm myself astonished by knowledge of quality geeks here I'm just a humble follower of them------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-10 : 11:26:16
|
Isn't this enough?SELECT o.ParentID, o.Parent_Name, SUM(CASE WHEN it.Region = 'EU' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS EU, SUM(CASE WHEN it.Region = 'INT' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS [INT], SUM(CASE WHEN it.Region = 'UK' THEN fh.hst_sales_amt_R12 ELSE 0 END) AS UK, SUM(fh.hst_sales_amt_R12) AS [Grand Total]FROM fdms.dbo.Fact_Financial_History_2 AS fhINNER JOIN fdms.dbo.Dim_Interchange_Tier_2 AS it ON it.Plan_Code = fh.hst_prod_code + '-' + fh.hst_plan_codeINNER JOIN fdms.dbo.Dim_Outlet AS o ON {Table alias here}.hst_merchnum = {Table alias here}.FDMSAccountNo_First9INNER JOIN #Top AS t ON t.ParentID = o.ParentIDGROUP BY o.ParentID, o.Parent_Name; N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
|