| Author |
 Topic  |
|
|
masond
Constraint Violating Yak Guru
250 Posts |
 Posted - 10/05/2012 : 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
Flowing Fount of Yak Knowledge
India
1681 Posts |
Posted - 10/05/2012 : 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 |
Edited by - bandi on 10/05/2012 08:20:46 |
 |
|
|
masond
Constraint Violating Yak Guru
250 Posts |
Posted - 10/05/2012 : 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 ? |
Edited by - masond on 10/05/2012 08:37:04 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1681 Posts |
|
|
masond
Constraint Violating Yak Guru
250 Posts |
Posted - 10/05/2012 : 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
|
|
|
| |
Topic |
|
|
|
displaying data in the correct layout
