I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination.
In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins.
I have tried many queries of which none worked.
My schema is
USE [GotoVine]
GO
/****** Object: Table [dbo].[wbridge_historyBasies] Script Date: 17/10/2019 09:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wbridge_historyBasies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[grower] [varchar](50) NULL,
[block] [varchar](50) NULL,
[section] [varchar](50) NULL,
[year] [int] NULL,
[weight] [decimal](5, 2) NULL,
[payclass] [varchar](50) NULL,
[yearclass] [varchar](50) NULL,
CONSTRAINT [PK_wbridge_historyBasies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[wbridge_historyBasies] ON
My data
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (1, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(4.94 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (2, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(4.00 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (3, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(5.46 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (4, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(5.78 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (5, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.32 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (6, N'2437-VIS', N'53067', N'14', 2013, CAST(5.96 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (7, N'2437-VIS', N'53067', N'14', 2013, CAST(5.50 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (8, N'2437-VIS', N'53067', N'14', 2013, CAST(5.36 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (9, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.26 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (10, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.56 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (11, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.52 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (12, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(5.26 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (13, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(4.56 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (14, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(4.83 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (15, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(3.88 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (16, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(5.14 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (17, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(5.40 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (18, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(4.12 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (19, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(5.04 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (20, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(4.66 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (21, N'2542-WIG', N'53057', N'12-WEL', 2016, CAST(4.80 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (22, N'2542-WIG', N'53057', N'12-WEL', 2016, CAST(4.76 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (23, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(4.52 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (24, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(5.02 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (25, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(5.48 AS Decimal(5, 2)), N'6-DROWIT', NULL)
SET IDENTITY_INSERT [dbo].[wbridge_historyBasies] OFF
The required result require is in the attached Excel file.
Regards and thank you in advance.
October 17, 2019 at 10:34 am
I am also attaching a script file for both the schema and data.
October 17, 2019 at 10:53 am
Try this. There may be a slightly more elegant solution, but this should work. I haven't checked that the results are correct, because I'm wary about opening spreadsheets posted online.
WITH payclassCounts AS (
SELECT
grower
,block
,section
,year
,payclass
,yearclass
,COUNT(*) OVER (PARTITION BY grower, block, section, year, payclass) AS payclassCount
FROM wbridge_historyBasies
)
, OrderedRows AS (
SELECT
grower
,block
,section
,year
,payclass
,yearclass
,FIRST_VALUE(payclass) OVER (PARTITION BY grower, block, section, year ORDER BY payclassCount DESC) AS NewValue
FROM payclassCounts
)
UPDATE OrderedRows
SET yearclass = NewValue;
John
Edit: oops - forgot about the weight tie-breaker requirement! Des's solution will probably work better.
I have not checked this against your spreadseet
WITH cteBase AS (
SELECT whb.ID, whb.grower, whb.block, whb.section, whb.year, whb.weight, whb.payclass
, PayClassCounter = COUNT( * ) OVER ( PARTITION BY whb.grower, whb.block, whb.section, whb.year, whb.payclass )
, WeightSum = SUM( weight ) OVER ( PARTITION BY whb.grower, whb.block, whb.section, whb.year, whb.payclass )
FROM dbo.wbridge_historyBasies AS whb
)
, cteMaxOrder AS (
SELECT b.ID, b.grower, b.block, b.section, b.year, b.weight, b.payclass, b.PayClassCounter, b.WeightSum
, rn = ROW_NUMBER() OVER ( PARTITION BY b.grower, b.block, b.section, b.year
ORDER BY b.PayClassCounter DESC, b.WeightSum DESC )
FROM cteBase AS b
)
, cteFinal AS (
SELECT mo.grower, mo.block, mo.section, mo.year, mo.weight, mo.payclass
--, mo.ID, mo.PayClassCounter, mo.WeightSum, mo.rn
FROM cteMaxOrder AS mo
WHERE mo.rn = 1
)
UPDATE hb
SET yearclass = f.payclass
FROM dbo.wbridge_historyBasies AS hb
INNER JOIN cteFinal AS f
ON hb.grower = f.grower
AND hb.block = f.block
AND hb.section = f.section
AND hb.year = f.year;
October 17, 2019 at 11:21 am
Just needed to add a single line to my previous solution in order to take account of the weight requirement. Again, I've checked that the query runs, but I haven't compared the results to those in the spreadsheet.
WITH payclassCounts AS (
SELECT
grower
,block
,section
,year
,payclass
,yearclass
,COUNT(*) OVER (PARTITION BY grower, block, section, year, payclass) AS payclassCount
,SUM(weight) OVER (PARTITION BY grower, block, section, year, payclass) AS Weightperpayclass
FROM wbridge_historyBasies
)
, OrderedRows AS (
SELECT
grower
,block
,section
,year
,payclass
,yearclass
,FIRST_VALUE(payclass) OVER (PARTITION BY grower, block, section, year ORDER BY payclassCount DESC, Weightperpayclass DESC) AS NewValue
FROM payclassCounts
)
UPDATE OrderedRows
SET yearclass = NewValue;
John
October 17, 2019 at 2:58 pm
Thank you both Des and John. Both queries works. And I must say both are very impressive. A newbie like me would never have gotten it. Thank you once again.
October 17, 2019 at 3:02 pm
You're welcome. Do you understand how they work? Make sure you don't put them into production until you do, because you'll be responsible for their operation and maintenance.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply