Go back a Year using year and Month fields 3

[Cpreston]

Hi

I have some tables with no dates in except Month and Year. These are 2 separate fields called CalenderYear and CalenderYear
Is it possible for me to set them somehow so they look back from todays date back 12 months some how. I have googled and looked for examples but beginning to think it may not be possible.

Any ideas please

Thanks

 

[Andrzejek]

CalenderYear and CalenderYear" - do you mean "CalenderMonth and CalenderYear" ?

"look back from [blue]todays date back 12 months[/blue]"...?

Something like this?
[pre]
CalenderMonth CalenderYear [blue]MyNewDate[/blue]
01 2020 [blue]09-15-2020[/blue]
02 2021 [blue]09-15-2020[/blue]
03 1999 [blue]09-15-2020[/blue]
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

oops yes I meant "CalenderMonth and CalenderYear"

CalenderMonth CalenderYear MyNewDate
1 2020 09-15-2020
2 2021 09-15-2020
3 1999 09-15-2020

The CalendarMOnth is only single digit.

Yes mynewdate would be good and then I could try and use this to only show from todays date back 12 months

So first step how do I create mynewdate

Thanks

 

[Cpreston]

Sorry slightly misread your post ,

Basically I am looking at many rows of data and want it to go back to a year ago. So the mynewdate would be ok as they back a year but I only have CalenderMonth and CalenderYea to use.

 

[Andrzejek]

To get a date 1 year back from any date, you can use DATEADD function. Instead of ADDing 1 year, just ADD -1 year (well, subtract 1 year). Easy.

But if you want to get a date 1 year back from today's date, what's the point of CalenderMonth and CalenderYear fields?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

So, what do you want to have in [blue]this field[/blue]?

[pre]
CalenderMonth CalenderYear [blue]MyNewDate[/blue]
1 2020 [blue]???[/blue]
2 2021 [blue]???[/blue]
3 1999 [blue]???[/blue]
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

They are the only date fields available against each record. I don't have a normal date like dd\mm\yyyy

So can they be made into a date field like dd/mm/yyyy being I don't have a day field to work with and then I would like

Todays date or todays date when the query is run and then back to last 12 months. So all records 15/09/2021 back to 1 year ago.

So I think step 1 is to get a date field of dd/mm/yyyy created if that is possible and if so how
Then step 2use this date to look from today to a year ago

Thanks

 

[Andrzejek]

Please, fill the BLUE [blue]???[/blue] in my previous post.
Let's see what you want.

Properly asking a question is 90% of the answer.
In my simple world I would like to know 2 things:
1. this is the data I have (I hope I know this by now...)
2. this is the outcome/new data I need to get

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Current data I have is just the "CalenderMonth and CalenderYear"

CalenderMonth CalenderYear MyNewDate
1 2020 01/01/2019
2 2021 01/02/2020
3 1999 01/03/1998

 

[Andrzejek]

In USA the date format is mm/dd/yyyy (crazy, I know, but that's what it is), so adjust for GB if needed:
[tt]
Select CalenderMonth, CalenderYear,
DATEADD(year, -1, CAST(CONCAT(CalenderMonth + '/1/' + CalenderYear) as date))
AS MyNewDate
From MyTable[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

I tried it as a simple query to start with as below it odes not like this bit (CONCAT(CalendarMonth + '/1/' + CalendarYear) or this is underlined in red and gives the error
Msg 189, Level 15, State 1, Line 35
The concat function requires 2 to 254 arguments.

Select CalendarMonth, CalendarYear,
DATEADD(year, -1, CAST(CONCAT(CalendarMonth + '/1/' + CalendarYear) as date))
AS MyNewDate
From ProductAnalysis

 

[Andrzejek]

Read about CONCAT Function
You may even try to skip it and see what happens:
[tt]
Select CalenderMonth, CalenderYear,
'1/' + CalenderMonth + '/' + CalenderYear - 1 AS MyNewDate
From ProductAnalysis[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

I have the below but getting
Msg 245, Level 16, State 1, Line 39
Conversion failed when converting the varchar value '1/' to data type tinyint.


Select CalendarMonth, CalendarYear,
'1/' + CalendarMonth + '/' + CalendarYear - 1 AS MyNewDate
From ProductAnalysis

 

[Andrzejek]

Ooops, try:
[tt]
Select CalendarMonth, CalendarYear,
DATEADD(year, -1, CAST(CONCAT(CalendarMonth[highlight #FCE94F],[/highlight] '/1/'[highlight #FCE94F],[/highlight] CalendarYear) as date))
AS MyNewDate
From ProductAnalysis[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

You can try:

[tt][highlight #8AE234]STR[/highlight]('1/' + CalendarMonth + '/' + CalendarYear - 1) AS MyNewDate[/tt]
or
[tt][highlight #E9B96E]CAST[/highlight]('1/' + CalendarMonth + '/' + CalendarYear - 1 as varchar(10)) AS MyNewDate[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Or, since you already have a Month, you may just need a new (calculated) Year field:
[tt]
Select CalendarMonth, CalendarYear,[blue]
CalendarYear - 1 AS MyCalculatedYear[/blue]
From ProductAnalysis[/tt]



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Ok for the STR and Cast commands you last sent I get
Msg 245, Level 16, State 1, Line 34
Conversion failed when converting the varchar value '1/' to data type tinyint.

For the line DATEADD(year, -1, CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date)) appeared to work ok for the query but it went back to CalendarYear 2008, 2007 etc,,, so went back to far.


however when I run it with a little more detailed query I get some strange results the query and screen shot below, for example the first one as 2020 month 2 this is more than 1 year ago


select CalendarYear, CalendarMonth,
DATEADD(year, -1, CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date))
AS MyNewDate, SUM(QuantitySold) As QTYSold
from ProductAnalysis where
ProductID = 49810
and BranchID = 1
--and CalendarYear = 2021
Group by CalendarMonth, CalendarYear

 

[Cpreston]

SO for the dates I would expect to see the below (2021 looks ok except 2 is missing but that is because we did not do anything with this in Feb) which would then give me 09 2021 back to 10 2020

2020 12
2020 11
2020 10

 

[Cpreston]

Although what it appears to do is go back to the last 12 known transactions let me investigate it more and I will get back, thanks for the help thus far

 

[Andrzejek]

Your screen shot looks OK to me.
All 2020 CalendarYears have 2019 in MyNewDate, all 2021 have 2020 in MyNewDate, all [green]Months[/green] match just fine...

"the first one as 2020 month 2 this is more than 1 year ago" - is it?[pre]
CalYr CalMonth MyNewdate
2020 2 2019-02-01[/pre]

What's wrong with it? 2019-02-01 (Feb 1, 2019) is one Year prior to Feb 2020

I must be missing something. Stating: "It's wrong" does not help.
This is what I am getting: ABC
This is what I need: XYZ

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson