Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
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
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
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
SELECT COUNT([ID])/62 Completed
FROM [dbo].[SupplierSurveyInput]
where CYearStockist <> 'u'
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
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
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
;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
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
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,
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,
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
13 Experts available now in Live!