Adding Month to Date and Insert in another Table in SQL Server

Last Reply one year ago By pandeyism

Posted one year ago

Hi! I have two tables with data. I need each month save result query from two tables into third table. Exactly check date by input date and save result each month automatically without any person help.

For example:

If I input 15.03.2019 then 15.04.2019 save result into third table. 

DECLARE @Things AS TABLE(Id INT,Name VARCHAR(20),Percante INT)
INSERT INTO @Things VALUES(1,'mersedes',5)
INSERT INTO @Things VALUES(2,'build',7)
INSERT INTO @Things VALUES(3,'table',3)
INSERT INTO @Things VALUES(4,'wardrobe',3)
INSERT INTO @Things VALUES(5,'computer',3)
INSERT INTO @Things VALUES(6,'printer',3)

DECLARE @mainTable AS TABLE(Id INT,IdThings INT,Price money,getYear date)
INSERT INTO @mainTable VALUES(1,1,500,'01.01.2018')
INSERT INTO @mainTable VALUES(2,2,3000,'09.08.2018')
INSERT INTO @mainTable VALUES(3,3,400,'22.12.2018')
INSERT INTO @mainTable VALUES(4,5,400,'05.01.2019')
INSERT INTO @mainTable VALUES(6,6,700,'04.04.2019')

DECLARE @ResultTable AS TABLE(Id INT,Name VARCHAR(20),Res money, Rem money, getYear date)

select Name, (Price*Percante)/100 as res, (Price-(Price*Percante)/100)rem, getYear 
from @mainTable m 
inner join @Things t on m.IdThings = t.Id

 

Posted one year ago

Hi PRA,

Refer below sample.

SQL

DECLARE @Things AS TABLE(Id INT,Name VARCHAR(20),Percante INT)
INSERT INTO @Things VALUES(1,'mersedes',5)
INSERT INTO @Things VALUES(2,'build',7)
INSERT INTO @Things VALUES(3,'table',3)
INSERT INTO @Things VALUES(4,'wardrobe',3)
INSERT INTO @Things VALUES(5,'computer',3)
INSERT INTO @Things VALUES(6,'printer',3)
 
DECLARE @mainTable AS TABLE(Id INT,IdThings INT,Price money,getYear DATETIME)
INSERT INTO @mainTable VALUES(1,1,500,'01-01-2018')
INSERT INTO @mainTable VALUES(2,2,3000,'08-09-2018')
INSERT INTO @mainTable VALUES(3,3,400,'12-22-2018')
INSERT INTO @mainTable VALUES(4,5,400,'01-05-2019')
INSERT INTO @mainTable VALUES(6,6,700,'04-04-2019')

DECLARE @ResultTable AS TABLE(Id INT ,Name VARCHAR(20),Res money, Rem money, getYear DATETIME)
INSERT  INTO @ResultTable(Id,Name, Res, Rem,GetYear) 
	SELECT t.Id,Name, (Price*Percante)/100 AS res, (Price-(Price*Percante)/100)rem, DATEADD(Month,1,getYear) getYear
	FROM @mainTable m
	INNER JOIN @Things t on m.IdThings = t.Id
SELECT * FROM @ResultTable

Screenshot