Month and Year Columns

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 12:56 pm

  #325763

  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.

  MCSE SQL Server 2012\2014\2016

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 19, 2017 at 1:09 pm

  #1923845

  When working with dates, you DO NOT want to use Year and Month (unless you have a calendar table).  You want to use the first day of the month.  Using Year/Month requires a function on one of the database fields, so it is not SARGable, whereas using the first day of the month may be SARGable (if the first day is calculated from constants/variables).

  Also, by "last" do you mean based on today's date or based on the max date in the table?

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 1:14 pm

  #1923850

  drew.allen - Thursday, January 19, 2017 1:09 PM

  When working with dates, you DO NOT want to use Year and Month (unless you have a calendar table).  You want to use the first day of the month.  Using Year/Month requires a function on one of the database fields, so it is not SARGable, whereas using the first day of the month may be SARGable (if the first day is calculated from constants/variables).

  Also, by "last" do you mean based on today's date or based on the max date in the table?

  Drew

  I only have month and year in the table, no day.

  MCSE SQL Server 2012\2014\2016

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 19, 2017 at 1:37 pm

  #1923853

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

  drew.allen - Thursday, January 19, 2017 1:09 PM

  When working with dates, you DO NOT want to use Year and Month (unless you have a calendar table).  You want to use the first day of the month.  Using Year/Month requires a function on one of the database fields, so it is not SARGable, whereas using the first day of the month may be SARGable (if the first day is calculated from constants/variables).

  Also, by "last" do you mean based on today's date or based on the max date in the table?

  Drew

  I only have month and year in the table, no day.

  Can you please give us a create table statement, some sample data and a representative query/outputs?

  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 1:49 pm

  #1923856

  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

  MCSE SQL Server 2012\2014\2016

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 19, 2017 at 2:08 pm

  #1923861

  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.

  SELECT Col1, Col2
  FROM Tab1
  WHERE YearColumn=Year(GetDate())-1
  AND MonthColumn=Moth(GetDate())-1;

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 19, 2017 at 2:12 pm

  #1923863

  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;


• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  January 19, 2017 at 2:22 pm

  #1923866

  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()))

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 19, 2017 at 2:23 pm

  #1923868

  Just a quick question, If generating the report in January 2017 are you looking for just data in December 2016 or do you want December 2016 and in January 2017 up to the date of the report run.

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 2:27 pm

  #1923871

  Lynn Pettis - Thursday, January 19, 2017 2:23 PM

  Just a quick question, If generating the report in January 2017 are you looking for just data in December 2016 or do you want December 2016 and in January 2017 up to the date of the report run.

  Just January. I only want last months totals. It's for a month end process for the accounting department.

  MCSE SQL Server 2012\2014\2016

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 19, 2017 at 2:32 pm

  #1923873

  Assuming that you data does not contain any records for the current month, this should work.

  ;
WITH CTE AS
(
    SELECT StoreId, sales, DENSE_RANK() OVER(ORDER BY [year] DESC, [month] DESC) AS dr
    FROM sales
)
SELECT StoreId, SUM(sales) AS total_sales
FROM CTE
WHERE dr = 1
GROUP BY StoreId


  You want to filter before doing the GROUP BY, because that leaves fewer totals to calculate.  The DENSE_RANK should perform fairly well if you have an index on Year DESC and Month DESC.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 2:34 pm

  #1923875

  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

  MCSE SQL Server 2012\2014\2016

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  January 19, 2017 at 2:37 pm

  #1923877

  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;


• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  January 19, 2017 at 2:41 pm

  #1923878

  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()))

• lkennedy76

  SSChampion

  Points: 13683

  More actions

  January 19, 2017 at 2:43 pm

  #1923879

  drew.allen - Thursday, January 19, 2017 2:32 PM

  Assuming that you data does not contain any records for the current month, this should work.

  ;
WITH CTE AS
(
    SELECT StoreId, sales, DENSE_RANK() OVER(ORDER BY [year] DESC, [month] DESC) AS dr
    FROM sales
)
SELECT StoreId, SUM(sales) AS total_sales
FROM CTE
WHERE dr = 1
GROUP BY StoreId


  You want to filter before doing the GROUP BY, because that leaves fewer totals to calculate.  The DENSE_RANK should perform fairly well if you have an index on Year DESC and Month DESC.

  Drew

  the year and date are literally hard coded in. for example store # 4 opened in 1999. I have sales for each month in my tables, 1 through 12 and every year going forward. 1999-2016

  

  MCSE SQL Server 2012\2014\2016