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]

Also, your Select statement should error:

[tt]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[green]
--and CalendarYear = 2021[/green]
Group by CalendarMonth, CalendarYear
[/tt]
You have 4 fields in your Select, you have one SUM() but your Group By by just 2 fields.
You should have:
[tt]
...
Group by CalendarMonth, CalendarYear,
DATEADD(year, -1, CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date))[/tt]


---- Andy

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

 

[Cpreston]

What I meant by 2020 2 being incorrect is that if you count the months back to 2020 2

9 this year and 10 2020 to get to February so it is about 19 months ago. I only expected to see Calendar Month back to 2020 10

I will add you suggestion to my select statement thanks

 

[Andrzejek]

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."

To SEE what's going on, run:
[tt]
select CalendarYear, CalendarMonth,
CAST(CONCAT(CalendarMonth, '/1/', CalendarYear) as date) As MyCalendarDate
from ProductAnalysis
[/tt]
and SEE if your CalendarYear and CalendarMonth matches your 2 fields when converted to a DATE field MyCalendarDate

---- Andy

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

 

[Andrzejek]

What I meant by 2020 2 being incorrect is that if you count the months back to 2020 2

9 this year and 10 2020 to get to February so it is about 19 months ago. I only expected to see Calendar Month back to 2020 10

I admit, I am completely lost here.
The only way for me to get 'un-lost' would be to have what you have showed in your screen shot, but add another column 'by-hand' of what do you expect as a correct/expected value.

[pre]
... MyNewDate QTYSold CorrectValue
[/pre]

---- Andy

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

 

[Cpreston]

Ok will get something over tomorrow which hopefully can get you un-lost, I think some screen shots from our system may also help. Many thanks for your help this far

 

[gmmastros]

What is the data type for the CalendarYear and CalendarMonth columns? Specifically, they could be a string but only contain numbers. Please look at the table definition and let me know.

I'm thinking of a different approach than Andy. Maybe not better, but different.

If you multiply the year by 100 and add the month, you should have something that you can use to compare.

Ex:

Select *
From   YourTable
Where  CalendarYear * 100 + CalendarMonth >= (Year(GetDate()) - 1) * 100 + Month(GetDate())

Thinking about it this way...

If today is 15-Sep-2021, and you want to get everything from Sept-2020 forward, you multiply year * 100 + month, so your table data would look something like this...

Year Month (result)
2020 1 202001
2020 9 202009
2020 12 202012
2021 4 202104

Then, you take (today's year - 1) * 100 + today's month, you would get 202009

Using the above sample, your where would be

Where Year * 100 + Month >= 202001

You would get all rows except the first one (Jan 2020), which you wouldn't want. If your CalendarYear and CalendarMonth columns are strings, this would get a little more complicated but should still work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

CalendarYear is smallint
CalendarMonth is tinyint

 

[gmmastros]

In that case... does this work for you?

select  CalendarYear, 
        CalendarMonth,
        SUM(QuantitySold) As QTYSold
from    ProductAnalysis 
where   ProductID = 49810
        and BranchID = 1
        And CalendarYear * 100 + CalendarMonth >= (Year(GetDate()) - 1) * 100 + Month(GetDate()) 
Group by CalendarMonth, CalendarYear

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks gmmastros that gets me back to the exact dates I require. I have actually combined both of your suggestions so I have a date field also albeit they are first of the month on all of them it gives me something to sue if people want to choose between 2 dates.

Finally I am trying to create a new column which consists of the Month numbers 1,2,3, etc.... nut I want them to display Jan, Feb, Mar and then also want the year also so the result would be Jan 2020, Feb 2020 etc... Just doing some googling not having much luck. Any ideas thanks

 

[Andrzejek]

You may try this approach...

---- Andy

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

 

[Cpreston]

That just takes me to a web search page

I have tried the below

DATENAME(MONTH, CalendarMonth) this gives me January in all the rows even though my calendarmonth column as 1,3,4,5,6,7,8,9

CONVERT(varchar(3), DATENAME(MONTH, CalendarMonth)) AS OrderMonth this gives me Jan in all the rows even though my calendarmonth column as 1,3,4,5,6,7,8,9

Thanks

 

[Chris Miller]

You need a time machine to go back 1 year.

But jokes aside: I think many didn't get your 'from todays date back 12 months' right, up to this from gammastros.

CalendarYear * 100 + CalendarMonth >= (Year(GetDate()) - 1) * 100 + Month(GetDate())

Okay, but why not make use of DATE()?





Chriss

 

[Andrzejek]

How about:[tt]
CHOOSE(CalendarMonth, 'Jan', 'Feb', ..., 'Dec') As MyMonthName[/tt]

---- Andy

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

 

[Cpreston]

Chris I tried to apply your line of code as a test but it gives syntax issues, gammastros works for what I need. Currently trying to make the calendar month into a name and then join the the month and year together so I get Jan 2020 for example

 

[Chris Miller]

Sorry, I meant to say DATEFROMPARTS. See

http://sqlfiddle.com/#!18/0f4a6/1

...Where DATEFROMPARTS(CalendarYear,CalendarMonth,1)
Between  DateAdd(year,-1,GetDate()) and GetDate()

Chriss

 

[Andrzejek]

DATENAME(MONTH, CalendarMonth) this gives me January"

DATENAME needs a Date to be passed as a second arg, not [tt]tinyint[/tt]
[tt]
DATENAME ( datepart , date ) [/tt]

---- Andy

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

 

[Cpreston]

Andrzejek ( the CHOOSE worked so I have the names now which is great. Just need to join the CalendarYear and the CHOOSE(CalendarMonth,'Jan', 'Feb', 'Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as NameMonth result together now

 

[Cpreston]

Simple Concat did the trick, thanks to all for the help brilliant. All working now phew.

 

[Cpreston]

Simple concat did not do the trick, I am going to create a new thread as it is a different issue I think, so if you see it it does not relate top this one, or I don't think it does. Thanks

 

[JeffModen]

From what I've seen (and I may have missed one somewhere), all of the solutions provided so far have a Non-SARGable equation in the WHERE clause. That will become a whole lot more important when the table gets larger. It seems like they would also skip a month if there were no data for a given month.

Since I'm not a frequent flier on this forum, please don't take those comments as slams. They're just observations.

With all of that being said, here's one possible solution. I built a million row test table to test it based on all the code in this thread. If I got that part right, this should work right out of the box. If you want to go back one more month, just add a "(12)" in the VALUES clause.

   WITH cteCalendar AS
(--==== Creates a 12 row calendar table with 12 months previous including the current month.
     -- This will also allow us to display months that have zero sales.
 SELECT  MonthDate
        ,CalendarMonth = MONTH(MonthDate)
        ,CalendarYear  = YEAR(MonthDate)
   FROM (--==== Create the dates for the last 12 months instead of converting the data dates.
         SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE())-t.N,0) -- First of current month
           FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11))t(N)
        )dt(MonthDate)
)--==== Everything is simple and sargable now.
 SELECT  PrettyDate = SUBSTRING(CONVERT(VARCHAR(22),cal.MonthDate,106),4,18)
        ,QtySold    = ISNULL(SUM(dat.QuantitySold),0)
   FROM      cteCalendar          cal
   LEFT JOIN dbo.ProductAnalysis  dat
          ON cal.CalendarMonth = dat.CalendarMonth
         AND cal.CalendarYear  = dat.CalendarYear
    AND dat.ProductID = 49810
    AND dat.BranchID  = 1
  GROUP BY cal.MonthDate
  ORDER BY cal.MonthDate
;

I did remove all of the data for February to show that February will still show up with a zero value.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row