Help with a distinct within my query

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

Help with a distinct within my query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 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
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 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
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-05 : 07:16:45

you are a star :)

thank you very much

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 07:29:17