Solved

Tier Query Results MS SQL

Posted on 2013-01-03
17
462 Views
Last Modified: 2013-01-03
Hello Experts,

I have a query, which returns results from an online survey, the existing query is detailed below -

select 
ROW_NUMBER() OVER(order by (((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) desc) as Position, 
brandid,
B.Name,
/*sum(Saleability) TotalSaleability,*/ 
sum(Saleability) /count(*) as avgSaleability,
/*sum(Quality) TotalQuality,*/
sum(Quality) /count(*) as avgQuality,
/*sum(Availability) TotalAvailability,*/
sum(Availability) /count(*) as avgAvailability,
/*sum(Delivery) TotalDelivery,*/
sum(Delivery) /count(*) as avgDelivery,
/*sum([Returns]) TotalReturns,*/
sum(Returns) /count(*) as avgReturns,
/*sum(RetailPrice) TotalRetailPrice,*/
sum(RetailPrice) /count(*) as avgRetailPrice,
/*sum(ServiceSupport) TotalServiceSupport,*/
sum(ServiceSupport) /count(*) as avgServiceSupport,
/*sum(Rep) TotalRep,*/
sum(Rep) /count(*) as avgRep,
/*sum(Accounts) TotalAccounts,*/
sum(Accounts) /count(*) as avgAccounts,
/*sum(CreditControl) TotalCreditControl,*/
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY 
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
where [CYearStockist] = 'Y'
group by b.name, brandid, SupplierSurveyID
order by Total desc

Open in new window


What I would like to do though is tier the results, so that any suppliers with less that 10% stockists are moved to the bottom, so the results would look like so

Stockists > 10% order by Total Desc
Stockists < 10% order by Total Desc

and have these returned in one recordset as they are currently.

Any suggestions?

Appreciate the help, many thanks
0
Comment
Question by:garethtnash
  • 8
  • 8
17 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38739553
we do not see what you see: table structure/data/etc.. please clarify:
what is the result now and what you wish to have?

if you can send some sample \example with some create table\populate this table with some data and your select -- it could help too
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38739573
What you need to do is add another column e.g. StockistPercentage and use CASE

when Stockists >= 10% then 1
when Stockists < 10% then 2

and do the Order By StockistPercentage, Total Desc


if you need further help to calculate percentage then let me know
0
 

Author Comment

by:garethtnash
ID: 38739585
Thank you Both,

My tables that form the query look like this --

CREATE TABLE [dbo].[SupplierSurveyInput](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SupplierSurveyID] [int] NULL,
	[BrandID] [int] NULL,
	[PartnerID] [int] NULL,
	[CYearStockist] [char](1) NULL,
	[CYearNStockistWhy] [int] NULL,
	[CountryID] [int] NULL,
	[Saleability] [decimal](5, 2) NULL,
	[Quality] [decimal](5, 2) NULL,
	[Availability] [decimal](5, 2) NULL,
	[Delivery] [decimal](5, 2) NULL,
	[Returns] [decimal](5, 2) NULL,
	[RetailPrice] [decimal](5, 2) NULL,
	[ServiceSupport] [decimal](5, 2) NULL,
	[Rep] [decimal](5, 2) NULL,
	[Accounts] [decimal](5, 2) NULL,
	[CreditControl] [decimal](5, 2) NULL,
	[NextYearTrade] [char](1) NULL,
	[NoTradeWhy] [int] NULL,
	[Completed] [char](1) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SupplierSurveyInput] ADD  DEFAULT ('N') FOR [CYearStockist]
GO

ALTER TABLE [dbo].[SupplierSurveyInput] ADD  DEFAULT ('N') FOR [NextYearTrade]
GO

ALTER TABLE [dbo].[SupplierSurveyInput] ADD  DEFAULT ('N') FOR [Completed]
GO

Open in new window


And

CREATE TABLE [dbo].[Brand](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SupplierID] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Add1] [nvarchar](255) NULL,
	[Add2] [nvarchar](255) NULL,
	[City] [int] NULL,
	[County] [int] NULL,
	[Country] [int] NULL,
	[Postcode] [nvarchar](10) NULL,
	[SwitchBoard] [nvarchar](20) NULL,
	[Fax] [nvarchar](20) NULL,
	[URL] [nvarchar](250) NULL,
	[Logo] [nvarchar](250) NULL,
	[UK] [char](1) NULL,
	[Eire] [char](1) NULL,
	[TGIAccountMgr] [int] NULL,
	[StartDate] [datetime] NULL,
	[ExitDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Brand] ADD  DEFAULT ('N') FOR [UK]
GO

ALTER TABLE [dbo].[Brand] ADD  DEFAULT ('N') FOR [Eire]
GO

Open in new window


I can query the table using the query above, and I can query the table like so -

SELECT COUNT([ID])/62 Completed
  FROM [dbo].[SupplierSurveyInput]
  where CYearStockist <> 'u'

Open in new window


To get the total number of people that participated in the survey.

I'm stuck as to how to marry these two together?

Thanks
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38739648
Can you also provide some sample data for both files in excel table format

thanks
0
 

Author Comment

by:garethtnash
ID: 38739657
Hi RehanYousaf,

I think I'm a stage closer on this, using the query below, what's the best way of including your CASE statement?

The query below adds a column that returns the total number of survey participants...

select 
ROW_NUMBER() OVER(order by (((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) desc) as Position, 
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY, 
C.Completed Completed
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/62 Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y'
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Total desc

Open in new window


The data is slightly complicated, but I'll see what I can do :)
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38739682
If you have to calculate Stockists %, how will you do it

based on that I can provide you with the code


Also,  why are you hard coding 62 in your code
0
 

Author Comment

by:garethtnash
ID: 38739691
Here's the main table data, for the brand table, please remove all but the ID and Name columns for the DDL, so --

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Brand](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Open in new window


And use the Brands spreadsheet attached ....


Thank you :)SupplierSurveySample-Data.xlsxBrand-Sample-Data.xlsx
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38739756
Thanks for the data, just need a little more explanation in simple term that
what Stockists is?,  and
to calculate Stockists percentage from the data provided in excel how will I do it using simple maths?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:garethtnash
ID: 38739864
Thanks RehanYousaf, ok to explain, we are a buying group for a number of retailers (stockists) each year we ask them to complete a survey on the brands that they stock, for the data returned the 'Completed' column is the total number of retailers that completed the survey, 'Stockists' is the number of retailers that stocked the brand, as they don't stock all brands.

As we don't want the figures to be offset by a brand that has a very a single very happy stockist and no other stockists. we have decided to move all brands where less (or equal) than 10% of the the total 'completed' are stockists to the bottom and then apply the normal order by.

So in Excel, the query would be = Sum('Stockists'/'Completed')*100, when i tried this in SQL, I got strange results....

Hope this helps.

62 was hard coded, as we know that to be the total brands this year, although if this could be queried as COUNT DISTINCT  brandid that would be good also....

Thank you so much.

G
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38739993
Your calculations are fine, just need to change

COUNT([ID])/62 Completed
to
CONVERT(FLOAT, COUNT([ID]))/62 Completed

and case statement will be something like

CASE
            WHEN c.Completed >= 0.10 THEN 1
            ELSE 0
      END AS PercentageOrder
0
 

Author Comment

by:garethtnash
ID: 38740018
Thank you, :)

I think we are almost there,.. I had just put the following together --

