Split values in 12 months

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  October 16, 2015 at 10:06 am

  #304361

  Hi

  I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.

  There is no date column in the table and the total amount has to be splitted equally.

  Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?

  CREATE TABLE #tbl_data (

  Region Varchar(25),

  Amount FLOAT,

  );

  INSERT INTO #tbl_data (

  Region,

  Amount

  )

  VALUES

  ( 'North America',48000),

  ( 'Asia' ,60000)

  Select * from #tbl_data

  DROP Table #tbl_data

  Output expected will be

  Region Jan2015 Feb2015.......Dec2015

  NorthAmerica 4000 4000 ......... 4000

  Asia 5000 5000 .......... 5000

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 16, 2015 at 10:14 am

  #1833860

  You mean like this?

  Select Region,

  Amount / 12 AS Jan2015,

  Amount / 12 AS Feb2015,

  --...

  Amount / 12 AS Dec2015

  from #tbl_data

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 16, 2015 at 10:16 am

  #1833861

  sharonsql2013 (10/16/2015)

  ---

  Hi

  I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.

  There is no date column in the table and the total amount has to be splitted equally.

  Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?

  CREATE TABLE #tbl_data (

  Region Varchar(25),

  Amount FLOAT,

  );

  INSERT INTO #tbl_data (

  Region,

  Amount

  )

  VALUES

  ( 'North America',48000),

  ( 'Asia' ,60000)

  Select * from #tbl_data

  DROP Table #tbl_data

  Output expected will be

  Region Jan2015 Feb2015.......Dec2015

  NorthAmerica 4000 4000 ......... 4000

  Asia 5000 5000 .......... 5000

  SELECT

  Region,

  [Jan2015] = Amount/12,

  [Dec2015] = Amount/12

  FROM #tbl_data

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  October 16, 2015 at 10:24 am

  #1833864

  Oh yes! Thanks

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  October 16, 2015 at 10:24 am

  #1833865

  Thanks:)