grouping problem - 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

grouping problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-13 : 04:46:10

Hi guys

I am hoping you will be able to help me

i have the basis of my query, however it’s not in the desired layout

The results are being populated like this

hst_date_processed Region Sum Of Net Sales Sum Of Net Transactions
2012-06-01 EU 2031196.44 16794
2012-06-01 INT 1479859.09 13278
2012-07-01 EU 1974633.66 14673
2012-06-01 UK 99433288.66 903805
2012-08-01 EU 2242086.39 15589
2012-08-01 INT 1859279.81 13323
2012-07-01 INT 1486238.01 12682
2012-07-01 UK 112457878.58 1015684
2012-08-01 UK 104054609.69 1030256

However i need the results populated this thiss

hst_date_processed EU , NT, UK

June
Sum Of Net Sales
Sum Of Net Transactions

July
Sum Of Net Sales
Sum Of Net Transactions

August
Sum Of Net Sales
Sum Of Net Transactions

My query is as follows

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
Fact_Financial_History.hst_date_processed,
--Dim_Interchange_Tier_2.Qualification_2,
Dim_Interchange_Tier_2.Region,
SUM(Fact_Financial_History.Net_Sales) AS [Sum Of Net Sales],
SUM(Fact_Financial_History.Net_Trans) AS [Sum Of Net Transactions]
FROM Fact_Financial_History INNER JOIN Dim_Interchange_Tier_2 ON
Fact_Financial_History.hst_prod_code + '-' + Fact_Financial_History.hst_plan_code = Dim_Interchange_Tier_2.Plan_Code
WHERE
(Fact_Financial_History.hst_date_processed >= @date)
and Dim_Interchange_Tier_2.Qualification_2 in ('Secure eCom','Non Secure eCom')
GROUP BY
Fact_Financial_History.hst_date_processed,
--Dim_Interchange_Tier_2.Qualification_2,
Dim_Interchange_Tier_2.region

any ideas ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:17:33

looks like reporting requirement to me

i would have brought data as you've now and would have used a reporting tool like SSRS to generate given output using matrix container which allows row and column grouping to get output in desired format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources