| Author |
 Topic  |
|
|
masond
Posting Yak Master
241 Posts |
 Posted - 10/05/2012 : 04:19:56
|
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
CASE
WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'
ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))
END AS FDMSAccountNo_First9,
dbo.Dim_Outlet.DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
[dbo].[Dim_Outlet].DBA_Name
with rollup
At present the query produces duplicate records
for eg
Fdmsaccountno_first9 DBA_ Name Totalofnetamount totalofnettransactions
0001 Test £100 15
0001 null £100 15
0002 KFC £3000 100
0002 null £3000 100
I however need unique /distinct FDMSaccountno
for eg
Fdmsaccountno_first9 DBA_ Name Totalofnetamount totalofnettransactions
0001 Test £100 15
0002 KFC £3000 100
, does anyone have any ideas how i can achieve this ?
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 10/05/2012 : 04:47:50
|
Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT *
FROM ( SELECT
CASE
WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'
ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))
END AS FDMSAccountNo_First9,
dbo.Dim_Outlet.DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
[dbo].[Dim_Outlet].DBA_Name
with rollup
) as temp
WHERE temp.DBA_NAME IS NOT NULL
--
Chandu |
 |
|
|
masond
Posting Yak Master
241 Posts |
Posted - 10/05/2012 : 05:08:01
|
HI bandi
Thank you for your response,
Your adaptation of my query removes my grand total row
In my original query, grand total, was in the FDMS accountno_first9, but because there wasn’t a DBA_name it produced a null
in your query , you have removed the nulls from the DBA name, consequently removing my grand total row.
Is there anyway, i can get the grand total row back?
I Dont mind Grand total being populated in the FDMSaccountno_first9 and also dba_name
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 10/05/2012 : 05:56:05
|
I can provide one more logical solution...........
I am not sure.. But try this one
Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT *
FROM ( SELECT
CASE
WHEN GROUPING([dbo].[Dim_Outlet].FDMSAccountNo_First9) = 1 THEN 'Grand Total'
ELSE CAST([dbo].[Dim_Outlet].FDMSAccountNo_First9 AS VARCHAR(20))
END AS FDMSAccountNo_First9,
dbo.Dim_Outlet.DBA_Name,
SUM(dbo.Fact_Financial_History.Net_Sales) AS TotalofNetAmount,
SUM(dbo.Fact_Financial_History.Net_Trans)AS TotalofNetTransactions
FROM
Fact_Financial_History
INNER JOIN
Dim_Outlet
ON Fact_Financial_History.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
INNER JOIN
dbo.Dim_MCC
ON dbo.Dim_Outlet.MCC_Code = dbo.Dim_MCC.MCC
WHERE
(dbo.Fact_Financial_History.hst_prod_code BETWEEN '79' AND '84')
and MCC_Code = '5968'
AND (dbo.Fact_Financial_History.hst_date_processed > @date)
group by
[dbo].[Dim_Outlet].FDMSAccountNo_First9,
[dbo].[Dim_Outlet].DBA_Name
with rollup
) as temp
WHERE temp.DBA_NAME IS NOT NULL OR temp.FDMSAccountNo_First9 = 'Grand Total'
--
Chandu |
|
|
|
masond
Posting Yak Master
241 Posts |
Posted - 10/05/2012 : 07:16:45
|
you are a star :)
thank you very much |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 10/05/2012 : 07:29:17
|
quote:
Originally posted by masond
you are a star :)
thank you very much
Welcome
--
Chandu |
|
|
| |
Topic |
|
Help with a distinct within my query
