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
 Help with a distinct within my query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  04:19:56  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
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
2168 Posts

Posted - 10/05/2012 :  04:47:50  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  05:08:01  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/05/2012 :  05:56:05  Show Profile  Reply with Quote
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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 10/05/2012 :  07:16:45  Show Profile  Reply with Quote
you are a star :)

thank you very much
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 10/05/2012 :  07:29:17  Show Profile  Reply with Quote
quote:
Originally posted by masond

you are a star :)

thank you very much



Welcome

--
Chandu
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.08 seconds. Powered By: Snitz Forums 2000