Date Amount name of date
2017-09-05 364100.00 Tuesday
2017-09-11 189700.00 Monday
Tuesday should have value as 364100+189700
Question is badly worded, but I only take a guess this is what you are after??
;with mycte as (
select
'2017-09-05' as date
, 364100.00 as amount
,'Tuesday' as [name of date]
union all
select
'2017-09-11'
, 189700.00
,' Monday'
)
Select
date
,amount
,[name of date]
,SUM(amount) over (order by date) as Total
from mycte
order by date
To combine 2 integer, you need convert each as varchar first then you can use operator + or use concat function like this :
SELECT CONCAT(CAST(364100 AS varchar(20)), '+', CAST(189700 AS varchar(20)))
But to achieve your goal, because a row depends on other row, may be you need cursor for looping like this :
DECLARE @Your_Table TABLE
(
Date_Column Date,
Amount INT
)
DECLARE @ResultTable TABLE
(
Date_Column Date,
Amount VarChar(200),
NameOfDate VarChar(20)
)
INSERT INTO @Your_Table VALUES('2017-09-05', 364100);
INSERT INTO @Your_Table VALUES('2017-09-11', 189700);
DECLARE @Dt Date, @Am INT, @CurrentAm VarChar(200)
SELECT @CurrentAm = '';
DECLARE db_cursor CURSOR FOR
SELECT * FROM @Your_Table ORDER BY Date_Column DESC
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Dt, @Am
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CurrentAm=''
BEGIN
SELECT @CurrentAm=CAST(@Am AS VarChar(200));
INSERT INTO @ResultTable VALUES(@Dt,@CurrentAm, DATENAME(DW, @Dt));
END
ELSE
BEGIN
SELECT @CurrentAm=CONCAT(CAST(@Am AS varchar(20)), '+', CAST(@CurrentAm AS VarChar(200)));
INSERT INTO @ResultTable VALUES(@Dt,@CurrentAm, DATENAME(DW, @Dt));
END
FETCH NEXT FROM db_cursor INTO @Dt, @Am
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM @ResultTable ORDER BY Date_Column
Edited : AS Harry's comment, You do not have to convert int to char in concat function
replace this
SELECT @CurrentAm=CONCAT(CAST(@Am AS varchar(20)), '+', CAST(@CurrentAm AS VarChar(200)));
with this
SELECT @CurrentAm=CONCAT(@Am, '+', @CurrentAm);