0

I am using Microsoft SQL Server Management Studio and I am trying to do the following: I want to make a calculation that looks for the last 2 months and the "current" month.

The month and year are defined as follows:

SELECT
    ID,
    dc.Year * 100 + dc.MonthOfYear AS YYYMM,
    dc.Year * 100 + dc.MonthOfYear - 1 AS PrevMonth,
    COUNT(1) AS Count_sales,
    SUM(sales) AS TotalSales
FROM
    xx (NOLOCK) dc
GROUP BY
    dc.Year * 100 + dc.[Month Of Year]

The problem occurs when I have 202101 because the previous month is taken as 202102 any know how to handle when the year changes as well please?

I have tried to using the following but in SQL Server, the add_months function doesn't exists:

Hive SQL Integer YYYYMM previous Months

Any suggestions please?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
alphasqrd
  • 53
  • 6
  • You have tagged this mysql, and you talk about mysql, but you also say you are using Microsoft SQL SMS and nolock is a MS-SQL feature... Is this for mysql or mssql? – MrApnea Mar 30 '21 at 15:06
  • @MrApnea you are right apologies, i am talking about mssql, have updated my question accordingly. – alphasqrd Mar 30 '21 at 15:12
  • 3
    @alphasqrd `NOLOCK` doesn't mean `go fast` or `don't take locks`, it means `take extra locks, read dirty and duplicate data while randomly throwing errors`. It won't fix any performance issues caused by bad queries or missing indexes, it will make them *worse* – Panagiotis Kanavos Mar 30 '21 at 15:15
  • See DATEADD https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15 – David Browne - Microsoft Mar 30 '21 at 15:17
  • `a calculation that looks for the last 2 Months and the "current" month` are you trying to filter or group by month, or both – Charlieface Mar 30 '21 at 15:18
  • @alphasqrd the easiest and fastest way to handle date-based reports is to use a Calendar table with precalculated eg 50 years of dates, with columns for year, month, quarter, week, day, YearMonth, LastYearMonth and anything else you need for reporting. After that, all you need is to join with the Calendar table on the `Date` field and group by the field you want. If you want to calculate month-over-month differences you can use the `LAG()` function after grouping by `YearMonth` – Panagiotis Kanavos Mar 30 '21 at 15:18

1 Answers1

0

It will not be the prettiest of code but you can use case here:

select 
    ID
    ,dc.Year*100+dc.MonthOfYear as YYYMM
    ,case when dc.MonthOfYear = 1 then dc.Year-1 else dc.Year end * 100 + dc.MonthOfYear - (case when dc.MonthOfYear = 1 then -11 else 1 end) as PrevMonth
    ,dc.MonthOfYear-case when dc.MonthOfYear = 1 then -11 else 1 end as PrevMonthXX
    ,count(1) as Count_sales
    ,sum(sales) as TotalSales
from xx (nolock) dc
group by dc.Year*100+dc.[Month Of Year]

It is most often easier to save as a regular date and then just take out year and month if you need it seperatly later on. But sometimes you just have do make do with what you have..

MrApnea
  • 1,776
  • 1
  • 9
  • 17
  • that is what I also tried but then I realised that if the month will lie in the first quarter of the year you have to do it for all months so I am not sure it is the best practice :/ – alphasqrd Apr 05 '21 at 08:09
  • Not sure what you mean? Why does it matter if the month is in the first quarter? If you just take the previous month there should only be special cases for january. And best practice is to use a date columns type so that you can handle it as a date. This is already passed best practice and is why this is causing so much trouble for such an easy task. If you had the possibility and time I would actually suggest to change the columns to a real date and everything would be much easier going forward. But of course that is not always a possibility. – MrApnea Apr 05 '21 at 10:54