select 
ROW_NUMBER() OVER(order by (((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) desc) as Position, 
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY, 
C.Completed Completed,
CASE WHEN (count(*)*100)/C.Completed <= 10 then 1
Else 2 END as Tier
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/62 Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y'
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Tier desc, Total desc

Open in new window


Which appears to work, but it still has the hard coded 62 in it, any suggestions on the best way to not have this hardcoded?

Again, thank you so much for all your help.

G
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38740052
Anotehr way of doing this will be

;WITH cteSupplierSurveyInput AS (
	SELECT
		SupplierSurveyID,
		BrandID,
		SUM(Saleability) /COUNT(*) AS avgSaleability,
		SUM(Quality) /COUNT(*) AS avgQuality,
		SUM(Availability) /COUNT(*) AS avgAvailability,
		SUM(Delivery) /COUNT(*) AS avgDelivery,
		SUM(RETURNS) /COUNT(*) AS avgReturns,
		SUM(RetailPrice) /COUNT(*) AS avgRetailPrice,
		SUM(ServiceSupport) /COUNT(*) AS avgServiceSupport,
		SUM(Rep) /COUNT(*) AS avgRep,
		SUM(Accounts) /COUNT(*) AS avgAccounts,
		SUM(CreditControl) /COUNT(*) AS avgCreditControl,
		(((SUM(Saleability)  + SUM(Quality)  + SUM(Availability) + SUM(Delivery) + SUM([RETURNS]) + SUM(RetailPrice) + SUM(ServiceSupport) + SUM(Rep) + SUM(Accounts) + SUM(CreditControl) ) /10)  / COUNT(*)) Total,
		COUNT(*) Stockists,
		SUM(CASE WHEN nextyeartrade = 'Y' THEN 1 ELSE 0 END) AS StockistsNY
	FROM 
		SupplierSurveyInput S	
	GROUP BY 
		S.BrandID, 
		S.SupplierSurveyID
),
cteSupplierSurveyCountFiltered AS(
	SELECT 
		SupplierSurveyID,
		COUNT(*) AS SupplierSurveyCountFiltered
	FROM	
		SupplierSurveyInput	
	WHERE
		CYearStockist != 'U'
	GROUP BY
		SupplierSurveyID
),
cteSupplierSurveyCount AS (
SELECT 
	SupplierSurveyID,
	COUNT(*) AS SupplierSurveyCount
FROM	
	SupplierSurveyInput	
GROUP BY
	SupplierSurveyID
)
SELECT 
	ROW_NUMBER() OVER(ORDER BY s.Total) AS RowNumber,
	*,
	CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 AS Percentage,
	case 
		when CONVERT(FLOAT,f.SupplierSurveyCountFiltered)/c.SupplierSurveyCount * 100 >= 10 then 1
		else 0
	end AS PercentageOrder
FROM 
	cteSupplierSurveyInput s
	LEFT JOIN cteSupplierSurveyCountFiltered f ON s.SupplierSurveyID = f.SupplierSurveyID
	LEFT JOIN cteSupplierSurveyCount c ON s.SupplierSurveyID = c.SupplierSurveyID
	LEFT JOIN Brand b ON s.BrandID = b.ID
ORDER BY 
	PercentageOrder,
	Total

Open in new window

0
 

Author Comment

by:garethtnash
ID: 38740190
Thank you,. but that gives me some strange results... One question,  the query below works great now, with the exception of the 'Position' column, which is no longer correct,..

select 
ROW_NUMBER() OVER(order by (((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) desc) as Position, 
brandid,
B.Name,
sum(Saleability) /count(*) as avgSaleability,
sum(Quality) /count(*) as avgQuality,
sum(Availability) /count(*) as avgAvailability,
sum(Delivery) /count(*) as avgDelivery,
sum(Returns) /count(*) as avgReturns,
sum(RetailPrice) /count(*) as avgRetailPrice,
sum(ServiceSupport) /count(*) as avgServiceSupport,
sum(Rep) /count(*) as avgRep,
sum(Accounts) /count(*) as avgAccounts,
sum(CreditControl) /count(*) as avgCreditControl,
(((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*))*20 Total,
count(*) Stockists,
sum(case when nextyeartrade = 'Y' then 1 else 0 end) as StockistsNY, 
C.Completed Completed,
CASE WHEN (count(*)*100)/C.Completed <= 10 then 1
Else 2 END as Tier
from [dbo].[SupplierSurveyInput] S
inner join dbo.Brand B
on S.BrandID = B.ID
Left join(SELECT [SupplierSurveyID], COUNT([ID])/COUNT(DISTINCT(BrandID)) Completed FROM [dbo].[SupplierSurveyInput] where CYearStockist <> 'u' group by [SupplierSurveyID]) C on C.SupplierSurveyID = S.SupplierSurveyID
where [CYearStockist] = 'Y' AND S.SupplierSurveyID = 1 AND CountryID in (3)
group by b.name, brandid, S.SupplierSurveyID, C.Completed
order by Tier desc, Total desc

Open in new window


Any thoughts? I know that

ROW_NUMBER() OVER(order by (((sum(Saleability)  + sum(Quality)  + sum(Availability) + sum(Delivery) + sum([Returns]) + sum(RetailPrice) + sum(ServiceSupport) + sum(Rep) + sum(Accounts) + sum(CreditControl) ) /10)  / count(*)) desc) as Position, 

Open in new window


is no longer correct as it doesn't take into consideration the tier element... just not sure how to correct this?

Thanks

G
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 500 total points
ID: 38740393
Try this

ROW_NUMBER() OVER(ORDER BY CASE WHEN (COUNT(*)*100)/C.Completed <= 10 THEN 1 ELSE 2 END, (((SUM(Saleability)  + SUM(Quality)  + SUM(Availability) + SUM(Delivery) + SUM([RETURNS]) + SUM(RetailPrice) + SUM(ServiceSupport) + SUM(Rep) + SUM(Accounts) + SUM(CreditControl) ) /10)  / COUNT(*)) DESC) AS POSITION, 

Open in new window

0
 

Author Comment

by:garethtnash
ID: 38740508
Close, but not quite, this starts 1 at the first result with a tier of 1, which means that the last 19 have a position of the first 19...

Thanks
0
 

Author Closing Comment

by:garethtnash
ID: 38740522
Thank you so much for your support, for your reference, the last query was just missing the DESC!

Thank you Thank you and Happy New Year
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38740623
Sorry I missed one as well

use
CONVERT(FLOAT,(COUNT(*))*100)/C.Completed
instead of
(COUNT(*)*100)/C.Completed

in your script
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now