REATE TABLE [dbo].[Product](
        [id] [int] IDENTITY(1,1) NOT NULL,
	[Artikul] [nvarchar](20) NOT NULL,
	[Name] [varchar](50) NOT NULL

CREATE TABLE [dbo].[agreg](
        [id] [int] IDENTITY(1,1) NOT NULL,	
        [Date_Create] [datetime2](7) NOT NULL,
	[Product] [int] NOT NULL,
	[Line] [int] NOT NULL,
	[Barcode_bottle] [nvarchar](150) NOT NULL,
	[Barcode_box] [nvarchar](26) NULL

3. Parents (таблица дальнейшей иерархии агрегации)
[src]CREATE TABLE [dbo].[Parents](
        [id] [int] IDENTITY(1,1) NOT NULL,	
        [children] [nchar](26) NULL,
	[agregation_level] [int] NULL, (уровень агрегации если короб в коробе = 2, короб в паллете =3)
	[Parents] [nchar](26) NULL

select distinct t1.Line, t2.artikul, t2.name, count(distinct t1.barcode_bottle),
count(distinct t1.Barcode_box),  
from [fsmnew].[dbo].[Product] as t2 
join [fsmnew].[dbo].[agregation] as t1 on t2.id = t1.product 
join [fsmnew].[dbo].[Parents] as t3 on t1.barcode_box = t3.children
 where t1.barcode_box in 
(select barcode_box from[dbo].[agreg] where Date_Create = ('2020-12-03') and 
group by t1.Line,t2.Name,t2.artikul