This is my table:

Date part is saved as int month and day. How to find the total amount made in the previous month of the current month?

CodePudding user response：

Try the following query:

Select Date_/100 as Date_Month, Sum(Total) as MonthlyTotal From YourTable
Where MONTH(GETDATE()) - 1 = (Date_/100)
Group By (Date_/100)

See a demo from db<>fiddle.

CodePudding user response：

I concur with the comments made above. I get it that you can't change the way that you store your "dates" . But you should make whoever made that decision miserable. They are asking for trouble like that.

Having said that -

I rely, here, on the fact that the division of two integers, in SQL Server, if it is not an integer already, is truncated to the next lower integer, and returned as integer.

WITH
-- your input, don't use in final query ...
-- I renamed the third column more aptly to "monthday"
indata(id,Total,monthday) AS (
          SELECT 1,3.00,410
UNION ALL SELECT 2,4.55,121
UNION ALL SELECT 3,7.40,630
UNION ALL SELECT 4,8.00,629
)
-- real query starts here - replace following comma with "WITH"
,
per_month AS (
  SELECT
    monthday / 100 AS monthno                                                                                                                                                                                                     
  , SUM(total) AS monthtot
  FROM indata
  GROUP BY monthno
-- ctl  monthno | monthtot 
-- ctl --------- ----------
-- ctl        1 |     4.55
-- ctl        4 |     3.00
-- ctl        6 |    15.40
)
SELECT 
  *
FROM per_month
WHERE monthno=MONTH(CURRENT_DATE) - 1;
-- out  monthno | monthtot                                                                                                                                                                                                        
-- out --------- ----------
-- out        6 |    15.40