Hi!
How I can group by count?
DECLARE @Product AS TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](20) NULL
)
INSERT INTO @Product([Name]) VALUES ('Computer')
INSERT INTO @Product([Name]) VALUES ('Printer')
INSERT INTO @Product([Name]) VALUES ('NoteBook')
INSERT INTO @Product([Name]) VALUES ('TV')
select * from @Product
DECLARE @IMPORT AS TABLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[IdProduct] [int],
[ImpDate] [date] NULL,
[Price] money NULL,
[Count] int NULL
)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (1, '2020-11-12', '18000', 11)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (2, '2021-01-01', '9000', 12)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (3, '2021-01-25', '11000', 11)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (4, '2020-01-12', '9000', 12)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (5, '2020-11-10', '14000', 13)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (6, '2020-01-01', '19000', 14)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (7, '2020-10-25', '12000', 13)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price], [Count]) VALUES (8, '2020-08-12', '13000', 14)
select Count, Price,
CASE WHEN DATEDIFF(DAY,ImpDate,GETDATE()) > 30 THEN ((Price * 5)/100) * (SELECT DATEPART(m, getdate()))
ELSE 0 END 'MonthDeprice',
CASE WHEN DATEDIFF(DAY,ImpDate,GETDATE()) > 30 THEN (Price - ((Price * 5)/100) * (SELECT DATEPART(m, getdate())))
ELSE Price END 'Remain',
ImpDate
from @IMPORT group by Count, ImpDate, Price
