Month T-1

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  December 18, 2018 at 1:39 am

  #409389

  I have year(YYYY) and Month(M01) as data.
  How can I have a logic where when user select year and month, it will deduct T-1month

  I tried to query it but the year does not change.

  Example if Year=2018 and Month=M01 it still appear as Year 2018 and Month M12

  The expected result is Year 2017 and Month M12

• mark.humphreys

  SSC Eights!

  Points: 867

  More actions

  December 18, 2018 at 3:56 am

  #2016635

  I am sure there is a more elegant way of doing this, and without seeing your code this is just a bit of an assumption.

  However it would seem your year is dependent on the month, therefore you could use a case test on the month value and calculate the year accordingly.  However I don't know how far you data goes back.

  based purely on what you have said you could try the following.

  CASE WHEN Month = 'M01' THEN Year = 2017 ELSE 2018 END

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  December 21, 2018 at 6:24 pm

  #2017145

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  January 10, 2019 at 1:05 am

  #2018483

  Jeff Moden - Friday, December 21, 2018 6:24 PM

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  Is it possible if I would like to consolidate more than one @MonthCol.

  @MonthCol will input more than 1 month example @MonthCol=M12,M11

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  January 10, 2019 at 8:57 am

  #2018552

  girl_bj - Thursday, January 10, 2019 1:05 AM

  Jeff Moden - Friday, December 21, 2018 6:24 PM

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  Is it possible if I would like to consolidate more than one @MonthCol.

  @MonthCol will input more than 1 month example @MonthCol=M12,M11

  What do you expect the output from that to be?  1 row or 2?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  January 10, 2019 at 9:01 am

  #2018553

  Jeff Moden - Thursday, January 10, 2019 8:57 AM

  girl_bj - Thursday, January 10, 2019 1:05 AM

  Jeff Moden - Friday, December 21, 2018 6:24 PM

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  Is it possible if I would like to consolidate more than one @MonthCol.

  @MonthCol will input more than 1 month example @MonthCol=M12,M11

  What do you expect the output from that to be?  1 row or 2?

  2 rows.

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  January 10, 2019 at 9:41 am

  #2018569

  girl_bj - Thursday, January 10, 2019 9:01 AM

  Jeff Moden - Thursday, January 10, 2019 8:57 AM

  girl_bj - Thursday, January 10, 2019 1:05 AM

  Jeff Moden - Friday, December 21, 2018 6:24 PM

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  Is it possible if I would like to consolidate more than one @MonthCol.

  @MonthCol will input more than 1 month example @MonthCol=M12,M11

  What do you expect the output from that to be?  1 row or 2?

  2 rows.

  Use a string splitter (like DelimitedSplit8K) to split the single row into 2 and then use the method I previously demonstrated on the set of two rows.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  January 10, 2019 at 6:46 pm

  #2018650

  Jeff Moden - Thursday, January 10, 2019 9:41 AM

  girl_bj - Thursday, January 10, 2019 9:01 AM

  Jeff Moden - Thursday, January 10, 2019 8:57 AM

  girl_bj - Thursday, January 10, 2019 1:05 AM

  Jeff Moden - Friday, December 21, 2018 6:24 PM

  1.  Hopefully this teaches why you should never store formatted dates in a table.  Leave such formatting to when you need to present the data.
  2.  Hopefully this also teaches you the fallacy of storing parts of dates in more than 1 column instead of a single date column, which can be easily manipulated to display just about any format.

  Since the table you're working with has neither of those advantage, the following code will work for any legal year and month representation you have in your two columns.
  
DECLARE @YearCol CHAR(4) = '2018'
   ,@MonthCol CHAR(3) = 'M01'
;
SELECT YYYY = DATENAME(yy,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))
   ,Mxx = 'M'+RIGHT(DATEPART(mm,DATEADD(mm,-1,DATEFROMPARTS(@YearCol,RIGHT(@MonthCol,2),1)))+100,2)
 --FROM dbo.SomeTable
;


  Is it possible if I would like to consolidate more than one @MonthCol.

  @MonthCol will input more than 1 month example @MonthCol=M12,M11

  What do you expect the output from that to be?  1 row or 2?

  2 rows.

  Use a string splitter (like DelimitedSplit8K) to split the single row into 2 and then use the method I previously demonstrated on the set of two rows.

  Hi, DelimitedSplit8K not supported in sql server? Invalid object name.

• pietlinden

  SSC Guru

  Points: 63436

  More actions

  January 10, 2019 at 7:05 pm

  #2018651

  Did you run the code to create the function? Sounds like you didn't.

• girl_bj

  SSCommitted

  Points: 1731

  More actions

  January 10, 2019 at 7:14 pm

  #2018652

  pietlinden - Thursday, January 10, 2019 7:05 PM

  Did you run the code to create the function? Sounds like you didn't.

  Any sample that I can refer?

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  January 10, 2019 at 9:50 pm

  #2018658

  girl_bj - Thursday, January 10, 2019 7:14 PM

  pietlinden - Thursday, January 10, 2019 7:05 PM

  Did you run the code to create the function? Sounds like you didn't.

  Any sample that I can refer?

  Thanks

  Apologies.  I forgot to post the link.
  http://www.sqlservercentral.com/articles/Tally+Table/72993/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)