Month and Year Columns

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 19, 2017 at 2:46 pm

  #1923882

  lkennedy76 - Thursday, January 19, 2017 2:34 PM

  I tried
  where [Year]=Year(GetDate())-1
  AND [Month]=Month(GetDate())-1;

  No results came back

  I also tried the datepart and dateadd, still nothing

  Sadly your month is an int so it doesn't have a leading zero, so we have to jump through a bit of hoop. 

  Quick and dirty is to select max(year) first into a variable and then select max(month) where year = @maxyear into a variable. Now you can run your query with an explicit filter of each value. This should be quite quick if you have an index on year.

  Without said index you will be doing two table scans above. In this case it will be more efficient to use a CASE statement to pre-pend a leading zero to a character cast of the month and post-pend that to the year and then get the max of that into a variable. Now strip out the actual year and month into appropriate int variables and proceed with your filtered query.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 2:47 pm

  #1923883

  ZZartin - Thursday, January 19, 2017 2:41 PM

  lkennedy76 - Thursday, January 19, 2017 2:34 PM

  I tried
  where [Year]=Year(GetDate())-1
  AND [Month]=Month(GetDate())-1;

  No results came back

  I also tried the datepart and dateadd, still nothing

  Are you sure you have data in there?

  This gets me 2016 and 12 for year and month.

  SELECT DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())), DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
  

  MCSE SQL Server 2012\2014\2016

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 19, 2017 at 2:48 pm

  #1923885

  Oh, perhaps you can cheat and add a persisted computed column to the table?!? 😀

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 2:49 pm

  #1923886

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  MCSE SQL Server 2012\2014\2016

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 19, 2017 at 2:50 pm

  #1923887

  
-- Demonstrate math
declare @date date = '2016-03-31';
select @date CurrentDate, DATEADD(month, -1, @date) ReportDate, ReportYear = YEAR(DATEADD(month, -1, @date)), ReportMonth = Month(DATEADD(month, -1, @date));

  -- Here is a query
  SELECT s.SalesID
       , s.StoreID
       , s.[Year]
       , s.[Month]
       , s.Sales
  FROM
    dbo.Sales s
  WHERE
    s.[Year] = YEAR(DATEADD(month, -1, GETDATE()))
    AND s.[Month] = Month(DATEADD(month, -1, GETDATE()));

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 19, 2017 at 3:40 pm

  #1923904

  lkennedy76 - Thursday, January 19, 2017 2:49 PM

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  A lot of extra work when you compare to what I posted.

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 4:07 pm

  #1923905

  Lynn Pettis - Thursday, January 19, 2017 3:40 PM

  lkennedy76 - Thursday, January 19, 2017 2:49 PM

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  A lot of extra work when you compare to what I posted.<

  Hey Lynn,

    If you want to help me on something else....
  I now have to look back at two months prior on the third month and assign a percentage to that total, my noodle is baked today!

  MCSE SQL Server 2012\2014\2016

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 19, 2017 at 4:27 pm

  #1923909

  lkennedy76 - Thursday, January 19, 2017 4:07 PM

  Lynn Pettis - Thursday, January 19, 2017 3:40 PM

  lkennedy76 - Thursday, January 19, 2017 2:49 PM

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  A lot of extra work when you compare to what I posted.<

  Hey Lynn,

    If you want to help me on something else....
  I now have to look back at two months prior on the third month and assign a percentage to that total, my noodle is baked today!

  Care to expand on this requirement?  Are you saying that every three months you need the data for the previous three months?  If so, how do you determine when this occurs?

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 6:29 pm

  #1923917

  Lynn Pettis - Thursday, January 19, 2017 4:27 PM

  lkennedy76 - Thursday, January 19, 2017 4:07 PM

  Lynn Pettis - Thursday, January 19, 2017 3:40 PM

  lkennedy76 - Thursday, January 19, 2017 2:49 PM

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  A lot of extra work when you compare to what I posted.<

  Hey Lynn,

    If you want to help me on something else....
  I now have to look back at two months prior on the third month and assign a percentage to that total, my noodle is baked today!

  Care to expand on this requirement?  Are you saying that every three months you need the data for the previous three months?  If so, how do you determine when this occurs?

  okay so, if sales are met at 50K for two months straight on the third month, charge them x amount percentage on the third month during a 12 month period. if the never make 50K ever in a 12 month period back to back, after 12 months charge them X amount percent...

  MCSE SQL Server 2012\2014\2016

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  January 20, 2017 at 3:35 am

  #1923958

  ZZartin - Thursday, January 19, 2017 2:22 PM

  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  Sorry I anwsered before noticing there where already plenty anwsers.
  So this anwser can be ignored.
  
Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))


  ZZartin did give the anwser to the question: How do I get the previous month.

  Januar will give december of the previous year.
  Februar will give januar of the SAME year.
  March ......    etc.

  The question with the 'LAST' month is a bit ambiguous.
  I think that what you wanted is deliverd by ZZartin, if not restate the question.

  Ben
  (Sorry to 'break' in on your anwser ZZartin,but seeing the other posts, I think this needed some extra explanation).

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 20, 2017 at 9:21 am

  #1924078

  lkennedy76 - Thursday, January 19, 2017 6:29 PM

  Lynn Pettis - Thursday, January 19, 2017 4:27 PM

  lkennedy76 - Thursday, January 19, 2017 4:07 PM

  Lynn Pettis - Thursday, January 19, 2017 3:40 PM

  lkennedy76 - Thursday, January 19, 2017 2:49 PM

  Joe Torre - Thursday, January 19, 2017 2:37 PM

  ZZartin - Thursday, January 19, 2017 2:22 PM

  Joe Torre - Thursday, January 19, 2017 2:12 PM

  lkennedy76 - Thursday, January 19, 2017 1:49 PM

  create table dbo.Sales 

  (

  [SalesID] [int] IDENTITY(1,1) NOT NULL,

  [StoreID] [int] NOT NULL,

  [Year] [int] NOT NULL,

  [Month] [int] NOT NULL,

  [Sales] [money] NULL
  )

  select
  sum(sales),
  StoreID
  from sales
  where month =12 and year = 2016 --I do not want to hard code the month and year I want to be able to pull just 12/2016 data for sales and only the last months worth of sales going forward for a report.
  group by storeid

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year=Year(GetDate())-1
AND s.Month=Month(GetDate())-1;


  What happens in January?

  It would be something more like, at least the month and year aren't stored as strings 🙂

  Year = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))
  AND Month = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))

  
