Hi!
I used below query, but didn’t get requirement result. I want program check import date if it less 30 days not count monthly deprice.
Because import date for two product is less 30 days. For than not need calculation for products import day is less 30 days till getdate.
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
)
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price]) VALUES (1, '2020-11-12', '1000')
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price]) VALUES (2, '2021-01-01', '9000')
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price]) VALUES (3, '2021-01-25', '11000')
INSERT INTO @IMPORT([IdProduct], [ImpDate], [Price]) VALUES (4, '2020-01-12', '9000')
select Price, ((Price * 15)/100) * (SELECT DATEPART(m, getdate()))MonthDeprice, (Price - ((Price * 15)/100) * (SELECT DATEPART(m, getdate())))Remain, ImpDate from @IMPORT
--15 is deprice percentage for tecnical from price for each month
I want below result:
Price
|
MonthDeprice
|
Remain
|
ImpDate
|
1000,00
|
150,00
|
850,00
|
2020-11-12
|
9000,00
|
0,00
|
9000,00
|
2021-01-01
|
11000,00
|
0,00
|
11000,00
|
2021-01-25
|
9000,00
|
1350,00
|
7650,00
|
2020-01-12
|