Calculate column value based on condition in SQL Server

PRA
 
on Jan 29, 2021 03:02 AM
662 Views

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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jan 29, 2021 03:33 AM

Refer below query and modify accordingly.

SQL

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, 
CASE WHEN DATEDIFF(DAY,ImpDate,GETDATE()) > 30 THEN ((Price * 15)/100) * (SELECT DATEPART(m, getdate())) 
ELSE  0 END 'MonthDeprice',
CASE WHEN DATEDIFF(DAY,ImpDate,GETDATE()) > 30 THEN (Price - ((Price * 15)/100) * (SELECT DATEPART(m, getdate())))
ELSE  Price END 'Remain',
ImpDate 
from @IMPORT