SELECT s.SalesID
     , s.StoreID
     , s.Year
     , s.Month
     , s.Sales
FROM dbo.Sales s
WHERE s.Year= CASE WHEN Month(GetDate())=1 THEN Year(GetDate())-1 ELSE Year(GetDate()) END
AND s.Month= CASE WHEN Month(GetDate())=1 THEN 12 ELSE Month(GetDate())-1 END;


  Boom! that's it, thank you!

  A lot of extra work when you compare to what I posted.<

  Hey Lynn,

    If you want to help me on something else....
  I now have to look back at two months prior on the third month and assign a percentage to that total, my noodle is baked today!

  Care to expand on this requirement?  Are you saying that every three months you need the data for the previous three months?  If so, how do you determine when this occurs?

  okay so, if sales are met at 50K for two months straight on the third month, charge them x amount percentage on the third month during a 12 month period. if the never make 50K ever in a 12 month period back to back, after 12 months charge them X amount percent...

  Okay, I am a visual problem solver and I am having a problem making a visual representation of your word problem.  Please explain your problem using concrete examples in the form of data.  Starting with X what I want to see is Y.

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  January 20, 2017 at 10:26 am

  #1924105

  lkennedy76 - Thursday, January 19, 2017 12:56 PM

  I have a question on getting just the last month and last year with only a month and year column. I have tried dateadd using the month and the year but that doesn't seem to work. Any Ideas? this is for my where clause.

  This does not really work in T-SQL. In ANSI/ISO standard SQL, there is an INTERVAL temporal data type. And that is what you are trying to do! Are you use to DB2 or Oracle?

  My recommendation is to create a calendar table that holds the start and end dates of each month, and has a column that uses the MySQL convention of "yyyy-mm-00" for the names of the months (this is been proposed for ISO-8601 standard, but it is still dialect today. As far as I know) . This convention allows you to properly sort the month intervals in with the ANSI/ISO standard "yyyy-mm-dd" display formatting dates.

  Another trick that you might not think about is to set your fill factor as tight as possible. You know you are never going to have to insert data in the middle of such a table. It is also a good idea to keep the rows in descending order because most of the time you are probably looking at current dates rather than historical ones.

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 20, 2017 at 10:52 am

  #1924115

  Thanks but a solution was check. A case stmt was the trick.

  MCSE SQL Server 2012\2014\2016