CODEWITHSUNDEEP
sqlsql-server
Gopipuli
Gopipuli

Reputation: 393

How to use a where condition in SQL Server for summing data from a year to another year that is from financial year to next year?

In my table, I have two columns - one is month with values 1,2,...12, and the other is year 2014,...2019.

I want to sum a column for a period that is from April 2014 to April 2015 - how can I specify a condition to get these values?

Please help me to solve this. I found this article on the web but I don't have a date field as mentioned in this link. Please help, and thanks

Upvotes: 0

Views: 292

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would simply do:

select sum(column)
from t cross apply
     (values (datefromparts(year, month, 1))
     ) v(yyyymm)
where v.yyyymm >= '2014-04-01' and
      v.yyyymm <= '2015-04-01';

Dates are so much easier to work with when using dates.

In fact, they are so simple, I would recommend adding a computed column into the table:

alter table t add column yyyymm as (datefromparts(year, month, 1));

Then the query would simply be:

select sum(column)
from t 
where t.yyyymm >= '2014-04-01' and
      t.yyyymm <= '2015-04-01';

Simple. Readable. And you can persist yyyymm and use an index.

Upvotes: 1

George Joseph
George Joseph

Reputation: 5932

Easier to work with dates, If there isnt a daet field, we could compare as follows

select sum(mysum)
  from mytable
where cast(concat(cast(myYear as varchar(4)),'-',cast(myMonth as varchar(4)),'-01') as date) >='2014-04-01'
  and cast(concat(cast(myYear as varchar(4)),'-',cast(myMonth as varchar(4)),'-01') as date)  <'2019-04-01'

Upvotes: 1

Andreas
Andreas

Reputation: 159225

From April 2014 to April 2015, upper-exclusive:

SELECT SUM(MyNum)
  FROM MyTable
 WHERE ((MyYear = 2014 AND MyMonth >= 4)
     OR (MyYear = 2015 AND MyMonth < 4))

If it crosses multiple years, e.g. from April 2014 to April 2019:

SELECT SUM(MyNum)
  FROM MyTable
 WHERE ((MyYear = 2014 AND MyMonth >= 4)
     OR (MyYear BETWEEN 2015 AND 2018)
     OR (MyYear = 2019 AND MyMonth < 4))

Upvotes: 1

Related Questions