Last period calculation using existing variable

Question

Last period calculation using existing variable

ldeassis09

SSC Veteran

Points: 223
More actions
March 29, 2017 at 11:38 am

#319935

Hello!
I'm trying to create a variable (@Last_Period) based on an existing one (@Begin_Period). Both variables are integers, and the result should be: @Last_Period = @Begin_Period - 1
For instance, if @Begin_Period = 201702 then @Last_Period should be 201701
Thanks
Luiz

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24569 More actions · Answer 1

ldeassis09 - Wednesday, March 29, 2017 11:38 AM
Hello!
I'm trying to create a variable (@Last_Period) based on an existing one (@Begin_Period). Both variables are integers, and the result should be: @Last_Period = @Begin_Period - 1
For instance, if @Begin_Period = 201702 then @Last_Period should be 201701
Thanks
Luiz

DECLARE @Begin_Period INT = 201702; DECLARE @Last_Period INT;

SET @Last_Period = @Begin_Period - 1;

SELECT @Begin_Period AS Begin_Period , @Last_Period AS Last_Period;

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 101202 More actions · Answer 2

DECLARE @Begin_Period int; DECLARE @Last_Period int;

SET @Begin_Period = 201712; SET @Last_Period = CASE WHEN @Begin_Period % 100 < 12 THEN @Begin_Period + 1 ELSE @Begin_Period + 89 END

SELECT @begin_period, @last_period

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sgmunson SSC Guru Points: 110651 More actions · Answer 3

ScottPletcher - Wednesday, March 29, 2017 11:47 AM
DECLARE @Begin_Period int; DECLARE @Last_Period int; SET @Begin_Period = 201712; SET @Last_Period = CASE WHEN @Begin_Period % 100 < 12 THEN @Begin_Period + 1 ELSE @Begin_Period + 89 END
SELECT @begin_period, @last_period

Ummm... that works nicely, but produces the NEXT period, not the previous one. If you have 201712, then the last period is 201711, but as you clearly noticed, a BeginPeriod of 201801 has a LastPeriod of 201712. I suspect this is much better handled as a date data type than as an int.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ldeassis09 SSC Veteran Points: 223 More actions · Answer 4

Thanks Scott and Sg! That's what I was looking for. Des, I tried to use your solution before but came across the issue covered by the CASE statement Scott and Sg provided.

Luiz

ldeassis09 SSC Veteran Points: 223 More actions · Answer 5

Guys,

I've just tested the logic below for @Begin_Period = 201701 and got "201700" for @Last_Period. I was expecting 201612 instead. Any suggestions? Thanks

DECLARE @Begin_Period int;
DECLARE @Last_Period int;

SET @Begin_Period = 201701;
SET @Last_Period = CASE WHEN @Begin_Period % 100 < 12 THEN @Begin_Period + 1 ELSE @Begin_Period + 89 END

SELECT @begin_period, @last_period

sgmunson SSC Guru Points: 110651 More actions · Answer 6

ldeassis09 - Wednesday, March 29, 2017 12:40 PM
Guys,
I've just tested the logic below for @Begin_Period = 201701 and got "201700" for @Last_Period. I was expecting 201612 instead. Any suggestions? Thanks
DECLARE @Begin_Period int;
DECLARE @Last_Period int;
SET @Begin_Period = 201701;
SET @Last_Period = CASE WHEN @Begin_Period % 100 < 12 THEN @Begin_Period + 1 ELSE @Begin_Period + 89 END
SELECT @begin_period, @last_period

As I indicated, this is much better off with date logic. Try this instead:
DECLARE @Begin_Period int; DECLARE @Last_Period int; DECLARE @Last_Period_Date date;

SET @Begin_Period = 201712; SET @Last_Period_Date = DATEADD(month, -1, CONVERT(date, CAST(@Begin_Period AS char(6)) + '01')); SET @Last_Period = CAST(CONVERT(char(6), @Last_Period_Date, 112) AS int);

SELECT @begin_period, @last_period;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

DesNorton SSC-Insane Points: 24569 More actions · Answer 7

ldeassis09 - Wednesday, March 29, 2017 12:40 PM
Guys,
I've just tested the logic below for @Begin_Period = 201701 and got "201700" for @Last_Period. I was expecting 201612 instead. Any suggestions? Thanks
DECLARE @Begin_Period int;
DECLARE @Last_Period int;
SET @Begin_Period = 201701;
SET @Last_Period = CASE WHEN @Begin_Period % 100 < 12 THEN @Begin_Period + 1 ELSE @Begin_Period + 89 END
SELECT @begin_period, @last_period

You need to cahneg the logic from ADD to SUBTRACT when working backwards
DECLARE @Begin_Period int; DECLARE @Last_Period int;

SET @Begin_Period = 201701; SET @Last_Period = CASE WHEN @Begin_Period % 100 > 1 THEN @Begin_Period - 1 ELSE @Begin_Period - 89 END

SELECT @begin_period, @last_period

How to post data/code on a forum to get the best help.

ldeassis09 SSC Veteran Points: 223 More actions · Answer 8

Guys,

Both examples work great! Thank you very much for the quick response.

L