displaying data in the correct layout

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

displaying data in the correct layout

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 07:33:12

Hey guys

i need some help

i have created the following query

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
order by Fact_Financial_History.hst_date_processed desc

at present the query produces the results as follows

hst_date_processed Region Sum Of NetSales Sum Of Net Transactions
01/06/2012 EU 2031196.44 16794
01/06/2012 INT 1479859.09 13278
01/07/2012 EU 1974633.66 14673
01/06/2012 UK 99433288.66 903805
01/08/2012 EU 2242086.39 15589
01/08/2012 INT 1859279.81 13323
01/07/2012 INT 1486238.01 12682
01/07/2012 UK 112457878.6 1015684
01/08/2012 UK 104054609.7 1030256

i however need the results to be displayed as

Month EU NT UK Grand Total
August net Sales 2242086 1859280 104054609.7 108155976
August net trans 15589 13323 1030256 1059168
July net Sales 1974634 1486238 112457878.58 115918750
July net trans 14673 12682 1015684 1043039
june net Sales 2031196 1479859 99433288.66 102944344
june net trans 16794 13278 903805 933877
Total net sales 6247916 4825377 315945776.9 327019070
Total net Trans 47056 39283 2949745 3036084

Does anyone have any ideas how i can achieve this ?
ps( i dont have reporting services)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 08:15:58

Check this one

Edit:
--> Replace 'grand total' as ISNULL([EU], 0)+ISNULL([INT], 0)+ISNULL([UK], 0) in both cases
--> Replace UNION ALL with UNION

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

SELECT DISTINCT DATENAME(MM, hst_date_processed)+' Net Sales' as Months, [EU], [INT], [UK], [EU]+[INT]+[UK] as 'Grand Total'
FROM
(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

) as p
PIVOT
( MAX( [Sum Of Net Sales]) FOR Region IN ([EU], [INT], [UK]) ) as pvt

UNION ALL

SELECT DISTINCT DATENAME(MM, hst_date_processed)+' Net Trans' as Months, [EU], [INT], [UK], [EU]+[INT]+[UK] as 'Grand Total'
FROM
(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

) as p
PIVOT
( MAX( [Sum Of Net Transactions]) FOR Region IN ([EU], [INT], [UK]) ) as pvt
order by Months

This code will get only this part
Month EU NT UK Grand Total
August net Sales 2242086 1859280 104054609.7 108155976
August net trans 15589 13323 1030256 1059168
July net Sales 1974634 1486238 112457878.58 115918750
July net trans 14673 12682 1015684 1043039
june net Sales 2031196 1479859 99433288.66 102944344
june net trans 16794 13278 903805 933877

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 08:33:10

HI bandi

Sorry to seem thick

but i take it you mean like so ..

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

SELECT DISTINCT DATENAME(MM, hst_date_processed)+' Net Sales' as Months, [EU], [INT], [UK], ISNULL([EU], 0)+ISNULL([INT], 0)+ISNULL([UK], 0) as 'Grand Total'
FROM
(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

) as p
PIVOT
( MAX( [Sum Of Net Sales]) FOR Region IN ([EU], [INT], [UK]) ) as pvt

UNION

SELECT DISTINCT DATENAME(MM, hst_date_processed)+' Net Trans' as Months, [EU], [INT], [UK], ISNULL([EU], 0)+ISNULL([INT], 0)+ISNULL([UK], 0) as 'Grand Total'
FROM
(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

) as p
PIVOT
( MAX( [Sum Of Net Transactions]) FOR Region IN ([EU], [INT], [UK]) ) as pvt
order by Months

if thats the case then my results will be displayed as .

Months EU INT UK Grand Total
August Net Sales NULL NULL 104054609.69 104054609.69
August Net Sales NULL 1859279.81 NULL 1859279.81
August Net Sales 2242086.39 NULL NULL 2242086.39
August Net Trans NULL NULL 1030256.00 1030256.00
August Net Trans NULL 13323.00 NULL 13323.00
August Net Trans 15589.00 NULL NULL 15589.00
July Net Sales NULL NULL 112457878.58 112457878.58
July Net Sales NULL 1486238.01 NULL 1486238.01
July Net Sales 1974633.66 NULL NULL 1974633.66
July Net Trans NULL NULL 1015684.00 1015684.00
July Net Trans NULL 12682.00 NULL 12682.00
July Net Trans 14673.00 NULL NULL 14673.00
June Net Sales NULL NULL 99433288.66 99433288.66
June Net Sales NULL 1479859.09 NULL 1479859.09
June Net Sales 2031196.44 NULL NULL 2031196.44
June Net Trans NULL NULL 903805.00 903805.00
June Net Trans NULL 13278.00 NULL 13278.00
June Net Trans 16794.00 NULL NULL 16794.00

ideally i need a one liner per month for net sales and net trans
so for eg
eu int Uk Grand total
August Net sales 100 200 300 600
Is that possible ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 08:52:25

Check this link related to Pivot

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=179094

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 09:02:58

Bandi

thank you for providing that link, it was a simple sum which threw me out ?

the query should of been

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

SELECT DISTINCT DATENAME(MM, hst_date_processed)+'Net Sales' as Months,
[EU], [INT], [UK], ISNULL([EU],0)+ISNULL([INT],0)+ISNULL([UK],0) as 'Grand Total'
FROM
(SELECT Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Region,
SUM(Fact_Financial_History.Net_Sales) AS [Sum Of Net Sales]
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.region

) as p
PIVOT
( MAX( [Sum Of Net Sales]) FOR Region IN ([EU], [INT], [UK]) ) as pvt

UNION

SELECT DISTINCT DATENAME(MM, hst_date_processed)+'Net Trans' as Months,
[EU], [INT], [UK], ISNULL([EU], 0)+ISNULL([INT], 0)+ISNULL([UK], 0) as 'Grand Total'
FROM
(SELECT Fact_Financial_History.hst_date_processed,
Dim_Interchange_Tier_2.Region,
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.region

) as p
PIVOT
( MAX( [Sum Of Net Transactions]) FOR Region IN ([EU], [INT], [UK]) ) as pvt
order by Months

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources