0

I am working on a query where I want the average temperature of a whole month, but I have a challenge. When the new year starts the Month is 1 and i can't use the command: 

MONTH(TimestampUTC) = MONTH(DATEADD(MONTH, -1, GETDATE()))

Because month 1 minus 1 is month 0.

Therefor I want to use a case expression to make my code work.

When I run the code below, I get the following warning which doesn't make sense to me, because it worked when I tested it when it wasn't in this query:

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '='.

It is about the following line:

THEN MONTH(TimestampUTC) = MONTH(DATEADD(MONTH, +11, GETDATE()))

It looks like mssql doesn't accept an = statement after a THEN. I can't find it on the internet. Can somebody help me?

SELECT AVG(Value) AS AVG_temp 
FROM(

SELECT Value, TimestampUTC  
WHERE SourceName = 'Buitentemperatuur' and
CASE 
    WHEN MONTH(TimestampUTC) = 1 
    THEN MONTH(TimestampUTC) = MONTH(DATEADD(MONTH, +11, GETDATE()))
        and YEAR(TimestampUTC) = YEAR(DATEADD(YEAR, -1, GETDATE()))
    ELSE
        MONTH(TimestampUTC) = MONTH(DATEADD(MONTH, -1, GETDATE()))
        and YEAR(TimestampUTC) = YEAR(DATEADD(YEAR, 0, GETDATE()))
END 
FROM vDataLogChannelValue
) x

The program i work in is Microsoft SQL Server Management Studio 2014. I already did some UNIT tests on the other parts of the code. When i add the CASE the error comes active and the code doesn't work anymore.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Harold
  • 3
  • 3
  • 1
    "Because month 1 minus 1 is month 0." No; when you subtract a month from a date in January with `dateadd` you get a date in December previous year. You can test this easily yourself: `SELECT DATEADD(MONTH, -1, '20180101')` – HoneyBadger Jun 12 '18 at 11:39
  • By the way, you get the error because case returns an atomic value, it cannot be used to control program flow. – HoneyBadger Jun 12 '18 at 11:43
  • Do you know how i can use program flow in sql? – Harold Jun 12 '18 at 11:46
  • I don't understand what you are trying to do, as your premise is incorrect, as I explained in my first comment. – HoneyBadger Jun 12 '18 at 11:47
  • What you mean is correct, but i have history in my database in 2017 and 2016. When i use the statement you give to me i also receive information of that date a year before. Therefor i use a restriction on the year, or month, but when i do that your statement doesn't work anymore. – Harold Jun 12 '18 at 11:51
  • I don't follow your last comment; in your code you are also subtracting a year. I don't see what the problem is. – HoneyBadger Jun 12 '18 at 11:54
  • What you say works, but i have data from the years before. If i SELECT DATEADD(MONTH, -1, '20180101' then i get values from 20160101 two – Harold Jun 12 '18 at 12:06
  • @Harold Since your query does not "work", use words to describe which rows you want to include in your calculation. It appears that you want your query to be based on the current date in some fashion - but it is not clear what logic you intend to use. – SMor Jun 12 '18 at 12:15
  • @Harold And you say that you want average temperature for month, but your attempt indicates that you want to use a much longer period of time to select rows. So - does that mean you want to generate a monthly average for a series of months? – SMor Jun 12 '18 at 12:17
  • @SMor I try to be as summier as possible. I make a calculation for heating and cooling degree days. After a month i want the sum of the heating and cooling degree days of the previous month saved. To save this i have another program. The purpose of this query is resulting the sum of the heating - or cooling degree days of the previous month. The problem i face is that when i do month-1 it also selects the data of the previous month from the past years. – Harold Jun 12 '18 at 12:28

1 Answers1

0

When the new year starts the Month is 1 and i can't use the command:

MONTH(TimestampUTC) = MONTH(DATEADD(MONTH, -1, GETDATE()))

Because month 1 minus 1 is month 0.

You're wrong about that.

SELECT MONTH(DATEADD(month, -1, '20180101'))

Returns 12, not 0, so you CAN use the command you described above.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • You are correct, @HoneyBadger already made that clear to me, but how can i give a restriction that it is 201712 and not also 201612 what the system is giving me right now. – Harold Jun 13 '18 at 06:26
  • Look at the answer in the duplicate question. It handles getting both the correct month and year. – Tab Alleman Jun 13 '18 at 14:17