Ordering customer's average sales

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

SQL Server 2012 Forums

Transact-SQL (2012)

Ordering customer's average sales

Author

Topic

chaitujm
Starting Member

2 Posts

Posted - 2013-11-21 : 23:36:02

Hi all

I have state wise, customer wise average sales of 2012 and 2013. Each state has more than at least 15-20 customers. I need to order the customers, state wise in descending order of their average sales 2013. Apart from that, my other conditions which need to be met are:
1. Customers need to be in descending order of their percentage of sale to their state's total sale.
2. The number of customers per state should not be more than 10
3. Show only those customers whose cumulative average sales 2013 percentage falls under 80% of total state's percentage.
4. The rest of the customers should fall under a category 'The Rest' which should show their respective cumulative data.

I have been able to meet only 3 of the conditions but not three. Please help.

chaitujm
Starting Member

2 Posts

Posted - 2013-11-22 : 00:50:02

This is my code so far. I have only been able to restrict the no. of customers to 10 but meeting the 80% condition has been ungettable since I also need to put the other 20% in the 'The Rest' category.

WITH CTE
AS
(
SELECT zs.Zone,ZS.state,zs.[CUSTOMER CITY], ZS.[CUSTOMER NAME] ,SUM( ZS.NETWR/100000) AS VAL,YEAR(ZS.INVOICEDATE) AS INV_YEAR,MONTH(ZS.INVOICEDATE) AS INV_MONTH,
CONVERT(VARCHAR(7) ,ZS.INVOICEDATE,120) AS INVDT
FROM ZSSR_REPORT_TABLE ZS

WHERE zs.zone is not null
and YEAR(zs.INVOICEDATE)=2013

GROUP BY zs.[CUSTOMER NAME],state,zs.[CUSTOMER CITY],ZS.INVOICEDATE,zs.Zone,YEAR(ZS.INVOICEDATE),MONTH(ZS.INVOICEDATE)

)

,
CTE2
AS
(
SELECT TT.Zone,TT.state, tt.[CUSTOMER CITY], TT.[CUSTOMER NAME],SUM(TT.[1]) AS Jan,sum(tt.[2]) as Feb,sum(tt.[3]) as Mar,sum(tt.[4]) as Apr,
SUM(TT.[5]) AS May,sum(tt.[6]) as Jun,sum(tt.[7]) as Jul,sum(tt.

) as Aug,SUM(TT.[9]) AS Sep,sum(tt.[10]) as Oct,sum(tt.[11]) as Nov,sum(tt.[12]) as Dec

FROM CTE
PIVOT(SUM(VAL) FOR INV_MONTH IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12])) AS TT
group by TT.Zone,TT.state,tt.[CUSTOMER CITY], TT.[CUSTOMER NAME]

)

,CTE2013
AS
(
SELECT *,
ISNULL(Jan,0)+ISNULL(Feb,0)+ISNULL(MAR,0)+ISNULL(Apr,0)+ISNULL(Jun,0)+ISNULL(Jul,0)+ISNULL(Aug,0)+ISNULL(Sep,0)+ISNULL(Oct,0)+ISNULL(Nov,0)+ISNULL(Dec,0) AS "YTDSALES" ,
(ISNULL(Jan,0)+ISNULL(Feb,0)+ISNULL(MAR,0)+ISNULL(Apr,0)+ISNULL(Jun,0)+ISNULL(Jul,0)+ISNULL(Aug,0)+ISNULL(Sep,0)+ISNULL(Oct,0)+ISNULL(Nov,0)+ISNULL(Dec,0)) / DATEPART(MM,GETDATE()) AS "AVGSALES"
FROM CTE2
)

,CTE12
AS

(
SELECT zs.Zone,ZS.state, zs.District_Name,zs.[CUSTOMER CITY],ZS.[CUSTOMER NAME] ,SUM( ZS.NETWR/100000) AS VAL,YEAR(ZS.INVOICEDATE) AS INV_YEAR,MONTH(ZS.INVOICEDATE) AS INV_MONTH,
CONVERT(VARCHAR(7) ,ZS.INVOICEDATE,120) AS INVDT FROM ZSSR_REPORT_TABLE ZS

WHERE ZS.DIVISION<>20
and YEAR(zs.INVOICEDATE)=2012 and zs.zone is not null

GROUP BY zs.[CUSTOMER NAME],state,zs.District_Name,zs.[CUSTOMER CITY],ZS.INVOICEDATE,zs.Zone,YEAR(ZS.INVOICEDATE),MONTH(ZS.INVOICEDATE)
),
CTE112
AS
(
SELECT TT.Zone,TT.state, tt.District_Name,tt.[CUSTOMER CITY],TT.[CUSTOMER NAME],SUM(TT.[1]) AS Jan,sum(tt.[2]) as Feb,sum(tt.[3]) as Mar,sum(tt.[4]) as Apr,
SUM(TT.[5]) AS May,sum(tt.[6]) as Jun,sum(tt.[7]) as Jul,sum(tt.

) as Aug,SUM(TT.[9]) AS Sep,sum(tt.[10]) as Oct,sum(tt.[11]) as Nov,sum(tt.[12]) as Dec
FROM CTE12
PIVOT(SUM(VAL) FOR INV_MONTH IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12])) AS TT
group by TT.Zone,TT.state, tt.District_Name,tt.[CUSTOMER CITY],TT.[CUSTOMER NAME]
),CTE2012
AS
(
SELECT distinct *,
ISNULL(Jan,0)+ISNULL(Feb,0)+ISNULL(MAR,0)+ISNULL(Apr,0)+ISNULL(Jun,0)+ISNULL(Jul,0)+ISNULL(Aug,0)+ISNULL(Sep,0)+ISNULL(Oct,0)+ISNULL(Nov,0)+ISNULL(Dec,0) AS "YTDSALES" ,
(ISNULL(Jan,0)+ISNULL(Feb,0)+ISNULL(MAR,0)+ISNULL(Apr,0)+ISNULL(Jun,0)+ISNULL(Jul,0)+ISNULL(Aug,0)+ISNULL(Sep,0)+ISNULL(Oct,0)+ISNULL(Nov,0)+ISNULL(Dec,0)) / 9 AS "AVGSALES"

FROM CTE112
)

,CTEJ
AS
(
SELECT C3.Zone,C3.state,c3.[CUSTOMER CITY],C3.[CUSTOMER NAME],sum(C2.AVGSALES) AS "Avg12" , sum( C3.AVGSALES) AS "Avg13"--,C3.ARREAR-- SUM(C3.[<30DAYS]) AS "<30DYS",SUM(C3.[>30DAYS]) AS ">30DYS",SUM(C3.TOU) AS "TOTALOUTSTA",SUM(CLIMIT) AS CLIMIT

FROM CTE2013 C3 left JOIN CTE2012 C2 ON C3.Zone=C2.Zone AND C3.state=C2.state and c3.[CUSTOMER NAME]=c2.[CUSTOMER NAME]

GROUP BY C3.Zone,C3.state,c3.[CUSTOMER CITY],C3.[CUSTOMER NAME],C3.AVGSALES --,c3.District_Name,c3.[CUSTOMER CITY]--,C3.ARREAR--,C2.YTDSALES, C3.YTDSALES, c3.Sep
)

,ctecj2
as
(
select

CASE GROUPING(Zone)

WHEN 1 THEN 'Subtotal'

ELSE Zone END AS 'Zone',
CASE GROUPING(state)

WHEN 1 THEN Zone+' Subtotal'

ELSE state END AS 'state',
CASE GROUPING([CUSTOMER CITY])

WHEN 1 THEN state+' Subtotal'

ELSE [CUSTOMER CITY] END AS 'City',
CASE GROUPING([CUSTOMER NAME])

WHEN 1 THEN [CUSTOMER CITY]+' Subtotal'

ELSE [CUSTOMER NAME] END AS 'Customer',
sum([Avg12]) avg12,sum([Avg13]) avg13

from ctej
group by zone,state,[CUSTOMER CITY],[CUSTOMER NAME] with rollup

)

,ctecj1
as
(
select *, ROW_NUMBER() OVER(ORDER BY zone,state,[Avg13] desc) AS z1
,ROW_NUMBER() OVER(PARTITION BY state ORDER BY [Avg13] desc ) AS z2 from ctecj2 where Customer not like '%Subtotal%'
)

select top 10 *,avg13/(select (avg13) from CTECJ2 where city like '%Chhattisgarh Subtotal%' ) from CTECJ1 where state='Chhattisgarh' AND city not like '%Chhattisgarh Subtotal%'

union all

select 'Central','Chhattisgarh','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Chhattisgarh Subtotal%' group by avg13 )) from ctecj1 where state='Chhattisgarh' AND city not like '%Chhattisgarh Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Chhattisgarh Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Hyderabad Subtotal%' ) from CTECJ1 where state='Hyderabad' AND City not like '%Hyderabad Subtotal%'

union all

select 'Central','Hyderabad','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Hyderabad Subtotal%' group by avg13 )) from ctecj1 where state='Hyderabad' AND City not like '%Hyderabad Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Hyderabad Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Madhya Pradesh Subtotal%' ) from CTECJ1 where state='Madhya Pradesh' AND City not like '%Madhya Pradesh Subtotal%'

union all

select 'Central','Madhya Pradesh','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Madhya Pradesh Subtotal%' group by avg13 )) from ctecj1 where state='Madhya Pradesh' AND City not like '%Madhya Pradesh Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Madhya Pradesh Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%ROAP Subtotal%' ) from CTECJ1 where state='ROAP' AND City not like '%ROAP Subtotal%'

union all

select 'Central','ROAP','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%ROAP Subtotal%' group by avg13 )) from ctecj1 where state='ROAP' AND City not like '%ROAP Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%ROAP Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Telangana Subtotal%' ) from CTECJ1 where state='Telangana' AND City not like '%Telangana Subtotal%'

union all

select 'Central','Telangana','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Telangana Subtotal%' group by avg13 )) from ctecj1 where state='Telangana' AND City not like '%Telangana Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Telangana Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Vidharba Subtotal%' ) from CTECJ1 where state='Vidharba' AND City not like '%Vidharba Subtotal%'

union all

select 'Central','Vidharba','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Vidharba Subtotal%' group by avg13 )) from ctecj1 where state='Vidharba' AND City not like '%Vidharba Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Vidharba Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Assam Subtotal%' ) from CTECJ1 where state='Assam' AND City not like '%Assam Subtotal%'

union all

select 'East','Assam','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Assam Subtotal%' group by avg13 )) from ctecj1 where state='Assam' AND City not like '%Assam Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Assam Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Bihar Subtotal%' ) from CTECJ1 where state='Bihar' AND City not like '%Bihar Subtotal%'

union all

select 'East','Bihar','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Bihar Subtotal%' group by avg13 )) from ctecj1 where state='Bihar' AND City not like '%Bihar Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Bihar Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Jharkhand Subtotal%' ) from CTECJ1 where state='Jharkhand' AND City not like '%Jharkhand Subtotal%'

union all

select 'East','Jharkhand', '','The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Jharkhand Subtotal%' group by avg13 )) from ctecj1 where state='Jharkhand' AND City not like '%Jharkhand Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Jharkhand Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Orissa Subtotal%' ) from CTECJ1 where state='Orissa' AND City not like '%Orissa Subtotal%'

union all

select 'East','Orissa','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Orissa Subtotal%' group by avg13 )) from ctecj1 where state='Orissa' AND City not like '%Orissa Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Orissa Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%West Bengal Subtotal%' ) from CTECJ1 where state='West Bengal' AND City not like '%West Bengal Subtotal%'

union all

select 'East','West Bengal','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%West Bengal Subtotal%' group by avg13 )) from ctecj1 where state='West Bengal' AND City not like '%West Bengal Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%West Bengal Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Delhi & NCR Subtotal%' ) from CTECJ1 where state='Delhi & NCR' AND City not like '%Delhi & NCR Subtotal%'

union all

select 'North','Delhi & NCR','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Delhi & NCR Subtotal%' group by avg13 )) from ctecj1 where state='Delhi & NCR' AND City not like '%Delhi & NCR Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Delhi & NCR Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Haryana Subtotal%' ) from CTECJ1 where state='Haryana' AND City not like '%Haryana Subtotal%'

union all

select 'North','Haryana','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Haryana Subtotal%' group by avg13 )) from ctecj1 where state='Haryana' AND City not like '%Haryana Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Haryana Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Jammu & Kashmir Subtotal%' ) from CTECJ1 where state='Jammu & Kashmir' AND City not like '%Jammu & Kashmir Subtotal%'

union all

select 'North','Jammu & Kashmir','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Jammu & Kashmir Subtotal%' group by avg13 )) from ctecj1 where state='Jammu & Kashmir' AND City not like '%Jammu & Kashmir Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Jammu & Kashmir Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Punjab Subtotal%' ) from CTECJ1 where state='Punjab' AND City not like '%Punjab Subtotal%'

union all

select 'North','Punjab','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Punjab Subtotal%' group by avg13 )) from ctecj1 where state='Punjab' AND City not like '%Punjab Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Punjab Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Rajasthan Subtotal%' ) from CTECJ1 where state='Rajasthan' AND City not like '%Rajasthan Subtotal%'

union all

select 'North','Rajasthan','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Rajasthan Subtotal%' group by avg13 )) from ctecj1 where state='Rajasthan' AND City not like '%Rajasthan Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Rajasthan Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Uttar Pradesh - East Subtotal%' ) from CTECJ1 where state='Uttar Pradesh - East' AND City not like '%Uttar Pradesh - East Subtotal%'

union all

select 'North','Uttar Pradesh - East','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Uttar Pradesh - East Subtotal%' group by avg13 )) from ctecj1 where state='Uttar Pradesh - East' AND City not like '%Uttar Pradesh - East Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Uttar Pradesh - East Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Uttar Pradesh - West Subtotal%' ) from CTECJ1 where state='Uttar Pradesh - West' AND City not like '%Uttar Pradesh - West Subtotal%'

union all

select 'North','Uttar Pradesh - West','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Uttar Pradesh - West Subtotal%' group by avg13 )) from ctecj1 where state='Uttar Pradesh - West' AND City not like '%Uttar Pradesh - West Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Uttar Pradesh - West Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Uttaranchal Subtotal%' ) from CTECJ1 where state='Uttaranchal' AND City not like '%Uttaranchal Subtotal%'

union all

select 'North','Uttaranchal','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Uttaranchal Subtotal%' group by avg13 )) from ctecj1 where state='Uttaranchal' AND City not like '%Uttaranchal Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Uttaranchal Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Karnataka Subtotal%' ) from CTECJ1 where state='Karnataka' AND City not like '%Karnataka Subtotal%'

union all

select 'South','Karnataka','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Karnataka Subtotal%' group by avg13 )) from ctecj1 where state='Karnataka' AND City not like '%Karnataka Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Karnataka Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Kerala Subtotal%' ) from CTECJ1 where state='Kerala' AND City not like '%Kerala Subtotal%'

union all

select 'South','Kerala','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Kerala Subtotal%' group by avg13 )) from ctecj1 where state='Kerala' AND City not like '%Kerala Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Kerala Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Tamil Nadu Subtotal%' ) from CTECJ1 where state='Tamil Nadu' AND City not like '%Tamil Nadu Subtotal%'

union all

select 'South','Tamil Nadu','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Tamil Nadu Subtotal%' group by avg13 )) from ctecj1 where state='Tamil Nadu' AND City not like '%Tamil Nadu Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Tamil Nadu Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Goa Subtotal%' ) from CTECJ1 where state='Goa' AND City not like '%Goa Subtotal%'

union all

select 'West','Goa','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Goa Subtotal%' group by avg13 )) from ctecj1 where state='Goa' AND City not like '%Goa Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Goa Subtotal%'
union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Gujarat Subtotal%' ) from CTECJ1 where state='Gujarat' AND City not like '%Gujarat Subtotal%'

union all

select 'West','Gujarat','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Gujarat Subtotal%' group by avg13 )) from ctecj1 where state='Gujarat' AND City not like '%Gujarat Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Gujarat Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%Mumbai Subtotal%' ) from CTECJ1 where state='Mumbai' AND City not like '%Mumbai Subtotal%'

union all

select 'West','Mumbai', '','The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%Mumbai Subtotal%' group by avg13 )) from ctecj1 where state='Mumbai' AND City not like '%Mumbai Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%Mumbai Subtotal%'

union all

select top 10 *,avg13/(select (avg13) from CTECJ2 where City like '%ROM Subtotal%' ) from CTECJ1 where state='ROM' AND City not like '%ROM Subtotal%'

union all

select 'West','ROM','', 'The Rest',isnull(cast(sum(avg12) as numeric(22,2)),0) ,isnull(cast(sum(avg13) as numeric(22,2)),0),11,11, (sum(avg13)/(select (avg13) from CTECJ2 where city like '%ROM Subtotal%' group by avg13 )) from ctecj1 where state='ROM' AND City not like '%ROM Subtotal%' and z2>10

union all

select *,12,12,1 from ctecj2 where city like '%ROM Subtotal%'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-22 : 10:30:14

Here are some link for how to post/ask your question so that we can help you better. That includes posting sample data (in a consumable format) and expected output.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

I shouldn't be too hard to put together a query to help you out, but please post sample data so we have something to code against. it makes it easier for us and you get working code.

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2013-11-26 : 14:51:55

A couple of things. Why are you coding all your states in union statements? Why don't you just pull the state names from the table?

You can really simplify this query by using SQL Server's DATE functions, specifically DATEPART;

You also should research the windowing functions (the OVER clause). That will help with both the date issue and the geographic issue.

Good luck on your assignment.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources