Shortening a query - SQL Server Forums

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

Shortening a query

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 07:42:05

Hey guys

I have yet another problem with my query

This is my original query which only returns 86 records

SELECT sub.*,

case when SalesBandRM IN ('100m +')
then 'R05'
When SalesBandRM IN ('50M to 100M')
then 'R12'
when SalesBandRM = '2M to 4M'
then 'R15'

else RMSC end as RMSC

into #RM
FROM
(SELECT
[DBA_Name],
o.[fdmsaccountno],
[ho],
sum ([Gross_Sales]) as Sales_Annualised,
dbo.salesbandRM(sum(Gross_Sales))as SalesBandRM,
[post_code],
[Open_Date],

CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)
END AS 'sPostcode',
[mcc_code]
From [FDMS].[dbo].[Fact_Financial_History_Annualised] f
inner join Dim_Outlet o
on f.FDMSAccountNo = o.FDMSAccountNo
wHERE Account_Status = '16'
and LBG_Status <> 'Accepted'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
FROM
fdms_partnerreporting.tmp.trade_assocations)

group by
[DBA_Name],
o.[fdmsaccountno],
[ho],
[post_code],
[Open_Date],
[MCC_Code]

having sum([Gross_Sales])> 1999999

) Sub
INNER JOIN [geo_pca_sellers]
ON [pca] = spostcode

order by DBA_Name

--Drop table #RM

update rm
set RMSC = case when rn % 2 = 0 then 'R15' else 'R16' end
from
(
select RMSC, rn = row_number() over (order by Sales_Annualised desc)
from #RM
where RMSC = 'R15'
) rm

Declare @dateYTD varchar(10)
set @dateYTD = (select year(MAX(hst_date_processed))
from fdms.dbo.Fact_Financial_History)
SELECT ParentID
,sum([hst_sales_amt]) as Actual_SalesYTD
Into #YTD
FROM [FDMS].[dbo].[Fact_Financial_History] f inner join dim_outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where year(hst_date_processed) = @dateYTD
group by o.ParentID

--Drop table #YTD

--Rolling 12 Months --
Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT ParentID
,sum([hst_sales_amt]) as Rolling_12
Into #R12
FROM [FDMS].[dbo].[Fact_Financial_History] f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @date
group by o.ParentID

Select
r.FDMSAccountNo,
r.DBA_Name,
Post_Code,
Open_Date,
Sales_Annualised,
SalesBandRM,
Rolling_12,
Actual_SalesYTD,
RMSC
from #R12 r12
Inner Join #RM r on r12.parentid = r.fdmsaccountno
inner join #YTD y on r.FDMSAccountNo = y.ParentID

--Drop table #R12

I some need help rewriting that query above Around this query below( The query below returns the correct number of records, so i know its correct. I just need to apply the logic from above into the one below)

select ParentID,SUM(Gross_Sales) as ASales
Into #rm_Asales
from Dim_Outlet o inner join Fact_Financial_History_Annualised fh on o.FDMSAccountNo = fh.FDMSAccountNo
group by ParentID
having SUM (fh.Gross_Sales) > 1999999
order by SUM(Gross_Sales) desc

select rm.ParentID,Dba_Name,o.Post_Code,ASales,dbo.SalesBandRM([Asales]) as SalesID
From #rm_Asales rm inner join Dim_Outlet o on o.FDMSAccountNo = rm.ParentID

Can anyone help me with this ?

I know its alot to ask for , but for the love of me i cant work it out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:21:30

can you explain with some sample data what you mean by applying logic in below query? without some data we cant understand what your above queries are doing. please give intermediate results as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-30 : 10:25:24

Hi Visakh16,

do you have access to your gmail account ? if so il drop you an email on there with a few screen prints, and it give you a better understanding of what i am trying to achieve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:33:53

quote:
Originally posted by masond

Hi Visakh16,

do you have access to your gmail account ? if so il drop you an email on there with a few screen prints, and it give you a better understanding of what i am trying to achieve

post your data here. it will give oppurtunity for others also to see and reply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources