Get the previous month number including year

0
votes

I have a table in which I get the coupons of a specific company. My coupon table is like:

Company_Coupon

  • ID
  • CompanyID
  • Month
  • Total_Coupons
  • Year

I keep my months in the format of their number (1-12).

When I update the total coupons for the invoicing, I want to update the ones from the previous month.

I do this by the following query: 

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE CompanyID = 1205
AND Month = MONTH(GETDATE())-1 AND Year  = YEAR (GETDATE())

My query works but I noticed that this won't work in January 2019.

How can I update this query so that it will work in January 2019?

4
sqlsql-serverdatetime
It would probably be better to store a single YearMonth column, typed as date with a constraint enforcing that it's always the first day of the month. That way you can use straightforward date math functions to compute this. E.g. DATEADD(month,DATEDIFF(month,'20010201',GETDATE()),'20010101') always gives you the first of last month. - Damien_The_Unbeliever
@Damien_The_Unbeliever Sadly this isn't an option at the moment since I use this table to loop through the rows and manipulate them using different algorithms - user3127554

4 Answers

2
votes

Try with case when like below:

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE CompanyID = 1205
AND Month = (case when MONTH(GETDATE())-1=0 then 12 else MONTH(GETDATE())-1 end)  AND Year  = (case when MONTH(GETDATE())-1=0 then YEAR (GETDATE())-1 else YEAR (GETDATE()) end)
1
votes

You can try to get different number between 1900-01-01 and your data, then do some calculation to get last month.

Query 1:

SELECT DATEADD(month, DATEDIFF(month,0,'2019-01-01') - 1, 0)
UNION ALL
SELECT DATEADD(month, DATEDIFF(month,0,'2018-08-01') - 1, 0)

Results:

|                      |
|----------------------|
| 2018-12-01T00:00:00Z |
| 2018-07-01T00:00:00Z |

so you query can be

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE 
    CompanyID = 1205
AND 
    Month = MONTH(DATEADD(month, DATEDIFF(month,0,GETDATE()) - 1, 0))
AND 
    Year  = YEAR (DATEADD(month, DATEDIFF(month,0,GETDATE()) - 1, 0))
1
votes

As I said in a comment, I'd prefer to have a single YearMonth column with the correct data type for datetime work, but we can do something very similar here:

UPDATE Company_Coupon
SET Total_Coupons = @count
FROM Company_Coupon
CROSS APPLY (SELECT 
  DATEADD(month,DATEDIFF(month,'20010201',GETDATE()),'20010101')) t(LastMonth)
WHERE CompanyID = 1205
AND Month = MONTH(LastMonth) AND Year  = YEAR(LastMonth)

The two dates used in the above expression do not matter much. All that matters really is the relationship between them. Here, the second date falls a month before the first and it's the relationship that effectively gets applied to GETDATE() by the DATEADD/DATEDIFF expression. It's a pattern that can be used in lots of different ways - e.g. a variant of this pattern can be used to find the last day of 3 months ago if you're not on a SQL version that supports EOMONTH.

0
votes

If you use SQL Server 2012 or later version, you can employ the eomonth() function that returns last day of a previous month for a given date. From it, you can extract both month() and year() parts and use them in your query:

UPDATE c SET Total_Coupons = @count
from dbo.Company_Coupon c
WHERE c.CompanyID = 1205
    AND c.Month = MONTH(eomonth(GETDATE()))
    AND c.Year  = YEAR(eomonth(GETDATE()));