SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 displaying data in the correct layout
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  07:33:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/05/2012 :  08:15:58  Show Profile  Reply with Quote
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

Edited by - bandi on 10/05/2012 08:20:46
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  08:33:10  Show Profile  Reply with Quote
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 ?

Edited by - masond on 10/05/2012 08:37:04
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/05/2012 :  08:52:25  Show Profile  Reply with Quote
Check this link related to Pivot


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

--
Chandu
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  09:02:58  Show Profile  Reply with Quote
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







Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000