CODEWITHSUNDEEP
sqlsql-server
Dungeon
Dungeon

Reputation: 1040

SQL sum a particular row based on condition?

I am using MS SQL Server. This is the table I have:

Create table tblVal
(
Id int identity(1,1),
Val NVARCHAR(100),
startdate datetime,
enddate datetime
)
--Inserting Records--
Insert into tblVal values(500,'20180907','20191212')
Insert into tblVal values(720,'20190407','20191212')
Insert into tblVal values(539,'20190708','20201212')
Insert into tblVal values(341,'20190221','20190712')

Table as this:

   Id   |Val        |startdate      |enddate
   --- ----------------------------------------------
    1   |500        |2018-09-07     |2019-12-12 
    2   |720        |2019-04-07     |2019-12-12 
    3   |539        |2019-07-08     |2020-12-12 
    4   |341        |2019-02-21     |2019-07-12

This is what I want:

Mon     |   Total
------------------
Jan     |   500
Feb     |   841
March   |   841
April   |   1561 
May     |   1561
June    |   1561
July    |   2100
........|.........

I want to sum Val column if it lies in that particular month. For ex. in case of April month it lies between two of the rows. I have to check both the condition start date and end date. and then sum the values.

This is what I have tried:

select *
into #ControlTable 
from dbo.tblVal

DECLARE @cnt INT = 0;
while @cnt<12
begin
select sum(CASE  
             WHEN MONTH(startdate) BETWEEN @cnt and MONTH(enddate) THEN 0 
              ELSE 0 
           END) 
 from #ControlTable
SET @cnt = @cnt + 1;
end

drop table #ControlTable

but from above I was unable to achieve the result. How do I solve this? Thanks.

Upvotes: 4

Views: 2848

Answers (3)

Hemang A
Hemang A

Reputation: 1012

DECLARE @outpuTable Table(
MOn INT,
Total nvarchar(MAX)
)

DECLARE @cnt INT = 1;
while (@cnt<=12)
begin

INSERT INTo @outpuTable VALUES(@cnt,
(select ISNULL(sum(CONVERT(INT,Val)),0)
 from tblVal
WHERE  @cnt BETWEEN MONTH(startdate) and MONTH(enddate) ))

SET @cnt = @cnt + 1;
end

Select * from @outpuTable

Upvotes: 1

Sanpas
Sanpas

Reputation: 1180

Hi if i understand your wall query i think this query can respond : 

Create table #tblVal
(
Id int identity(1,1),
Val NVARCHAR(100),
startdate datetime,
enddate datetime
)
--Inserting Records--
Insert into #tblVal values(500,'20180907','20191212')
Insert into #tblVal values(720,'20190407','20191212')
Insert into #tblVal values(539,'20190708','20201212')
Insert into #tblVal values(341,'20190221','20190712')

Create table #tblMonth ( iMonth int)
Insert into #tblMonth values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);


select * from #tblVal
select * from #tblMonth

SELECT *, SUM(case when Val is null then 0 else cast (Val as int) end) OVER(ORDER BY iMonth
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as 'Totaltime'
FROM #tblMonth 
LEFT JOIN #tblVal ON MONTH(startdate) = iMonth
ORDER BY iMonth

drop table #tblVal
drop table #tblMonth

Not you have to use SQL Server version 2008 min for use OVER(ORDER BY iMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Link :

If you have older version you can use CTE or JOIN ON select .

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I believe you want something like this:

with dates as (
      select min(datefromparts(year(startdate), month(startdate), 1)) as dte,
             max(datefromparts(year(enddate), month(enddate), 1)) as enddte
      from tblVal
      union all
      select dateadd(month, 1, dte), enddte
      from dates
      where dte < enddte
     )
select d.dte, sum(val)
from dates d left join
     tblval t
     on t.startdate <= eomonth(dte) and
        t.enddate >= dte
group by d.dte
order by d.dte;

This does the calculation for all months in the data.

The results are a bit different from your sample results, but seem more consistent with the data provided.

Here is a db<>fiddle.

Upvotes: 3

Related Questions